Solved

Query formation

Posted on 2012-03-21
422 Views
Hi,

Query:

SELECT   fnsh_stk_cd, fsc_desc, actn_desc, rordr_shrt_rqst_end_ts
FROM sir_fsc_cncltn_rqst
WHERE 1 = 1
AND work_dt = TO_DATE ('22/03/2012', 'dd/MM/yyyy')
AND in68_cd = 121
AND (fnsh_stk_cd, rordr_shrt_rqst_strt_ts) IN (
SELECT   fnsh_stk_cd, MAX (rordr_shrt_rqst_strt_ts)
FROM sir_fsc_cncltn_rqst
WHERE work_dt = TO_DATE ('22/03/2012', 'dd/MM/yyyy')
AND in68_cd = 121
GROUP BY fnsh_stk_cd)
ORDER BY fnsh_stk_cd, rordr_shrt_rqst_end_ts DESC;

I need order by based on rordr_shrt_rqst_end_ts is null in the begining of the result set
and then the combination of rordr_shrt_rqst_end_ts,(FNSH_STK_CD,FSC_DESC,ACTN_DESC) desc

The (FNSH_STK_CD,FSC_DESC,ACTN_DESC), I will be passing any values and based on which it should sort.

So basically in this result set, null part is to be at the begining and the remaining thing needs to be below it ( remaining is based on the rordr_shrt_rqst_end_ts,(FNSH_STK_CD,FSC_DESC,ACTN_DESC) desc)

Result set available in the image attached.

Result-set.JPG
0
Question by:neoarwin
• 8
• 6
• 2
• +1

LVL 73

Expert Comment

ID: 37747089
order by nvl2(rordr_shrt_rqst_end_ts,2,1), FNSH_STK_CD desc,FSC_DESC,ACTN_DESC desc
0

LVL 73

Expert Comment

ID: 37747097
or you could try...

order by rordr_shrt_rqst_end_ts desc nulls first, FNSH_STK_CD desc nulls first,FSC_DESC,ACTN_DESC desc nulls first
0

Author Comment

ID: 37747389

I elaborated in detail below.

By default  rordr_shrt_rqst_end_ts nulls should be first..

I will pass other columns like FNSH_STK_CD,FSC_DESC,ACTN_DESC  in the query and the first result set (By default  rordr_shrt_rqst_end_ts nulls should be first..) should be joined with this result set.

Example :

CASE 1: TS NULLS FIRST

FSC  ACTION   TS
110  SHORT    EMPTY
210  REORDER  EMPTY
310  SHORT    NOT EMPTY
410  SHORT    NOT EMPTY

CASE 2: TS NULLS FIRST AND THEN FSC DESC

FSC  ACTION   TS
210  SHORT    EMPTY
110  REORDER  EMPTY
410  SHORT    NOT EMPTY
310  SHORT    NOT EMPTY

CASE 3: TS NULLS FIRST AND THEN ACTION DESC

FSC  ACTION     TS
310  SHORT      EMPTY
210  REORDER    EMPTY
110  SHORT      NOT EMPTY
210  REORDER    NOT EMPTY

AND FOR REMAINING COLUMNS ALSO I HAVE TO WORK OUT SOMETHING LIKE THIS.

Regards.
0

LVL 9

Expert Comment

ID: 37747738
Try:

(select case rordr_shrt_rqst_end_ts when Null then 0 else 1 end as TS from sir_fsc_cncltn_rqst) desc

In your Order by to sort on Null vs Non-Null for rordr_shrt_rqst_end_ts.
0

Author Comment

ID: 37748232
This will return more than one row.
(select case rordr_shrt_rqst_end_ts when Null then 0 else 1 end as TS from sir_fsc_cncltn_rqst) desc

how to use it in my query , can yuo form and show me,please?
0

LVL 31

Expert Comment

ID: 37748338
I'm a little confused about what you want to accomplish. Can you provide some sample data with your expected results and a little more clarification on why it should be those results?
0

Author Comment

ID: 37748365
Can you please check the image i posted.

And also the above post where I explained by seperate cases.

Basically two sets of result set, in first set always ordered by rordr_shrt_rqst_end_ts is not null.

second set is based on the other columns and both result should be joined horizontally.

That is how it should be displayed.

0

LVL 31

Assisted Solution

awking00 earned 250 total points
ID: 37748764
Sorry, I missed the posted image. However, I'm still not sure precisely what your criteria, but you might try the following:
select fnsh_stk_cd, fsc_desc, actn_desc, rordr_shrt_rqst_end_ts from
(SELECT fnsh_stk_cd, fsc_desc, actn_desc, rordr_shrt_rqst_end_ts,
rank() over (order by rordr_shrt_rqst_end_ts nulls first) nullrows,
row_number() over (order by fnsh_stk_cd, fsc_desc desc, actn_desc desc) rn
FROM sir_fsc_cncltn_rqst
WHERE work_dt = TO_DATE ('22/03/2012', 'dd/MM/yyyy')
AND in68_cd = 121)
order by greatest(nullrows, rn);
0

Author Comment

ID: 37748859
Okay I will try to explain it properly.

SEE attached image.. named 1st and 2nd.

1st is the result set when rordr_shrt_rqst_end_ts is null ..

