Hi,
I have a requirement which consists of a cursor with certain 'order by' fields wherein, depending on the value of a field that lies in the middle of the sequence, the fields that follow that field in the 'order by' list will also change.
To show it with the example, the order by clause looks like this:
ORDER BY CTRY
, BNK
, CURR
, ACCT
, (CASE REC_TYPE
WHEN '001' THEN 1
WHEN '002' THEN 2
WHEN '003' THEN 3
WHEN '004' THEN 4
WHEN '006' THEN 5
WHEN '021' THEN 6
WHEN '022' THEN 7
WHEN '010' THEN 8
WHEN '013' THEN 9 END) ASC
, CUST
, AS_DT
, TYPE_CD
, AMT
Now the requirement is something like this.
While doing an order by, if the REC_TYPE value comes out to be 010,
then the rest of order by should happen on all the four fields that follow
the parantheses, i.e CUST, then by AS_DT, then by TYPE_CD, then by AMT.
However if the record is having any other record type, then the rest of the order by should not contain the fields which are in bold above.
i,e if the REC_TYPE is having a value 006, then the ORDER BY should happen ONLY on AS_DT and then on AMT.
Is this really doable? I was able to find some ORDER BY OF ORDER XXX type of explanations, but not sure if that will fit here.
Another aspect of this is that this ORDER BY clause is a part of the cursor which is a part of a stored proc and just as you open the cursor, all the values get displayed on an interface. There is no FETCH involved.
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
Order by Order of is feature of Db29 ...and it is not used for the purpose which you have mentioned ...Ashu it is not mandatory that fetch has to be there in the SP ..it should be there in the calling program which could be from front end or from back end itself ... normally SP's just open the cursor and returns back the control to the caller ....
i dont think your requirement is doable in an SQL ...or else you may have to use arrays and do a sort based on rec_types ....
Thanks Ashimer,
I guess we cannot get this in SQL. The problem is that the cursor when opened sends the result set on to AQT ( Advanced Query tool). I havent got much chance to find out if doing it with arrays will be an option here.
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
Hello,
Depending on how intense the SELECTs and how large the found set, you might also consider creating a temporary table from the found set that is the same for all variations and then define the cursor on the temporary table. Your processing logic would be the same for all cases, but the selection would be customized for each situation.
You might also consider having multiple cursors and using the appropriate cursor for each execution. Not my favorite, but . . .
