Re: Order by based on input

Richard Fazio

Re: Order by based on input
(in response to Walter Janißen)
Use a sort exit with 3GL or SyncSort with a SQL Input (nice feature).
faz

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Raj K
Sent: Tuesday, November 27, 2007 8:54 AM
To: [login to unmask email]
Subject: [DB2-L] Order by based on input

Order by based on input
------------------------

Select col1, col2, col3
from table 1
order by col1 <ASC/DESC>, col2 <ASC/DESC>, col3 <ASC/DESC>

The sort order for col1, col2, col3 are parameters to the program. Can
anyone
explain how to parametrize the sort order without using a dynamic sql.

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion
listserv that are being implemented to improve reliability and the
overall user experience of DB2-L. These changes are coming on November
30th. Details at http://www.idug.org

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Jani&#223;en

[DB2-L] Order by based on input
Hi

If you only want to change the column names depending on the select list, it is easy:

ORDER BY 1, 2, 3

If you want to change the ASC/DESC, I think the only way would be, to code the SQL for all possible combinations and execute the approriate one.


Bitte richten Sie, falls Sie es nicht schon tun, in Zukunft alle E-Mails nicht mehr an Einzelpersonen, sondern
immer an das Postfach "LI25103D - Entwicklungssupport - Gruppenpostfach".
Ansonsten kann sich die Bearbeitung Ihrer E-Mail bedingt durch Urlaub, Gleittag, Krankheit, Projektarbeit in Köln,
ZEITplus o.ä. verzögern.
Vielen Dank!

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Laufzeitarchitektur
Hans-Böckler-Str. 36
D-40477 Düsseldorf
Tel.: +49 211 477-2928
Fax: +49 211 477-2615
mailto:[login to unmask email]
http:// www.itergo.com
Vorsitzender des Aufsichtsrats: Dr. Torsten Oletzky
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Dr. Michael Regauer, Wolfgang Schön.
Sitz: Düsseldorf | Handelsregister: Amtsgericht Düsseldorf, HRB 37996


-----Ursprüngliche Nachricht-----
Von: DB2 Data Base Discussion List [mailto:[login to unmask email] Im Auftrag von Raj K
Gesendet: Dienstag, 27. November 2007 15:54
An: [login to unmask email]
Betreff: [DB2-L] Order by based on input

Order by based on input
------------------------

Select col1, col2, col3
from table 1
order by col1 <ASC/DESC>, col2 <ASC/DESC>, col3 <ASC/DESC>

The sort order for col1, col2, col3 are parameters to the program. Can anyone explain how to parametrize the sort order without using a dynamic sql.

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Trovijo Jr (UOL)

Re: Order by based on input
(in response to Richard Fazio)
Create several sqls and choose one?

> Order by based on input
> ------------------------
>
> Select col1, col2, col3
> from table 1
> order by col1 <ASC/DESC>, col2 <ASC/DESC>, col3 <ASC/DESC>
>
> The sort order for col1, col2, col3 are parameters to the program. Can anyone
> explain how to parametrize the sort order without using a dynamic sql.
>

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

James Campbell

Re: Order by based on input
(in response to Walter Trovijo Jr (UOL))
Option 1)
select
case
when :s1 = 1 then col1
when :s1 = 2 then col2
...
else <default>
end as sort_col_1,
case
when :s2 = 1 then col1
when :s2 = 2 then col2
...
else <default>
end as sort_col_2,
...
col1, col2, col3, ...
from table_1
order by sort_col_1, sort_col_2, ....

and just set :s1, :s2 etc to identify the columns you want. You may need to cast col1, col2 etc
into a common datatype.

The only, minor, problem with this is that DB2 cannot make use of any index for ordering -
you are guaranteed to have a sort.

Option 2)
select col1, col2, col3, ...
from table_1
where :sid = 1
order by col1, col2, col3
union all
select col1, col2, col3, ...
from table_1
where :sid = 2
order by col1, col3, col2
union all
....

You need to know the ordering options when you write the SQL. Due to SQL pruning only
one select will actually be executed.

Which is better? That depends on what you actually need.

As for your other question about paramaterising scrollability: I believe that there is a
technique. However it is not a technique that I would ever use, if for no other reason than it is
undocumented in any manual I've ever seen. When it is changed (as I would have to
assume) who will maintain the program if I am not around? And, of course, it is not hard to
imagine the reaction to any PMR complaining about the change! If you happen to discover it,
pat yourself on the back and don't use it.

James Campbell

On 27 Nov 2007 at 9:54, Raj K wrote:

> Order by based on input
> ------------------------
>
> Select col1, col2, col3
> from table 1
> order by col1 <ASC/DESC>, col2 <ASC/DESC>, col3 <ASC/DESC>
>
> The sort order for col1, col2, col3 are parameters to the program. Can anyone
> explain how to parametrize the sort order without using a dynamic sql.
>

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm