SQL Passthrough DB2 - WITH clause problem

[saraimi]

I cannot get this query to run.  I've tried a lot of variations on this query - mostly trying either the WITH clause or an inline query.  I can use the SQL passthrough to DB2 with simpler queries, so I've left it out of the examples.  I've googled the error message, DB2 "WITH Clause", "common table expression", "CTE", and various combinations, and none of the dozens of results had anything that helps me resolve the issue.  I've also tried the inner query alone - that works.  I've tried these variants in another query tool, and have gotten the same results.  I know that means it's not a SAS problem, but I'm hoping someone can help, or point me to any useful documentation.

WITH clause VERSION:

   WITH inner_query AS 

          (SELECT oc.clm_cd, p.prod_ser_no, p.bld_date, p.vin,
                      p.in_srvc_date, p.in_srvc_trk_mlg,
                      year(p.in_srvc_date) as in_service_year,
                      case when p.prod_type_cd = 'ENGINE'
                              then p.prod_ser_no else '' end as eng_serial_number,
                      case when p.prod_type_cd = 'ENGINE'
                             then p.prod_mdl_cd else '' end as eng_model,
                      p.prod_mdl_cd, p.voc_cd,
                      p.prod_make_cd, p.prod_mdl_cd as fllc_internal_model,
                      p.prod_fam_cd, p.chass_mdl_cd, oc.flt_cd,
                      oc.flt_name, oc.fail_date, oc.trk_mlg_amt,
                     case when cfa.new_maj_comp_ser is not null
                             then oc.trk_mlg_amt else 0 end as eng_repl_mileage,
                     case when cfa.new_maj_comp_ser is not null
                             then oc.fail_date else '12-31-2999' end as eng_repl_date
           FROM wty_ddc.claims_fact_ddc_v oc
           INNER JOIN wty_ddc.product_dim_v p
           ON oc.prod_id = p.prod_id
           INNER JOIN wty_ddc.claims_fact_attr_ddc_v cfa
           ON cfa.clm_id = oc.clm_id
           WHERE trk_mlg_amt is not null and trk_mlg_amt > 0
                     and prod_type_cd in ('ENGINE','CHASSIS') )

   SELECT  clm_cd, prod_ser_no, bld_date, vin,  /*POST EDITED TO REMOVE TABLE ALIAS Q. from clm_cd */
            in_srvc_date, in_srvc_trk_mlg,
            in_service_year,
            prod_mdl_cd, voc_cd,
            prod_make_cd, fllc_internal_model,
            prod_fam_cd, chass_mdl_cd, flt_cd,
            flt_name, fail_date, trk_mlg_amt,
           eng_repl_mileage,
           eng_repl_date,
           max(eng_serial_number) OVER PARTITION BY (vin) as eng_serial_number,
           max(eng_model) OVER PARTITION BY (vin) as eng_model
     FROM inner_query
     ORDER BY vin, fail_date desc;

ERROR: CLI describe error: [IBM][CLI Driver][DB2/AIX64] SQL0104N  An unexpected token "clm_cd, prod_ser_no,
       bld_date, vin, in_sr" was found following "il_date desc) select".  Expected tokens may include: 
       "<space>".  SQLSTATE=42601

(My interpretation is that it objects to the outer query.  If I remove the columns list with "select * from inner_query" leaving the order by clause, THIS DOES WORK.  But there's no need for the WITH clause in that case - it's superfluous.  Anyway, part of the work I want to do here requires that PARTITION OVER feature.)

