Reuse SQL Alias Name in Case Statement

building semantic layer
1

I am trying to create derived table using below query but I am struck , I want to use below original query case statement alias name nn_due_date for another case statement in original query calculation but when I use I am getting error ORA - 00904: new_due_date":invalid identifier "
can you please help me, I want to use like below

case when a.DW_DX_DCRP_STRT_DT - DCRP_CRE_DT <= nn_due_date
then 1 else 0
end as wit_45_ct,
case when a.DW_DX_DCRP_STRT_DT - DCRP_CRE_DT > nn_due_date
 then 1 else 0
end as grt_45_ct

Original query:

select wit_45_ct,grt_45_ct,
case when cnt = 1 and trim(to_char(new_due_date, ‘DAY’, ‘NLS_DATE_LANGUAGE=ENGLISH’)) in(‘MONDAY’,‘TUESDAY’,‘WEDNESDAY’,‘THURSDAY’) then new_due_date+1
when cnt = 1 and trim(to_char(new_due_date, ‘DAY’, ‘NLS_DATE_LANGUAGE=ENGLISH’)) = ‘FRIDAY’ then new_due_date+3
end as nn_due_date

from (
select a.DW_DX_DCRP_STRT_DT,
a.DW_DX_DCRP_STRT_DT+45 as actual_due_date,
to_char(a.DW_DX_DCRP_STRT_DT+45, ‘DAY’, ‘NLS_DATE_LANGUAGE=ENGLISH’),
case when trim(to_char(a.DW_DX_DCRP_STRT_DT+45, ‘DAY’, ‘NLS_DATE_LANGUAGE=ENGLISH’)) not in (‘SATURDAY’,‘SUNDAY’) then a.DW_DX_DCRP_STRT_DT+45
when trim(to_char(a.DW_DX_DCRP_STRT_DT+45, ‘DAY’, ‘NLS_DATE_LANGUAGE=ENGLISH’)) =‘SATURDAY’ then a.DW_DX_DCRP_STRT_DT+47
when trim(to_char(a.DW_DX_DCRP_STRT_DT+45, ‘DAY’, ‘NLS_DATE_LANGUAGE=ENGLISH’)) =‘SUNDAY’ then a.DW_DX_DCRP_STRT_DT+46
end as new_due_date,
(select count(1) from hldy where to_date(hldy_id,‘YYYYMMDD’) =
case when trim(to_char(a.DW_DX_DCRP_STRT_DT+45, ‘DAY’, ‘NLS_DATE_LANGUAGE=ENGLISH’)) not in (‘SATURDAY’,‘SUNDAY’) then a.DW_DX_DCRP_STRT_DT+45
when trim(to_char(a.DW_DX_DCRP_STRT_DT+45, ‘DAY’, ‘NLS_DATE_LANGUAGE=ENGLISH’)) =‘SATURDAY’ then a.DW_DX_DCRP_STRT_DT+47
when trim(to_char(a.DW_DX_DCRP_STRT_DT+45, ‘DAY’, ‘NLS_DATE_LANGUAGE=ENGLISH’)) =‘SUNDAY’ then a.DW_DX_DCRP_STRT_DT+46
end
)as cnt,
case when a.DW_DX_DCRP_STRT_DT - DCRP_CRE_DT <= nn_due_date
then 1 else 0
end as wit_45_ct,
case when a.DW_DX_DCRP_STRT_DT - DCRP_CRE_DT > nn_due_date
 then 1 else 0
end as grt_45_ct

from dx_dcrp a, case_cnty c,csrt e,cnty d

)

vsenni (BOB member since 2015-11-10)

2

So you define a column cnt which is used in the outer query, and that outer query includes a column nn_due_date which you’re referencing from the inner query. That’s not going to work – you can’t reference a defined column from the outer query in the inner query. I’d suggest refactoring the query to use a WITH clause in which you define all the necessary columns.

With that said, a couple of things in your query caught my eye. This section:

               (SELECT COUNT (1)
                  FROM hldy
                 WHERE TO_DATE (hldy_id, 'YYYYMMDD') =
                          CASE
                             WHEN TRIM (
                                     TO_CHAR (a.DW_DX_DCRP_STRT_DT + 45,
                                              'DAY',
                                              'NLS_DATE_LANGUAGE=ENGLISH')) NOT IN
                                     ('SATURDAY', 'SUNDAY')
                             THEN
                                a.DW_DX_DCRP_STRT_DT + 45
                             WHEN TRIM (
                                     TO_CHAR (a.DW_DX_DCRP_STRT_DT + 45,
                                              'DAY',
                                              'NLS_DATE_LANGUAGE=ENGLISH')) =
                                     'SATURDAY'
                             THEN
                                a.DW_DX_DCRP_STRT_DT + 47
                             WHEN TRIM (
                                     TO_CHAR (a.DW_DX_DCRP_STRT_DT + 45,
                                              'DAY',
                                              'NLS_DATE_LANGUAGE=ENGLISH')) =
                                     'SUNDAY'
                             THEN
                                a.DW_DX_DCRP_STRT_DT + 46
                          END)
                  AS cnt,

can be condensed quite a bit:

(SELECT COUNT(1)
  FROM hldy
 WHERE TO_DATE (hldy_id, 'YYYYMMDD') = a.DW_DX_DCRP_STRT_DT +                
        CASE TO_CHAR (a.DW_DX_DCRP_STRT_DT + 45,'DAY','NLS_DATE_LANGUAGE=ENGLISH')
             WHEN 'SATURDAY'
             THEN 47
             WHEN 'SUNDAY'
             THEN 46            
             ELSE 45
             END) as cnt,

And this looks suspicious:

WHEN a.DW_DX_DCRP_STRT_DT - DCRP_CRE_DT <= nn_due_date

Assuming all three referenced columns are dates, it’s not going to work. You can’t subtract one date from another and get a third.

joepeters :us: (BOB member since 2002-08-29)

3

Thank you…

vsenni (BOB member since 2015-11-10)

4

I’m trying to create a derived table using a WITH clause (which works perfect in SQL).

When I check the syntax for the derived table I get an error (message below). Is it possible to use the WITH as sql in a derived table?
Capture.JPG

Eileen King :us: (BOB member since 2002-07-10)

5

I was able to with Oracle; I’m not sure if there’s anything different about DB2. I created a derived table with the following SQL:

with a as (select * from dual) select * from a

When I use the object from that table, it generates the following SQL:

SELECT DISTINCT
  Table__85.DUMMY
FROM
  ( with a as (select * from dual) select * from a) Table__85

Maybe try wrapping the whole thing with a select * from ( ) ?

joepeters :us: (BOB member since 2002-08-29)

6

I tried again. I still get the error message.

I’m wondering if it’s something that could be tweeked in a parameter…time to look deepter!

Eileen King :us: (BOB member since 2002-07-10)

7

If you pluck out the SQL from the WebI report and run it in another query tool, do you get the same result?

joepeters :us: (BOB member since 2002-08-29)

8

I can run it successful in AQT

Eileen King :us: (BOB member since 2002-07-10)

9

Strange. The error you’re getting is a syntax error from the database. I’m not sure why the same SQL would work elsewhere.

joepeters :us: (BOB member since 2002-08-29)