| View previous topic :: View next topic |
| Author |
Message |
deepa12
Beginner
Joined: 05 Apr 2005
Posts: 131
Topics: 64
Location: chennai
|
 Posted: Tue Jun 20, 2006 6:06 am Post subject: sql to select same cols but order by different cols |
 |
|
I have a table with
MGRP CHAR(3)
YRWK CHAR(6)
i want 1 of the 2 kinds of SORTING under different conditions
to be effective
SELECT MGRP, YRWK
from t1
where mgrp>=:mgrp
when user supplies MGRP
order by yrwk desc mgrp asc
or
SELECT MGRP, YRWK
from t1
where yrwk >=:yrwk
when user supplies YRWK
order by yrwk DESC MGRP ASC
how to code this using 1 cursor
_________________
deepa |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Tue Jun 20, 2006 7:18 am Post subject: |
|
|
deepa12,
try this . We add a temp column 3 which contains the column to be sorted.
| Code: |
01 WS-ORD-IND PIC X(01).
EXEC SQL
DECLARE ORD_CSR CURSOR FOR
SELECT MGRP
,YRWK
,CASE WHEN :WS-ORD-IND = '1'
THEN MGRP
ELSE YRWK
END
FROM TABLE
WHERE mgrp >= :mgrp
AND yrwk >= :yrwk
ORDER BY 3
END-EXEC.
IF MGRP is suplied
move '1' to ws-ord-ind
move low-values to yrwk
else
move '2' to ws-ord-ind
move low-values to mgrp
end-if
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
deepa12
Beginner
Joined: 05 Apr 2005
Posts: 131
Topics: 64
Location: chennai
|
| Posted: Tue Jun 20, 2006 7:41 am Post subject: |
|
|
Hi Thanks
But if you take a look at the ORDER BY you will observe that
ORDER BY YRWK DESC in 1 case as 1st level and MGRP ASC 2nd level and vice versa
So can you pls suggest a different option
_________________
deepa |
|
| Back to top |
|
|
deepa12
Beginner
Joined: 05 Apr 2005
Posts: 131
Topics: 64
Location: chennai
|
| Posted: Tue Jun 20, 2006 7:43 am Post subject: |
|
|
Sorry In my 1st communication i was wrong
The order by Major & minor is viceversa under different conditions
_________________
deepa |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Tue Jun 20, 2006 7:58 am Post subject: |
|
|
deepa12,
You gotta realize that it is a good Idea to follow the rule of KISS( Keep it simple student). Declare 2 cursors with desired order and process the approriate cursor. At any given time you will only process 3 statements (open, fetch, close). It only makes a difference to no: of lines of code in the pgm which should never be benchmark for performance.
I am just curious as to why you CANNOT code 2 different cursors? Can you explain them ?
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
deepa12
Beginner
Joined: 05 Apr 2005
Posts: 131
Topics: 64
Location: chennai
|
| Posted: Tue Jun 20, 2006 8:19 am Post subject: |
|
|
Ok.. i can make 2 cursors But it is a TELON SCROLL LOGIC & it will probably add to complications
Still thinking of dynamic sqls
_________________
deepa |
|
| Back to top |
|
|
deepa12
Beginner
Joined: 05 Apr 2005
Posts: 131
Topics: 64
Location: chennai
|
| Posted: Wed Jun 21, 2006 11:57 am Post subject: |
|
|
I thought of the below
SELECT
case when 'y'='y' then MGRP_CD
when 'y'='n' then -1*INTEGER(tesco_yrwk))
end,
case when 'y'='y' then-1*INTEGER(tesco_yrwk)
when 'y'='n' then MGRP_CD
end,
MGRP_CD ,
TESCO_YRWK ,
TRACK_EFFV_DATE,
PLAN_RAG_STATUS
FROM GDB2DBAT.TXGR0MTS
WHERE RO_NO = 2271
ORDER BY 1 , 2;
The problem is i need to have both as 1 data type I cant have 1 col as CHAR and another as INT
Any suggestion
_________________
deepa |
|
| Back to top |
|
|
Jaya
Beginner
Joined: 02 Sep 2005
Posts: 77
Topics: 10
Location: Cincinnati
|
| Posted: Thu Jun 22, 2006 6:41 am Post subject: |
|
|
Hi,
For the initial requirement...
| Code: | 01 WS-ORD-IND PIC X(01).
EXEC SQL
DECLARE ORD_CSR CURSOR FOR
SELECT
CASE WHEN :WS-ORD-IND = '1'
THEN MGRP
ELSE YRWK
END,
CASE WHEN :WS-ORD-IND = '2'
THEN MGRP
ELSE YRWK
END
FROM TABLE
WHERE MGRP >= :mgrp
AND YRWK >= :yrwk
ORDER BY YRWK DESC, MGRP ASC
END-EXEC.
IF MGRP is suplied
move '1' to ws-ord-ind
move low-values to yrwk
else
move '2' to ws-ord-ind
move low-values to mgrp
end-if
|
If ORDER BY columns are not fixed, You can use
| Code: | | ORDER BY 1 DESC, 2 ASC |
depending on your requirement.
I am getting
DSNT408I SQLCODE = -581, ERROR: THE DATA TYPES OF THE RESULT-EXPRESSIONS OF A
CASE EXPRESSION ARE NOT COMPATIBLE
for your CASE expression
| Code: |
-1*INTEGER(tesco_yrwk)
|
You can again use the CHAR function to convert INTEGER back to CHAR
| Code: | 01 WS-ORD-IND PIC X(01).
EXEC SQL
DECLARE ORD_CSR CURSOR FOR
SELECT
CASE WHEN :WS-ORD-IND = '1'
THEN MGRP
ELSE CHAR(-1 * INTEGER(YRWK))
END,
CASE WHEN :WS-ORD-IND = '2'
THEN MGRP
ELSE CHAR(-1 * INTEGER(YRWK))
END
FROM TABLE
WHERE MGRP >= :mgrp
AND YRWK >= :yrwk
ORDER BY 1 DESC, 2 ASC
END-EXEC.
IF MGRP is suplied
move '1' to ws-ord-ind
move low-values to yrwk
else
move '2' to ws-ord-ind
move low-values to mgrp
end-if |
Hope this helps..
Thanks,
Jaya.
_________________
"Great spirits have always encountered violent opposition from mediocre minds."
-Albert Einstein |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
