Query formation

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.

Please help me resolve
Result-set.JPG
Aravindan GPAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
order by nvl2(rordr_shrt_rqst_end_ts,2,1), FNSH_STK_CD desc,FSC_DESC,ACTN_DESC desc
0
sdstuberCommented:
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
Aravindan GPAuthor Commented:
Thanks for your response.

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.

Please help me resolve.

Regards.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Michael CarrilloInformation Systems ManagerCommented:
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
Aravindan GPAuthor Commented:
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
awking00Information Technology SpecialistCommented:
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
Aravindan GPAuthor Commented:
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.

Please help me resolve this.
0
awking00Information Technology SpecialistCommented:
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
Aravindan GPAuthor Commented:
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.

Please check the image..1st and 2nd, please help me now.
1st.JPG
2nd.JPG
0
Aravindan GPAuthor Commented:
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

Thanks for all your responses..

Regards..
0
Aravindan GPAuthor Commented:
If you can help me shorten above query. please help me.
0
sdstuberCommented:
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
Aravindan GPAuthor Commented:
I've requested that this question be closed as follows:

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

for the following reason:

The answer is correct.
0
Aravindan GPAuthor Commented:
I solved it myself.
0
sdstuberCommented:
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
sdstuberCommented:
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
sdstuberCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.