Anything Else Like SQL::Statement?

[NateTut]

I need to parse complex SQL like the example shown in the code below. SQL::Statement seemed to be the way to go, but it chokes pretty badly on the nasty SQL statements I'm feeding it. SQL::Statement didn't care for the comments (/**/ style) nor did it care for the derived queries.

Any other way to do this? I realize that this is a nasty problem (parsing such a mess) but I'd rather not have to even try re-inventing this wheel.

#!/usr/bin/perl

use strict;
use warnings;
use SQL::Statement;
#  my $sql    = "
#     SELECT a FROM b JOIN c WHERE c=? AND e=7 ORDER BY f DESC LIMIT 5
+,2
#  ";

 my $sql = "
/* ******************** NAT OTIF *************************************
+****** */
/* This query creates the measures file for NAT OTIF cube */
/* This query has been modified to get data from MCSI_ORD_LN_MONTHLY *
+/
/* The output names of the fields have been changed to match the old v
+ersion and the cube names  6/10/04 */
/* New field ORDERED_LATE added 12/16/04 */
Select

REF_DT,
SHIP_TO_CUST_ID,
EXT_MATL_GRP_ID,
STK_CLASS_ID,
MATL_ID,
FACILITY_ID,
BRAND_ID,
QTY_UNIT_MEAS_ID,
ABC_SLS_DMAN_IND,
DELIV_PRTY_ID,

NET_DUE_SFR_QTY,    
NET_DUE_CFR_QTY,
COMMITTED_LATE,
COMMIT_ONTIME_DUE_CURR_QTY,
RELEASED_ONTIME,
BLOCK_991,
BLOCK_021,
OLD_BLOCK_99,
OLD_BLOCK_02CREDIT,
ORDERED_LATE,
NO_DELIVERY_NOTE,
SHIPPED_LATE,
COMMIT_ONTIME_SHIPPED_ONTIME,
RELEASED_LATE_SHIPPED_ONTIME,
COMMIT_LATE_SHIPPED_ONTIME,
OTIF_QTY_SHIPPED_ONTIME,
SHIPPED_ONTIME_SFR_QTY,


