Another SQL Question

[lesstjm]

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.

[Dave_A]

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.

[chrisrlong]

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