SQL Passthrough DB2 - WITH clause problem

saraimi — Thu, 12 Mar 2020 19:45:48 GMT

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') )

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

Re: SQL Passthrough DB2 - WITH clause problem

fdsaaaa — Thu, 12 Mar 2020 19:22:26 GMT

To me this SQL looks invalid .

You are asking for

q.clm_cd,

but you have not defined q anywhere

Re: SQL Passthrough DB2 - WITH clause problem

saraimi — Thu, 12 Mar 2020 19:40:47 GMT

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

Re: SQL Passthrough DB2 - WITH clause problem

fdsaaaa — Thu, 12 Mar 2020 22:28:30 GMT

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

Re: SQL Passthrough DB2 - WITH clause problem

saraimi — Tue, 17 Mar 2020 15:45:53 GMT

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.

Re: SQL Passthrough DB2 - WITH clause problem

saraimi — Tue, 17 Mar 2020 18:49:59 GMT

...and the syntax for partition is this:

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

....

Re: SQL Passthrough DB2 - WITH clause problem

Tom — Tue, 17 Mar 2020 21:29:05 GMT

@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.

Re: SQL Passthrough DB2 - WITH clause problem

saraimi — Tue, 17 Mar 2020 21:34:57 GMT

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);

topic Re: SQL Passthrough DB2 - WITH clause problem in SAS Data Management

SQL Passthrough DB2 - WITH clause problem

Re: SQL Passthrough DB2 - WITH clause problem

Re: SQL Passthrough DB2 - WITH clause problem

Re: SQL Passthrough DB2 - WITH clause problem

Re: SQL Passthrough DB2 - WITH clause problem

Re: SQL Passthrough DB2 - WITH clause problem

Re: SQL Passthrough DB2 - WITH clause problem

Re: SQL Passthrough DB2 - WITH clause problem