((Case When Main.DELIV_PRTY_ID = '99' Then (Main.OLD_BLOCK_99 - Main.O
+rdered_Late) Else 0 End)) As BLOCK_99,
((Case When Main.DELIV_PRTY_ID <> '99' Then (Main.OLD_BLOCK_02CREDIT -
+ Main.Ordered_Late) Else 0 End)) As BLOCK_02CREDIT
From
(
select 
    REF_DT As REF_DT,
    substr(SHIP_TO_CUST_ID,3,8) As SHIP_TO_CUST_ID,
    OVER_ALL.EXT_MATL_GRP_ID As EXT_MATL_GRP_ID,
    OVER_ALL.STK_CLASS_ID  As STK_CLASS_ID,
    Substr(OVER_ALL.MATL_ID,11,8) As MATL_ID,
    FACILITY_ID As FACILITY_ID,
    OVER_ALL.BRAND_ID As BRAND_ID,
    QTY_UNIT_MEAS_ID As QTY_UNIT_MEAS_ID,
    (Case When MAT.ABC_SLS_DMAN_IND > '' then MAT.ABC_SLS_DMAN_IND els
+e 'Blank' end) As ABC_SLS_DMAN_IND,
    OVER_ALL.DELIV_PRTY_ID As DELIV_PRTY_ID,

    COALESCE(Sum(OVER_ALL.NET_DUE_SFR_QTY),0) As NET_DUE_SFR_QTY,    
    COALESCE(Sum(OVER_ALL.NET_DUE_CFR_QTY),0) As NET_DUE_CFR_QTY,
    Sum(NET_DUE_CFR_QTY - COMMIT_ONTIME_DUE_CURR_QTY) As COMMITTED_LAT
+E,
    COALESCE(Sum(COMMIT_ONTIME_DUE_CURR_QTY),0) As COMMIT_ONTIME_DUE_C
+URR_QTY,
    COALESCE(Sum(Released_Ontime),0) As RELEASED_ONTIME,
    COALESCE(Sum((case when OVER_ALL.DELIV_PRTY_ID = '99' then (OVER_A
+LL.COMMIT_ONTIME_DUE_CURR_QTY - OVER_ALL.Released_Ontime) else 0 end)
+),0) As BLOCK_991,
    COALESCE(Sum((case when OVER_ALL.DELIV_PRTY_ID <> '99' then (OVER_
+ALL.COMMIT_ONTIME_DUE_CURR_QTY - OVER_ALL.Released_Ontime) else 0 end
+)),0) As BLOCK_021,
    ((case when BLOCK_991 < 0 then 0 else BLOCK_991 end)) As OLD_BLOCK
+_99,
    ((case when BLOCK_021 < 0 then 0 else BLOCK_021 end)) As OLD_BLOCK
+_02CREDIT,
    COALESCE(Sum(OVER_ALL.Ordered_Late),0) As ORDERED_LATE,
    COALESCE(Sum(No_Delivery_note),0) As NO_DELIVERY_NOTE,
    COALESCE(Sum(Shipped_Late_Qty),0) As SHIPPED_LATE,
    COALESCE(Sum(Commit_Ontime_Shipped_Ontime),0) As COMMIT_ONTIME_SHI
+PPED_ONTIME,
    COALESCE(Sum(Released_Late_Shipped_Ontime),0) As RELEASED_LATE_SHI
+PPED_ONTIME,
    COALESCE(Sum(Commit_Late_Shipped_Ontime),0) As COMMIT_LATE_SHIPPED
+_ONTIME,
    COALESCE(Sum(OTIF_Qty_Shipped_Ontime),0) As OTIF_QTY_SHIPPED_ONTIM
+E,
    COALESCE(Sum(SHIPPED_ONTIME_SFR_QTY),0) As SHIPPED_ONTIME_SFR_QTY
From
    /* ***************************************** BEGINNING OF OVER_ALL
+ ************************************* */
    (Select 
        OS.REF_DT As REF_DT,
        OS.SHIP_TO_CUST_ID As SHIP_TO_CUST_ID,
        OS.EXT_MATL_GRP_ID As EXT_MATL_GRP_ID,
        OS.STK_CLASS_ID  As STK_CLASS_ID,
        OS.MATL_ID As MATL_ID,
        OS.QTY_UNIT_MEAS_ID As QTY_UNIT_MEAS_ID,
        OS.FACILITY_ID As FACILITY_ID,
        OS.BRAND_ID As BRAND_ID,
        OS.ORDER_ID As ORDER_ID,
        OS.ORDER_LINE_NBR As ORDER_LINE_NBR,
        OS.DELIV_PRTY_ID As DELIV_PRTY_ID,
        Max(OS.NET_DUE_CFR_QTY)  As NET_DUE_CFR_QTY,
        Max(OS.NET_DUE_SFR_QTY)  As NET_DUE_SFR_QTY,
        ((Case when Max(OS.COMMIT_ONTIME_DUE_CURR_QTY) > Max(OS.NET_DU
+E_CFR_QTY) then Max(OS.NET_DUE_CFR_QTY) else Max(OS.COMMIT_ONTIME_DUE
+_CURR_QTY) end)) As COMMIT_ONTIME_DUE_CURR_QTY,
        Sum(OS.Released_Ontime)  As Released_Ontime,
        Sum(OS.Ordered_Late) As Ordered_Late,
        Sum((case when OS.COMMIT_ONTIME_DUE_CURR_QTY - OS.Released_Ont
+ime > 0 and OS.DELIV_NOTE_CREA_DT >= OS.REQ_SHIP_DT And OS.GOODS_ISS_
+DT - OS.REQ_SHIP_DT <= 1 then OS.QTY_TO_SHIP else 0 end)) As Released
+_Late_Shipped_Ontime1,
        Sum(OS.Shipped_Late_Qty) As Shipped_Late_Qty,
        Sum(OS.Commit_Ontime_Shipped_Ontime) As Commit_Ontime_Shipped_
+Ontime,
        Sum(OS.Released_Late_Shipped_Ontime) As Released_Late_Shipped_
+Ontime,
        Max(OS.Commit_Late_Shipped_Ontime) As Commit_Late_Shipped_Onti
+me,
        Sum(OS.OTIF_Qty_Shipped_Ontime) As OTIF_Qty_Shipped_Ontime,
        Max(OS.SHIPPED_ONTIME_SFR_QTY) As SHIPPED_ONTIME_SFR_QTY,
     Max((case when OS.COMMIT_ONTIME_DUE_CURR_QTY > OS.SHIPPED_ONTIME_
+SFR_QTY And OS.DELIV_NOTE_CREA_DT Is Null then (OS.COMMIT_ONTIME_DUE_
+CURR_QTY -  OS.SHIPPED_ONTIME_SFR_QTY) else 0 end)) As No_Delivery_no
+te
     From
     (Select * From
/* ********************************************************** Orders *
+******************************************************************* *
+/
          (Select 
          OL.REF_DT As REF_DT,
          OL.SHIP_TO_CUST_ID as SHIP_TO_CUST_ID,
          OL.MATL_ID As MATL_ID,
(Case When OL.PROD_GRP_NBR in ('0082', '0083') Then 'AIRCRAFT RETREADS
+' Else OL.EXT_MATL_GRP_ID End)  As EXT_MATL_GRP_ID,
OL.STK_CLASS_ID  As STK_CLASS_ID,
          OL.QTY_UNIT_MEAS_ID As QTY_UNIT_MEAS_ID,
          OL.FACILITY_ID As FACILITY_ID,
          OL.BRAND_ID As BRAND_ID,
          OL.ORDER_ID As ORDER_ID,
          OL.ORDER_LINE_NBR As ORDER_LINE_NBR,
          Sum(OL.NET_DUE_CFR_QTY) As NET_DUE_CFR_QTY,
          Sum(OL.NET_DUE_SFR_QTY) As NET_DUE_SFR_QTY,
          Sum(OL.NET_DUE_CFR_QTY) - sum(OL.COMMIT_ONTIME_DUE_CURR_QTY)
+ As QTY_COMMIT_LATE_DUE_CUR,
          Sum(OL.COMMIT_ONTIME_DUE_CURR_QTY) As COMMIT_ONTIME_DUE_CURR
+_QTY,
          Sum(OL.SHIPPED_ONTIME_SFR_QTY) As SHIPPED_ONTIME_SFR_QTY,
          Sum(OL.NET_DUE_SFR_QTY - OL.SHIPPED_ONTIME_SFR_QTY) As QTY_S
+HP_LATE
          From
          GDYR_BI_VWS.MCSI_ORD_LN_MONTHLY OL
          Where
          OL.NET_DUE_CURR_QTY > 0 And

(OL.REF_DT = (DATE-1) - EXTRACT(DAY FROM (DATE-1))  OR OL.REF_DT = DAT
+E -1)  And 

(OL.EXT_MATL_GRP_ID = 'TIRE'  or (OL.MKT_GRP_NBR = '0028' and OL.PROD_
+GRP_NBR in ('0082', '0083'))) 
          Group by    1,2,3,4,5,6,7,8,9,10) ORDERS
     LEFT OUTER JOIN
/* ********************************************************** Shipment
+s *******************************************************************
+* */
          (Select 
          DL.ORDER_ID As ORDER_ID1,
          DL.ORDER_LINE_NBR As ORDER_LINE_NBR1,
          DL.DELIV_ID As DELIV_ID,
          DL.DELIV_LINE_NBR As DELIV_LINE_NBR,
          DE.DELIV_NOTE_CREA_DT As DELIV_NOTE_CREA_DT,
          DE.GOODS_ISS_DT As GOODS_ISS_DT,
          DE.DELIV_PRTY_ID As DELIV_PRTY_ID,
          O.DELIV_BLK_IND As DELIV_BLK_IND,
          SCH.REQ_SHIP_DT As REQ_SHIP_DT,
          Sum(DL.QTY_TO_SHIP) As QTY_TO_SHIP,

          Sum((case when DE.DELIV_NOTE_CREA_DT Is Null then OL.SHIPPED
+_ONTIME_SFR_QTY else (case when SCH.REQ_SHIP_DT > DE.DELIV_NOTE_CREA_
+DT and SCH.REQ_SHIP_DT < date-2  then  DL.QTY_TO_SHIP else 0 end ) en
+d)) As Released_Ontime,
      Sum((Case When O.ORDER_DT >= SCH.REQ_SHIP_DT Then DL.QTY_TO_SHIP
+ End)) As Ordered_Late,
          Sum((case when DE.GOODS_ISS_DT - SCH.REQ_SHIP_DT > 1 and DE.
+DELIV_NOTE_CREA_DT < SCH.REQ_SHIP_DT  then DL.QTY_TO_SHIP else 0 end)
+) As Shipped_Late_Qty,
          Released_Ontime - Shipped_Late_Qty As Commit_Ontime_Shipped_
+Ontime,
          Max((case when OL.COMMIT_ONTIME_DUE_CURR_QTY - ((case when D
+E.DELIV_NOTE_CREA_DT Is Null then OL.SHIPPED_ONTIME_SFR_QTY else (cas
+e when SCH.REQ_SHIP_DT > DE.DELIV_NOTE_CREA_DT and SCH.REQ_SHIP_DT < 
+date-2  then  DL.QTY_TO_SHIP else 0 end ) end)) > 0 and DE.DELIV_NOTE
+_CREA_DT >= SCH.REQ_SHIP_DT And DE.GOODS_ISS_DT - SCH.REQ_SHIP_DT <= 
+1 then DL.QTY_TO_SHIP else 0 end)) As Released_Late_Shipped_Ontime,
          Sum((case when OL.COMMIT_ONTIME_DUE_CURR_QTY <= OL.SHIPPED_O
+NTIME_SFR_QTY Then OL.SHIPPED_ONTIME_SFR_QTY - OL.COMMIT_ONTIME_DUE_C
+URR_QTY Else 0 End)) As Commit_Late_Shipped_Ontime,
          Sum(OL.SHIPPED_ONTIME_SFR_QTY) As True_Shipped_Ontime,
          (Commit_Ontime_Shipped_Ontime + Commit_Late_Shipped_Ontime +
+ Released_Late_Shipped_Ontime) As OTIF_Qty_Shipped_Ontime
          From
          GDYR_BI_VWS.ORDERS O,
          GDYR_BI_VWS.MCSI_ORD_LN_MONTHLY OL,
          GDYR_BI_VWS.DELIV_LINE DL,
          GDYR_BI_VWS.DELIV DE,
          GDYR_BI_VWS.SCHED_LINE SCH
          where
          DL.ORDER_ID = OL.ORDER_ID And
          DL.ORDER_LINE_NBR = OL.ORDER_LINE_NBR And
          DE.DELIV_ID = DL.DELIV_ID And
          DE.SBU_ID = DL.SBU_ID And
          DE.SBU_ID = SCH.SBU_ID And
          DE.SBU_ID = O.SBU_ID And
          DE.SBU_ID = 2 And
          DE.BUS_DT = DL.BUS_DT And
          DE.BUS_DT = SCH.BUS_DT And
          DE.BUS_DT = O.BUS_DT And
          DE.BUS_DT = current_date And
          DE.EXP_DT = DL.EXP_DT And
          DE.EXP_DT = SCH.EXP_DT And
          DE.EXP_DT = O.EXP_DT And
          DE.EXP_DT = '5555-12-31' And
          DL.ORDER_ID = SCH.ORDER_ID And
          DL.ORDER_LINE_NBR = SCH.ORDER_LINE_NBR And
          DL.ORDER_ID = O.ORDER_ID And
          OL.NET_DUE_CURR_QTY > 0 And

(OL.REF_DT = (DATE-1) - EXTRACT(DAY FROM (DATE-1))  OR OL.REF_DT = DAT
+E -1)  And 

(OL.EXT_MATL_GRP_ID = 'TIRE'  or (OL.MKT_GRP_NBR = '0028' and OL.PROD_
+GRP_NBR in ('0082', '0083'))) 

          Group by 1,2,3,4,5,6,7,8,9) SHIPMENTS        
/*   *****************************************************************
+***************************************** */
     ON
     ORDERS.ORDER_ID = SHIPMENTS.ORDER_ID1  And
     ORDERS.ORDER_LINE_NBR = SHIPMENTS.ORDER_LINE_NBR1)  OS
     group by 1,2,3,4,5,6,7,8,9,10,11) OVER_ALL,
GDYR_BI_VWS.MATL MAT
Where 
OVER_ALL.MATL_ID = MAT.MATL_ID
and MAT.BUS_DT = Date
And MAT.SBU_ID = 2
And MAT.EXP_DT = '5555-12-31'
Group by 1,2,3,4,5,6,7,8,9,10
) Main

