with subqry (select distinct tbl1."idnum" "library"."sales" tbl4 inner join "library"."customer" tbl1 on tbl4."customerkey" = tbl1."customerkey" inner join "library"."time" tbl3 on tbl4."datekey" = tbl3."datekey" inner join "library"."product" tbl2 on tbl4."productkey" = tbl2."productkey" tbl1."country" in ('austria', 'denmark', 'belgium') , tbl3."fiscalyear" between '2011' , '2016') select t1."region" , t1."country" , subqry."idnum" "booking_idnum" , (case when subqry."idnum" null 'new' else 'existing' end) "booking_flag" , t1."idnum" , t1."customer" , t1. "bussiness" , t1."projectnum" , t1."projectname" , t1."projectowner" , t1."project_status" , sum(t1."project_value") , count(*) "transaction_count" , to_char(trunc(t1."createdate" / 100)) "yearmonth_calendar" , (case when t1."buss_id" in ('abc', 'def') 'mid' else null end) "drive_type" , (case when t1."idnum" in ('901023','1401516','2401056','2101444') 'vip' else 'standard' end) "acc_type" , t1."buss_id" "library"."project_details" t1 left join subqry on t1."idnum" = subqry."idnum" t1."country" in ('austria', 'denmark', 'belgium') , t1."buss_id" in ('abc', 'def') , t1."createdate" >= '20160101' group t1."region" , t1."country" , subqry."idnum" , t1."idnum" , t1."customer" , t1. "bussiness" , t1."projectnum" , t1."projectname" , t1."projectowner" , t1."project_status" , t1."buss_id" , t1."createdate" having sum(t1."project_value") > 0 union select t1."region" , t1."country" , subqry."idnum" "booking_idnum" , (case when subqry."idnum" null 'new' else 'existing' end) "booking_flag" , t1."idnum" , t1."customer" , t1."bussiness" , t1."projectnum" , t1."projectname" , t1."projectowner" , t1."project_status" , sum(t1."project_value") , count(*) "transaction_count" , to_char(trunc(t1."createdate" / 100)) "createdate_yearmonth_calendar" , (case when t1."buss_id" in ('abc', 'jkl', 'pqr') 'pro'else null end) "drive_type" , (case when t1."idnum" in ('901023','1401516','2401056','2101444') 'vip' else 'standard' end) "acc_type" , t1."buss_id" "library"."project_details" t1 left join subqry on t1."idnum" = subqry."idnum" t1."country" in ('austria', 'denmark', 'belgium') , t1."buss_id" in ('abc', 'jkl', 'pqr') , t1."createdate" >= '20160101' group t1."region" , t1."country" , subqry."idnum" , t1."idnum" , t1."customer" , t1."bussiness" , t1."projectnum" , t1."projectname" , t1."projectowner" , t1."project_status" , t1."buss_id" , t1."createdate" having sum(t1."project_value") > 0 hello,
the above query consists of:
- with clause - purpose of clause identify distinct
"idnum"customers purchased items between 2011 , 2016. - 1st query - primary purpose of query check if of selected
"idnum"exist in above clause. also, query supposed check if selected"idnum"match specific"buss_id", flag these'mid'in column named"drive_type". - 2nd query - query same thing 1st query. difference flags
"idnum"'pro'in column named"drive_type".
you might have noticed in both queries, there common "buss_id": 'abc'
- 1st query -
, (case when t1."buss_id" in ('abc', 'def') 'mid' else null end) "drive_type" - 2nd query -
, (case when t1."buss_id" in ('abc', 'jkl', 'pqr') 'pro'else null end) "drive_type"
i have tried union these queries display each line, falls both "buss_id", 2 separate lines of results. in other words, if single line have "buss_id" of 'abc', select statement display twice. 1 line "drive_type" flagged 'mid' , flagged 'pro'.
problem is, when run query following errors:
ora-00604: error occurred @ recursive sql level 1
ora-00904:"from$_subquery$_003"."datekey_0_0": invalid identifier
what more confusing me, can run combinations of where clause + 1st query or with clause + 2nd query without problems. when try run with clause + 1st query + union + 2nd query errors.
this great thing cte. try re-write 3 ctes, bet error message better if still one
with subqry (select distinct tbl1."idnum" "library"."sales" tbl4 inner join "library"."customer" tbl1 on tbl4."customerkey" = tbl1."customerkey" inner join "library"."time" tbl3 on tbl4."datekey" = tbl3."datekey" inner join "library"."product" tbl2 on tbl4."productkey" = tbl2."productkey" tbl1."country" in ('austria', 'denmark', 'belgium') , tbl3."fiscalyear" between '2011' , '2016'), q1 ( select t1."region" , t1."country" , subqry."idnum" "booking_idnum" , (case when subqry."idnum" null 'new' else 'existing' end) "booking_flag" , t1."idnum" , t1."customer" , t1. "bussiness" , t1."projectnum" , t1."projectname" , t1."projectowner" , t1."project_status" , sum(t1."project_value") , count(*) "transaction_count" , to_char(trunc(t1."createdate" / 100)) "yearmonth_calendar" , (case when t1."buss_id" in ('abc', 'def') 'mid' else null end) "drive_type" , (case when t1."idnum" in ('901023','1401516','2401056','2101444') 'vip' else 'standard' end) "acc_type" , t1."buss_id" "library"."project_details" t1 left join subqry on t1."idnum" = subqry."idnum" t1."country" in ('austria', 'denmark', 'belgium') , t1."buss_id" in ('abc', 'def') , t1."createdate" >= '20160101' group t1."region" , t1."country" , subqry."idnum" , t1."idnum" , t1."customer" , t1. "bussiness" , t1."projectnum" , t1."projectname" , t1."projectowner" , t1."project_status" , t1."buss_id" , t1."createdate" having sum(t1."project_value") > 0 ), q2 ( select t1."region" , t1."country" , subqry."idnum" "booking_idnum" , (case when subqry."idnum" null 'new' else 'existing' end) "booking_flag" , t1."idnum" , t1."customer" , t1."bussiness" , t1."projectnum" , t1."projectname" , t1."projectowner" , t1."project_status" , sum(t1."project_value") , count(*) "transaction_count" , to_char(trunc(t1."createdate" / 100)) "createdate_yearmonth_calendar" , (case when t1."buss_id" in ('abc', 'jkl', 'pqr') 'pro'else null end) "drive_type" , (case when t1."idnum" in ('901023','1401516','2401056','2101444') 'vip' else 'standard' end) "acc_type" , t1."buss_id" "library"."project_details" t1 left join subqry on t1."idnum" = subqry."idnum" t1."country" in ('austria', 'denmark', 'belgium') , t1."buss_id" in ('abc', 'jkl', 'pqr') , t1."createdate" >= '20160101' group t1."region" , t1."country" , subqry."idnum" , t1."idnum" , t1."customer" , t1."bussiness" , t1."projectnum" , t1."projectname" , t1."projectowner" , t1."project_status" , t1."buss_id" , t1."createdate" having sum(t1."project_value") > 0 ) select * q1 union select * q2 also consider changing first cte use different name idnum, maybe booking_idnum
with subqry (select distinct tbl1.booking_idnum -- etc 
Comments
Post a Comment