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
... View more