/*  ********************************** For Test Only *****************
+*************************************************
And OL.SHIP_TO_CUST_ID = '0000600243'

And OL.MATL_ID = '000000000000117074'

OWN_CUST_ID = '00A0006024' And
OL.SHIP_TO_CUST_ID = '0000130408' And
OL.MATL_ID = '000000000000003305' And

OL.REF_DT in ('2003-01-31', '2003-02-28', '2003-03-31', '2003-04-30', 
+'2003-05-31', '2003-06-30', '2003-07-31', '2003-08-31', '2003-09-30',
+ '2003-10-31', '2003-11-30', '2003-12-31', '2004-01-31', '2004-02-29'
+, '2004-03-31', '2004-04-30', '2004-05-31', '2004-06-30', '2004-07-14
+')  And

OL.REF_DT in ('2004-05-31', '2004-06-30')  And

OL.REF_DT = '2004-11-30' And
OWN_CUST_ID = '00A0006024' And

(OL.REF_DT = (DATE-1) - EXTRACT(DAY FROM (DATE-1))  OR OL.REF_DT = DAT
+E -1)  And 
OWN_CUST_ID in ('00A0003047', '00A0006024', '00A0007029', '00A0008051'
+) And
********************************** For Test Only *********************
+********************************************* */ 
";
 my $parser = SQL::Parser->new();
 $parser->{RaiseError}=1;
 $parser->{PrintError}=0;
