Hi,
I have this (Original) query
select "FEED DESC",
"FEED ID",
"FEED TYPE",
"Load Region",
"CREATE USER",
min(CREATE_TIME),
"SUCCESS LOAD"
from (select distinct (fs.feed_description) as "FEED DESC",
fs.feed_id as "FEED ID",
ft.feed_type_description as "FEED TYPE",
fs.load_region as "Load Region",
case fs.load_region
/*If Region is 'ASIA'*/
when 'ASIA' then
(case
when
TO_CHAR(FROM_TZ(CAST(fsh.start_time AS
TIMESTAMP),
'Europe/London') AT TIME ZONE
'Asia/Singapore',
'hh24:mi:ss') <= '08:00:00' then
to_char(FROM_TZ(CAST(fsh.start_time
AS
TIMESTAMP),
'Europe/London') AT TIME ZONE
'Asia/Singapore',
'dd-MON-yyyy hh24:mi:ss')
end)
/*If Region is 'US'*/
when 'US' then
(case
when
TO_CHAR(FROM_TZ(CAST(fsh.start_time AS
TIMESTAMP),
'Europe/London') AT TIME ZONE
'US/Central',
'hh24:mi:ss') <= '08:00:00' then
to_char(FROM_TZ(CAST(fsh.start_time
AS
TIMESTAMP),
'Europe/London') AT TIME ZONE
'US/Central',
'dd-MON-yyyy hh24:mi:ss')
end)
/*If Region is 'EUR'*/
when 'EUR' then
(case
when to_char((select fsh.start_time
from feed_status_history fsh
where fi.feed_instance_id =fsh.feed_instance_id
and fs.feed_id =fi.feed_id
and fsh.status_id = 0),
'hh24:mi:ss') <=
'08:00:00' then
to_char((select fsh.start_time
from feed_status_history fsh
where fi.feed_instance_id =
fsh.feed_instance_id
and fsh.status_id = 0),
'dd-MON-yyyy hh24:mi:ss')
end)
/*If Region is 'ZURICH'*/
when 'ZH' then
(case
when
TO_CHAR(FROM_TZ(CAST(fsh.start_time AS
TIMESTAMP),
'Europe/London') AT TIME ZONE
'Europe/Zurich',
'hh24:mi:ss') <= '08:00:00' then
to_char(FROM_TZ(CAST(fsh.start_time
AS
TIMESTAMP),
'Europe/London') AT TIME ZONE
'Europe/Zurich',
'dd-MON-yyyy hh24:mi:ss')
end)
end as "CREATE_TIME",
(select max(fsh.create_user)
from mars.feed_status_history fsh
where fsh.status_id =0
and fsh.feed_instance_id = fi.feed_instance_id) as "CREATE USER",
(select count(fi.feed_instance_id)
from mars.feed_instance fi
where fs.feed_id = fi.feed_id
and fi.cob_date='05-Jan2011'
) as "SUCCESS LOAD"
from mars.feed_instance fi,
mars.feed_instance_file fif,
mars.feed_static fs,
mars.feed_status_history fsh,
mars.feed_type ft
where fs.feed_id = fi.feed_id
and fi.feed_instance_id = fif.feed_instance_id
and fi.feed_instance_id = fsh.feed_instance_id
and ft.feed_type_id = fs.feed_type_id
--and fif.load_mode = 'GET_LATEST'
and fs.feed_type_id in (1, 35)
and fi.cob_date = '05-Jan-2011'
and fi.status_id = 30
)
where CREATE_TIME is not null
-- and "SUCCESS LOAD" !=1
group by "FEED DESC",
"FEED ID",
"FEED TYPE",
"Load Region",
"CREATE USER",
"SUCCESS LOAD"
order by "Load Region","FEED DESC"
This query is executing fine but not giving me the exact results as I wanted because I want to use the "CREATE_TIME" calculated in the CASE statement to in turn query the "SUCCESS LOAD" part of the same query. I do not know if this is possible to I broke the query in 2 pieces using WITH clause. But still I am not able to get the results.
This is how I am trying
with Q1 as
(
select "FEED DESC",
"FEED ID",
"FEED TYPE",
"Load Region",
"CREATE USER",
min(CREATE_TIME)"Now",
"SUCCESS LOAD"
from (select distinct (fs.feed_description) as "FEED DESC",
fs.feed_id as "FEED ID",
ft.feed_type_description as "FEED TYPE",
fs.load_region as "Load Region",
case fs.load_region
/*If Region is 'ASIA'*/
when 'ASIA' then
(case
when
TO_CHAR(FROM_TZ(CAST(fsh.start_time AS
TIMESTAMP),
'Europe/London') AT TIME ZONE
'Asia/Singapore',
'hh24:mi:ss') <= '08:00:00' then
to_char(FROM_TZ(CAST(fsh.start_time
AS
TIMESTAMP),
'Europe/London') AT TIME ZONE
'Asia/Singapore',
'dd-MON-yyyy hh24:mi:ss')
end)
/*If Region is 'US'*/
when 'US' then
(case
when
TO_CHAR(FROM_TZ(CAST(fsh.start_time AS
TIMESTAMP),
'Europe/London') AT TIME ZONE
'US/Central',
'hh24:mi:ss') <= '08:00:00' then
to_char(FROM_TZ(CAST(fsh.start_time
AS
TIMESTAMP),
'Europe/London') AT TIME ZONE
'US/Central',
'dd-MON-yyyy hh24:mi:ss')
end)
/*If Region is 'EUR'*/
when 'EUR' then
(case
when to_char((select fsh.start_time
from feed_status_history fsh
where fi.feed_instance_id =fsh.feed_instance_id
and fs.feed_id =fi.feed_id
and fsh.status_id = 0),
'hh24:mi:ss') <=
'08:00:00' then
to_char((select fsh.start_time
from feed_status_history fsh
where fi.feed_instance_id =
fsh.feed_instance_id
and fsh.status_id = 0),
'dd-MON-yyyy hh24:mi:ss')
end)
/*If Region is 'ZURICH'*/
when 'ZH' then
(case
when
TO_CHAR(FROM_TZ(CAST(fsh.start_time AS
TIMESTAMP),
'Europe/London') AT TIME ZONE
'Europe/Zurich',
'hh24:mi:ss') <= '08:00:00' then
to_char(FROM_TZ(CAST(fsh.start_time
AS
TIMESTAMP),
'Europe/London') AT TIME ZONE
'Europe/Zurich',
'dd-MON-yyyy hh24:mi:ss')
end)
end as CREATE_TIME,
(select max(fsh.create_user)
from mars.feed_status_history fsh
where fsh.status_id =0
and fsh.feed_instance_id = fi.feed_instance_id) as "CREATE USER",
(select count(fi.feed_instance_id)
from mars.feed_instance fi
where fs.feed_id = fi.feed_id
and fi.cob_date='05-Jan2011'
) as "SUCCESS LOAD"
from mars.feed_instance fi,
mars.feed_instance_file fif,
mars.feed_static fs,
mars.feed_status_history fsh,
mars.feed_type ft
where fs.feed_id = fi.feed_id
and fi.feed_instance_id = fif.feed_instance_id
and fi.feed_instance_id = fsh.feed_instance_id
and ft.feed_type_id = fs.feed_type_id
--and fif.load_mode = 'GET_LATEST'
and fs.feed_type_id in (1, 35)
and fi.cob_date = '05-Jan-2011'
and fi.status_id = 30
)
where CREATE_TIME is not null
-- and "SUCCESS LOAD" !=1
group by "FEED DESC",
"FEED ID",
"FEED TYPE",
"Load Region",
"CREATE USER",
"SUCCESS LOAD"
order by "Load Region","FEED DESC"
)
select "Now" from q1 where "Now" < to_char('08:00:00','hh24:mi:ss')
My intention is to fetch the records from the "SUCCESS LOAD" where the previos clause "CREATE_TIME" is <=''08:00:00''
Please guide me on some other way or correcting me the same WITH clause if this can be done.
Thanks,
Aashish