2nd is the result set when rordr_shrt_rqst_end_ts is not null..

I need the first result set to always be coming at the top of the entire result set and this result set can also be ordered by other columns of the same result set but it should always come at the begining of the entire result set.

The second result set needs to be ordered by all the possible columns and it should be horizaontally joined to the first result set.

1st.JPG
2nd.JPG
0

Author Comment

ID: 37748913
Framed it myself..don't know how i got stuck on this..

/* Formatted on 2012/03/21 23:35 (Formatter Plus v4.8.8) */
SELECT *
FROM ((SELECT *
FROM (SELECT   fnsh_stk_cd, fsc_desc, actn_desc,
rordr_shrt_rqst_end_ts
FROM sir_fsc_cncltn_rqst
WHERE 1 = 1
AND work_dt = TO_DATE ('22/03/2012', 'dd/MM/yyyy')
AND in68_cd = 121
AND (fnsh_stk_cd, rordr_shrt_rqst_strt_ts) IN (
SELECT   fnsh_stk_cd,
MAX (rordr_shrt_rqst_strt_ts)
FROM sir_fsc_cncltn_rqst
WHERE work_dt =
TO_DATE ('22/03/2012', 'dd/MM/yyyy')
AND in68_cd = 121
GROUP BY fnsh_stk_cd)
AND rordr_shrt_rqst_end_ts IS NULL
ORDER BY rordr_shrt_rqst_end_ts NULLS FIRST, fsc_desc DESC) h)
UNION ALL
(SELECT *
FROM (SELECT   fnsh_stk_cd, fsc_desc, actn_desc,
rordr_shrt_rqst_end_ts
FROM sir_fsc_cncltn_rqst
WHERE 1 = 1
AND work_dt = TO_DATE ('22/03/2012', 'dd/MM/yyyy')
AND in68_cd = 121
AND (fnsh_stk_cd, rordr_shrt_rqst_strt_ts) IN (
SELECT   fnsh_stk_cd,
MAX (rordr_shrt_rqst_strt_ts)
FROM sir_fsc_cncltn_rqst
WHERE work_dt =
TO_DATE ('22/03/2012', 'dd/MM/yyyy')
AND in68_cd = 121
GROUP BY fnsh_stk_cd)
AND rordr_shrt_rqst_end_ts IS NOT NULL
ORDER BY rordr_shrt_rqst_end_ts DESC) b)) c

Regards..
0

Author Comment

ID: 37749011
0

LVL 73

Expert Comment

ID: 37749088
you appear to have changed the question.

do you need anything more on the original sorting?
if not, please close this question and open a new one with your new requirements.
0

Author Comment

ID: 37751995
I've requested that this question be closed as follows:

Accepted answer: 0 points for neoarwin's comment #37748913

for the following reason:

0

Author Comment

ID: 37751748
I solved it myself.
0

LVL 73

Expert Comment

ID: 37751996
you are using the NULLS FIRST/LAST method shown in the first post.

however,  you are using a UNION which is completely unnecessary and inefficient

based on your final post,  the first posts would do what you asked
if it does not, please explain how it differs
0

LVL 73

Expert Comment

ID: 37752290
between the original question and your last version of the query you made a small change to the columns you want to use in the sort, but the methods shown above still apply.    Combining your last version with the "nulls first" shown above would look like this...

SELECT   fnsh_stk_cd, fsc_desc, actn_desc, rordr_shrt_rqst_end_ts
FROM sir_fsc_cncltn_rqst
WHERE 1 = 1
AND work_dt = TO_DATE ('22/03/2012', 'dd/MM/yyyy')
AND in68_cd = 121
AND (fnsh_stk_cd, rordr_shrt_rqst_strt_ts) IN (
SELECT   fnsh_stk_cd, MAX (rordr_shrt_rqst_strt_ts)
FROM sir_fsc_cncltn_rqst
WHERE work_dt = TO_DATE ('22/03/2012', 'dd/MM/yyyy')
AND in68_cd = 121
GROUP BY fnsh_stk_cd)
order by rordr_shrt_rqst_end_ts desc nulls first, fsc_desc DESC
0

LVL 73

Accepted Solution

sdstuber earned 250 total points
ID: 37752322
or,  even better, combining a variation on the analytics as posted by awking00 above,
try the query below.  Sorting wise, there is no change, but it's more efficient since it doesn't need to query the table twice.

SELECT   fnsh_stk_cd, fsc_desc, actn_desc, rordr_shrt_rqst_end_tsm
FROM (SELECT fnsh_stk_cd,
fsc_desc,
actn_desc,
rordr_shrt_rqst_end_tsm,
RANK() OVER (PARTITION BY fnsh_stk_cd ORDER BY rordr_shrt_rqst_strt_ts DESC) r
FROM sir_fsc_cncltn_rqst
WHERE work_dt = TO_DATE('22/03/2012', 'dd/MM/yyyy') AND in68_cd = 121)
WHERE r = 1
ORDER BY rordr_shrt_rqst_end_ts DESC NULLS FIRST, fsc_desc DESC;

Note,  if (fnsh_stk_cd,rordr_shrt_rqst_strt_ts)  is a unique pair then you could use row_number instead of rank
0

Featured Post

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theâ€¦
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  â€¦
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.