# $parser->parse("LOAD 'MyLib::MySyntax' ");
 my $stmt = SQL::Statement->new($sql,$parser);
 printf "Command             %s\n",$stmt->command;
 printf "Num of Placeholders %s\n",scalar $stmt->params;
 printf "Columns             %s\n",join',',map{$_->name}$stmt->columns
+;
 printf "Tables              %s\n",join',',$stmt->tables;
 printf "Where operator      %s\n",join',',$stmt->where->op;
 printf "Limit               %s\n",$stmt->limit;
 printf "Offset              %s\n",$stmt->offset;
 printf "Order Columns       %s\n",join',',map{$_->column}$stmt->order
+;
 __END__

Update: Added Readmore Tags

[renodino]

1. Last I checked, S::S didn't support subqueries (tho jZed, S::S's keeper, recently intimated that someone was picking up that particular gauntlet, so there may be something in the works)

2. I *think* /*..*/ comments should work; I gave jZed a patch to support them awhile ago, and I think I'm supporting it in DBD::Amazon.

3. S::S doesn't yet support CASE clauses. There's a simple DECODE(), but that probably doesn't address your query's needs

BTW: perhaps a <readmore> is in order ?

If you're feeling frisky, you can take a stab at adding support for all the above. I've been trying to find time to do it (along with better JOIN support, and some predicate optimizer code from DBD::Amazon), but as always, time is a rare commodity.