INLINE QUERY VERSION:

    SELECT clm_cd, prod_ser_no, bld_date, vin,   /*POST EDITED TO REMOVE TABLE ALIAS Q. from clm_cd */
            in_srvc_date, in_srvc_trk_mlg,
            in_service_year,
            prod_mdl_cd, voc_cd,
            prod_make_cd, fllc_internal_model,
            prod_fam_cd, chass_mdl_cd, flt_cd,
            flt_name, fail_date, trk_mlg_amt,
            eng_repl_mileage,
            eng_repl_date,
            max(eng_serial_number) OVER PARTITION BY (vin) as eng_serial_number,
            max(eng_model) OVER PARTITION BY (vin) as eng_model
     FROM (SELECT oc.clm_cd, p.prod_ser_no, p.bld_date, p.vin,
                                 p.in_srvc_date, p.in_srvc_trk_mlg,
                                year(p.in_srvc_date) as in_service_year,
                                case when p.prod_type_cd = 'ENGINE'
                                        then p.prod_ser_no else '' end as eng_serial_number,
                               case when p.prod_type_cd = 'ENGINE'
                                       then p.prod_mdl_cd else '' end as eng_model,
                               p.prod_mdl_cd, p.voc_cd,
                               p.prod_make_cd, p.prod_mdl_cd as fllc_internal_model,
                               p.prod_fam_cd, p.chass_mdl_cd, oc.flt_cd,
                               oc.flt_name, oc.fail_date, oc.trk_mlg_amt,
                               case when cfa.new_maj_comp_ser is not null
                                        then oc.trk_mlg_amt else 0 end as eng_repl_mileage,
                              case when cfa.new_maj_comp_ser is not null
                                       then oc.fail_date else '12-31-2999' end as eng_repl_date
             FROM wty_ddc.claims_fact_ddc_v oc
             INNER JOIN wty_ddc.product_dim_v p
             ON oc.prod_id = p.prod_id
             INNER JOIN wty_ddc.claims_fact_attr_ddc_v cfa
             ON cfa.clm_id = oc.clm_id
             WHERE trk_mlg_amt is not null and trk_mlg_amt > 0
                           and prod_type_cd in ('ENGINE','CHASSIS')
             ORDER BY vin, fail_date desc) 

ERROR: CLI describe error: [IBM][CLI Driver][DB2/AIX64] SQL0104N  An unexpected token "q.clm_cd, prod_ser_no,
       bld_date, vin, in_srvc_date, in" was found following "SELECT ".  Expected tokens may include: 
       "<space>".  SQLSTATE=42601

(Same interpretation here - I think the query would work if the outer select statement was just "select * from....", but that's useless.)

I have tried the same thing leaving out the two column specs using PARTITION BY, in case that was ultimately the source of the syntax problem.  However, this error code ultimately means the same thing as the earlier one:

ERROR: CLI describe error: [IBM][CLI Driver][DB2/AIX64] SQL0206N  "Q.CLM_CD" is not valid in the context
       where it is used.  SQLSTATE=42703

>>>  I think I could get the work done with several PROC SQL steps creating local temporary tables, but that will make this run much, much slower.

TIA!!

Steve

[fdsaaaa]

To me this SQL looks invalid . 

You are asking for 

q.clm_cd,

but you have not defined q anywhere

[saraimi]

Right - thanks.  I left out some of my debugging steps.  As a later debugging attempt, I threw an alias of "q" on the inline query and the select column specifications.  When I posted, I stripped off the alias on the virtual table definition, but forgot to remove it from the columns  

[fdsaaaa · Accepted solution]

Right. I can only suggest you do what I would in this situation . 

You have said select * from works, So change that to select 1 column (with no min/max or any other derivations) and see if that works . Gradually add 1 column at a time to see what is the first change you make that causes the error to occur

then google 

good luck

[saraimi]

Thanks for illuminating my blind spot!  This works fine until I include one of the columns involving PARTITION OVER.  I'll dig into the details of that syntax.

[saraimi]

...and the syntax for partition is this:

select max(whatever var) OVER (PARTITION BY vin),

....

[Tom]

---

@saraimi wrote:

Thanks for illuminating my blind spot!  This works fine until I include one of the columns involving PARTITION OVER.  I'll dig into the details of that syntax.

---

What type of variable is eng_serial_number ?  Perhaps it is a BIGINT or other data type that SAS does not support.  If so cast it as FLOAT or VARCHAR(). 

Note that SAS only has two data types, floating point numbers and fixed length character strings.

[saraimi]

Thanks, Tom - that is important to keep in mind.  In this case, it's character, and my problem was with the syntax of the OVER and PARTITION BY:

bad

   MAX(eng_serial_number) OVER PARTITION BY (vin);

good

   MAX(eng_serial_number) OVER (PARTITION BY vin);