You Asked

I need the logic to derive the direction and line hour

direction is Inbound if either one of org and dest or both falls under the jrny_in list and the rt list
direction is outbound if either one of org and dest or both falls under the jrny_out list and the rt list

line hour is

if org is not there and dest is there in jrny_in then dest_hr
if org and dest both in jrny_in then org_hr
if org is there and dest is not there in jrny_in then org_hr

if org is not there and dest is there in jrny_out then dest_hr
if org and dest both in jrny_out then org_hr
if org is there and dest is not there in jrny_out then org_hr

if the org and dest both doesnt fall under any jrny we need to exclude this

I have written this query, any other OPTIMIZED way to make it clean AND CLUTTER FREE?

with LiveSQL Test Case:

https://livesql.oracle.com/apex/livesql/file/content_FP98E1GAP83W57Z91WDU4KGNC.html

and Chris said...

I don't really understand what you're doing here. But I'm assuming that the query at the bottom of your LiveSQL script gives the correct result.

In any case, you don't need to keep querying jrny_in/out in the case expressions. Just outer join these in your main query. Then refer to the columns in the case.

This leads to some duplicate rows in your data. So in your inner query, group by the jrny columns as appropriate and take the min() of the expressions. For example:

select j.org, j.org_hr, j.dest, j.dest_hr, j.rt_id,
       min(case 
         when ji.stn is not null then 'In'
         when jo.stn is not null then 'Out'
       end) dir,
       min(case
         when j.dest = ji.stn and j.org <> ji.stn then dest_hr
         when ( j.dest <> ji.stn and j.org = ji.stn ) or 
              ( ji.stn = all (j.dest, j.org) ) then org_hr
         when j.dest = jo.stn and j.org <> jo.stn then dest_hr
         when ( j.dest <> jo.stn and j.org = jo.stn ) or 
              ( jo.stn = all (j.dest, j.org) ) then org_hr
       end) l_hr,
       min(total_patrons) total_patrons
from   jrny j 
inner join rt on (rt.rt_id = j.rt_id)
left join jrny_in ji
on    ji.stn in (j.org, j.dest)
left join jrny_out jo
on    jo.stn in (j.org, j.dest)
group  by j.org, j.org_hr, j.dest, j.dest_hr, j.rt_id
order by rt_id, org_hr, dest_hr;

ORG    ORG_HR   DEST    DEST_HR   RT_ID   DIR   L_HR   TOTAL_PATRONS   
  8000       10    1000        12 R1      In        12               2 
  1000       11    2000        14 R2      In        11               4 
  1000       12    7000        13 R3      In        12               5 
  5000       13    6000        14 R3      Out       13               2 
  1000       13   10000        14 R3      In        13               2 
  9000       13   10000        16 R3                                10 
  9000       13    5000        15 R4      Out       15               1

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Just what I need

Manikanth, October 21, 2017 - 7:14 am UTC

Thank you very much for your solution.

However, I need to clarify two things, what does min do here, another one is ji.stn = all (j.dest, j.org) ) , what will this do..check whether the ji.stn falls under both j.dest and j.org ?

I am sorry if these are too basic questions to ask, never visualized any solutions like this before.

October 23, 2017 - 10:42 am UTC

The full outer join can lead to duplicate orgs and dests. The "group by min" is to ensure you only get one row.

val = all ( ... )

means the value must match all those inside the brackets:

select * from dual
where  1 = all (1, 2);

no rows selected

select * from dual
where  1 = all (1, 1);

DUMMY   
X

So yes, in this case it means stn must match both dest and org.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Just what I need

More to Explore

SQL

Neat and optimized code

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Just what I need

More to Explore

SQL