Here is the data in the table and B is defined as Char-20 and I need to sort this in asc order to get below mentioned output.
I would appriciate any thoughts.
I am planning something related to LOCATE 'K' adn then sort on digits before that and make it also, another option is using replace and remove 'K' , '-' and ' ' and then sort .
Now you only need to do a simple Sort starting from 3rd position through 4 bytes with a check on 3rd/4th byte as space or '-' to discount the first 2 records.
Oh.. I was (also) thinking it to be a SORT question. So, my above post is not useful. I am sorry for that.
I have now read carefully what you said and it makes sense to have 'k' as a fixed value and sort the values before that. However, the query that I wanted to write would be a little different.
I am getting same with your query as well. Also your query will not work if there are intermediate values like 801K - 999k as 801 will come after 1000 in char sort.
I agree with Rohit this will need thorough testing.
Sorry if I am pointing out the corrections or misses. But what I think if we creating something then it should be full proof and work for all conditions as we never know when these scenarios will occur
I guess for 1st UNION field2 will be '- ',for 2nd UNION field2 will be spaces and for third UNION it will be ' -' or 0K. With these values in Field2 what values I am getting seems to be correct
I would ask Rohit where will the '-' come in input
I believe the reason for difference between your result and mine is, that I was running the query in MS Access database. I work at a place where they don't have DB2, that's why I had to take help of the Access.
But, more importantly, the result could be achieved through the same query by mentioning Field2 as something else (e.g ' A', ' B', ' C' in the SELECT part of the query OR by doing another split in UNION) and I believe that is what the originator of the post asked i.e. other thoughts
Rahul, Thanks for your thoughts, but you where conditions are more likely very much hard coded and when I run your query I did not get the desired results. what if we have value like 30000k-49999k? your query needs another change right?
Chandan,
Thanks for your input too. But seems your SQL has some syntax errors which I have to take off and make it executable. also there is no <250k possible right now so that answer's your question.
Thanks for your idea's.
This is what I prepared and giving me the desired results.
Code:
select
inn2.FINAL_DESC,
from
(select
case
when substr(inn1.abc,3,(locate('K',inn1.abc)-3)) = ' ' then '0'
else substr(inn1.abc,3,(locate('K',inn1.abc)-3))
end as sort_1,
locate('K',inn1.abc)-3 sort_2,
case
when inn1.abc = ' K'
then concat(' ','None')
else inn1.abc
end as final_desc,
from
(SELECT
case when locate('>',col1) = 1 then col1
when col1 = ' ' then ' K'
else concat(' ',col1) end as abc
FROM A
) inn1
order by 3,2 ) inn2
*None->> When there is a space I have to translate that to None . I know I have not told that before but rest of the logic and the requirement is still intact. :-)
Actually I did not copy the query from mainframe screen as I am not allowed to do here so I had to write it down so human error resulted into syntax error
Also i would suggest to handle all possible data condition in query as much as possible as you never know when you will get data not getting selected in query and it's not good idea to change query frequently if its going to production
Glad to know it helped you in some way.
I would also like to know answer to Bill's question
chandan, you need to see how you can avoid SQLCODE -138 from your query, and what is 'QUANTITY' used for ? as LOCATE when gives 0 and when you do 0-2 in a substring then that's not true.
But thanks for your thoughts and logic.
Bill, very well pointed out. I have raised that question as I have also missed to observe.
Rahul, Thanks for your thoughts, but you where conditions are more likely very much hard coded and when I run your query I did not get the desired results. what if we have value like 30000k-49999k? your query needs another change right?
Yes, it would have needed other UNION parts to it.
I just want to keep things simple. Look at this query (replace 'Mid' by 'Substr'). You should get the desired result.
And I understand that we all feel much more comfortable at our own logic
Code:
SELECT Field1, '01' as Field2
FROM Table1
Where Mid(Field1,1,1)='<'
union SELECT Field1, '02' as Field2
FROM Table1
Where Mid(Field1,4,1)='k'
and Mid(Field1,1,1)<>'<'
union SELECT Field1, '03' as Field2
FROM Table1
Where Mid(Field1,5,1)='k'
and Mid(Field1,1,1)<>'<'
union SELECT Field1, '04' as Field2
FROM Table1
Where Mid(Field1,6,1)='k'
and Mid(Field1,1,1)<>'<'
union SELECT Field1, '05' as Field2
FROM Table1
Where Mid(Field1,7,1)='k'
and Mid(Field1,1,1)<>'<'
union SELECT Field1, '99' as Field2
FROM Table1
Where Mid(Field1,1,1)='>'
I didn't have actual data so had to create dummy data using Sysibm.Sysdummy1 and then use it for query. While creating the dummy data I used the column name as ' Quantity' and that I wrote by mistake.. my bad
And regarding -138 I would have tested it if I had actual data.. My intention was give you my thought and idea to overcome the problem...I will check for Sqlcode -138 again
At the end you got the working solution that is more important
so human error resulted into syntax error 