As it turns out, I may need to implement some of these myself RSN, esp. for DBD::eBay, so hopefully some of these may happen in the next few months.

While there are numerous modules for parsing SQL in various forms, and some that purport to translate SQL between DBMS's, I'm not aware of any others that implement a relational engine.

Correction:

I think my comments patch was for ANSI style i.e.,

select * -- this is an ANSI SQL comment
from mytable

That being said, the OP SQL isn't likely to sail thru S::S, w/ or wo/ comments.

[simonm]

I think SQL::Statement is the most capable such module available.

You might be best off extending it to do some pre-processing of comments and figuring out a way to stuff included subqueries into their own parse trees.

Not trivial work, but you could probably get some community help with it if/when you got stuck...

[jZed]

SQL::Statement doesn't handle subqueries or CASE statements. I welcome the help of anyone who would like to help add such support. AFAIK, it does currently support C-style comments when they occur at the start or end of a statement, but not when embedded in a statement. I intend to add support for embedded comments soon.

In terms of other modules that cover the same problem space, SQL::Translator has excellent support for DDL but not for SELECT statements; SQL::Routine is intdended to parse SQL but I'm not sure whether it can yet; DBIx::MyParse is a very promising looking module though I haven't tested it yet - it uses an embedded version of MySQL to give the parser a very robust (though MySQL specific) parser.