Another SQL Question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Another SQL Question

Hybrid View

  1. #1
    Join Date
    Jan 2001
    Posts
    515
    Is there a way to have something in your select clause, so that you can use it for order by clause , but not have it display? For example:

    select acno,1 ordering from rcb_acct
    where acno in ('555555666666666','333333333333333')
    and lgon_pref = 'MYN'
    union
    select acno,2 ordering from rcb_acct
    where lgon_pref = 'MYN'
    order by ordering, acno




    I don't want the ordering field returned. I just want it to put my results in the correct order.


  2. #2
    Join Date
    Feb 2001
    Posts
    123
    In SQL*Plus, issue the command:

    column ordering NOPRINT

    Then run the query. (Use the same command, but with PRINT instead of NOPRINT to undo the effect).

    HTH

    David.

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Assuming you want this somewhere other than SQL*PLUS :)

    Try this...

    SELECT
    ---ACNO
    FROM
    ---(
    ---select
    ------acno,1 ordering
    ---from
    ------rcb_acct
    ---where
    ------acno in ('555555666666666','333333333333333')---and
    ------lgon_pref = 'MYN'
    ---
    ---union ALL
    ---
    ---select
    ------acno,2 ordering
    ---from
    ------rcb_acct
    ---where
    ------lgon_pref = 'MYN'
    ---)
    order---by
    ---ordering,
    ---acno

    However, there are soome issues with your query as posted. First, any records where acno in (your list) will show up twice. That is because the rows are different because of the ordering column, so they will not be combined in the UNION. If that is what you wanted, then okay. However, if you simply wanted them to be sorted first in the list, then try this:

    SELECT
    ---ACNO
    FROM
    ---(
    ---SELECT
    ------ACNO,
    ------DECODE (
    ---------ACNO ,
    ------------'555555666666666',---1,
    ------------'333333333333333',---1,
    ------------2
    ---------)
    ---------AS ORDERING
    ---FROM
    ------RCB_ACCT
    ---WHERE
    ------LGON_PREF = 'MYN'
    ---)
    ORDER---BY
    ---ORDERING,
    ---ACNO

    This will perform much better and eliminate your duplicate rows.


    HTH,

    - Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width