IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Special Sorting help required
IBM Mainframe Forums -> DB2
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
PostPosted: Tue Mar 17, 2015 12:16 am
 Reply with quote
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 .

Code:
                             B                                   
  ------------------------------                       
  > 5000K                                                 
  1000K - 1999K                         
  2000K - 4999K                         
  250K - 999K


OutPut:

Code:

                                   B                                   
  ------------------------------                       
  250K - 999K                                                 
  1000K - 1999K                         
  2000K - 4999K                         
  > 5000K                           
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA
PostPosted: Tue Mar 17, 2015 1:03 am
 Reply with quote
My suggestion would be to first make the input records in sync. So that we have the same columns to Sort. Have a look below:
Code:
                             B                                   
  ------------------------------                       
  > 5000K                                                 
    1000K - 1999K                         
    2000K - 4999K                         
     250K -  999K

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.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
PostPosted: Tue Mar 17, 2015 2:29 am
 Reply with quote
Thanks for your thoughts!!

Quote:
My suggestion would be to first make the input records in sync.


Are you suggesting to write a query for this alignment and what if it get expanded to 20000K-29999K value?

This is what I tried and result looks okay but need more testing.

Code:
select innr.abc as final_sort from
  ( SELECT LOCATE('K',RNG_T),
   substr(RNG_T,1,
  (LOCATE('K',RNG_T)-1)),
    RNG_T  as abc
   from B
   order by  1, 2) innr
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Tue Mar 17, 2015 3:28 am
 Reply with quote
If you UFF the first value, use EDIT=, into an extension of the record, then SORT on that, it should be what you want.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Tue Mar 17, 2015 4:56 am
 Reply with quote
Hah. Didn't look at which part of the forum it was in. Sorry :-)
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
PostPosted: Tue Mar 17, 2015 7:49 am
 Reply with quote
Bill, that's fine. . Your sort solution may be also helpful for others as always did.

Thank You!!
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA
PostPosted: Tue Mar 17, 2015 10:02 am
 Reply with quote
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.

Here is what I took as an input:
Code:
Field1
> 5000k
1000k - 1999k
2000k - 2999k
250k - 999k
3000k - 3999k
4000k - 4999k


and I used the query:

Code:

SELECT Field1, Substr(Field1,6,2) as Field2
FROM Table1
Where Substr(Field1,4,1)='k'

UNION
SELECT Field1, Substr(Field1,6,2) as Field2
FROM Table1
Where Substr(Field1,5,1)='k'

Union
SELECT Field1, Substr(Field1,6,2) as Field2
FROM Table1
Where Substr(Field1,4,1)<>'k'

Order by Field2 asc, Field1 asc;


and the result (column 1) is:

Code:

Field1
250k - 999k
1000k - 1999k
2000k - 2999k
3000k - 3999k
4000k - 4999k
> 5000k
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
PostPosted: Tue Mar 17, 2015 10:05 am
 Reply with quote
Hi Rohit,

Your query looks fine for the given input.

I tried to test it with adding one more row as ' < 250K' then the query seems to be breaking.

As per your requirement '< 250K' should come first but it will come in between.

I am not sure about what will be your expected output for this scenario or you will have such case or not but just though of sharing one scenario icon_smile.gif

Thanks,
Chandan
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
PostPosted: Tue Mar 17, 2015 10:24 am
 Reply with quote
For my above post the output is coming as below


Code:
250k - 999k
1000k - 1999k
2000k - 2999k
3000k - 3999k
4000k - 4999k
< 250 K
> 5000k


Hi Rahul,

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 icon_smile.gif

Regards,
Chandan
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
PostPosted: Tue Mar 17, 2015 12:42 pm
 Reply with quote
I tried with below input

Code:
                        B                                   
  ------------------------------                       
  > 5000K                                                 
  1000K - 1999K                         
  2000K - 2999K 
  3000K - 3999K
  4000K - 4999K                       
  250K -  799K
  800K - 999K
  < 250K


The query I used is as below

Code:
SELECT LOCATE('K' , B ) -
           CASE WHEN LOCATE ('>' , 'B) > 0 THEN LOCATE ('>' , 'B) + 1
                WHEN LOCATE ('<' , 'B) > 0 THEN LOCATE ('<' , 'B) + 2
                ELSE 0 END
          ,CAST(
            SUBSTR(QUANTITY,
               (CASE WHEN LOCATE ('>' , 'B) > 0 THEN LOCATE ('>' , 'B) + 2
                     WHEN LOCATE ('<' , 'B) > 0 THEN LOCATE ('<' , 'B) + 2
                     ELSE 1 END),
               (CASE WHEN LOCATE ('>' , 'B) > 0
                         THEN LOCATE('K' , B ) - 2 - LOCATE ('>' , 'B)
                     WHEN LOCATE ('<' , 'B) > 0
                         THEN LOCATE('K' , B ) - 2 - LOCATE ('<' , 'B)
                     ELSE   LOCATE('K' , B ) - 2))
                 AS INTEGER)
           , B
FROM TABLE
ORDER BY 1,2


Query Output is as below for column B

Code:
             B                                   
  ------------------------------                       
 < 250K
250K -  799K
800K - 999K
1000K - 1999K                         
2000K - 2999K 
3000K - 3999K
4000K - 4999K     
  > 5000K                   


let me know if this works or if I missing anything

Regards,
Chandan
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Tue Mar 17, 2015 1:03 pm
 Reply with quote
Not your problem, it's there in the original, but where would 5000K go?
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA
PostPosted: Tue Mar 17, 2015 6:40 pm
 Reply with quote
Ok. I modified my query a bit:
Code:
SELECT Field1, Substr(Field1,6,2) as Field2
FROM Table1
Where Substr(Field1,4,1)='k'

UNION SELECT Field1, '  ' as Field2
FROM Table1
Where Substr(Field1,4,1)<>'k'
and Substr(Field1,1,1)='<'

UNION SELECT Field1, Substr(Field1,6,2) as Field2
FROM Table1
Where (Substr(Field1,4,1)<>'k')
and (Substr(Field1,1,1)<>'<')

ORDER BY Field2, Field1;


This gives me an output (column 1) as:

Code:
Field1
< 250k
250k - 799k
800k - 999k
1000k - 1999k
2000k - 2999k
3000k - 3999k
4000k - 4999k
> 5000k

Please try and let me know if you see any problem.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
PostPosted: Wed Mar 18, 2015 9:30 am
 Reply with quote
Hi Rahul,

Not sure what I am missing in your above query but still I am getting wrong output with above query. May be the data which we are using.

I am getting the output as below

Code:
Field1                                Field2 ( 2 bytes)
< 250 K                                 '  '
1000K - 1999K                           ' -'
2000K - 1999K                           ' -'
3000K - 1999K                           ' -'
4000K - 1999K                           ' -'
250K - 799K                             '- '
800k - 999k                             '- '
> 5000k                                  0K


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

Regards,
Chandan
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA
PostPosted: Wed Mar 18, 2015 7:02 pm
 Reply with quote
Thanks for your concern Chandan.

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 icon_smile.gif

Thank you.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
PostPosted: Thu Mar 19, 2015 3:50 am
 Reply with quote
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


Output:

Code:
 FINAL_DESC
 --------------------------------
   None
   250K - 999K                   
   1000K - 1999K                 
   2000K - 4999K                 
 > 5000K


*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. :-)
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Thu Mar 19, 2015 4:36 am
 Reply with quote
And still nowhere to account for 5000K :-)
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
PostPosted: Thu Mar 19, 2015 8:35 am
 Reply with quote
Hi Rohit,

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 icon_cry.gif so human error resulted into syntax error icon_smile.gif

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 icon_biggrin.gif

Regards,
Chandan
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
PostPosted: Thu Mar 19, 2015 11:34 pm
 Reply with quote
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.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA
PostPosted: Fri Mar 20, 2015 12:58 am
 Reply with quote
Quote:
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 icon_smile.gif

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)='>'

ORDER BY Field2, Field1;

Input:
Code:
Field1
> 50000k
1000k - 1999k
2000k - 2999k
250k - 799k
3000k - 3999k
4000k - 4999k
< 250k
800k - 999k
5000k - 39999k
40000k - 49999k
50000k

Output:
Code:
Field1
< 250k
250k - 799k
800k - 999k
1000k - 1999k
2000k - 2999k
3000k - 3999k
4000k - 4999k
5000k - 39999k
40000k - 49999k
50000k
> 50000k
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
PostPosted: Fri Mar 20, 2015 8:59 am
 Reply with quote
Hi Rohit,

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 icon_redface.gif

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 icon_biggrin.gif

Regards,
Chandan
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
PostPosted: Sat Mar 21, 2015 12:00 am
 Reply with quote
Rahul, MID is not compatible with DB2 on Zos.
Chandan, No worries. You were very helpful.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA
PostPosted: Sat Mar 21, 2015 12:04 am
 Reply with quote
Quote:
I just want to keep things simple. Look at this query (replace 'Mid' by 'Substr'). You should get the desired result.

Let's end this thread here please.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
PostPosted: Sat Mar 21, 2015 12:45 am
 Reply with quote
Quote:
Let's end this thread here please.


That's Okay Sir!! Thanks for your inputs.
Back to top
View user's profile Send private message
View previous topic : : View next topic  
Post new topic   Reply to topic All times are GMT + 6 Hours
Forum Index -> DB2

 

Similar Topics
Topic Forum Replies
No new posts Help required to reset decimal points... DFSORT/ICETOOL 10
No new posts COBOL sorting, with input GDG base COBOL Programming 7
No new posts Sorting Date Field DFSORT/ICETOOL 4
This topic is locked: you cannot edit posts or make replies. Automation need help in sorting the data DFSORT/ICETOOL 38
No new posts Sorting a record spanned over multipl... DFSORT/ICETOOL 13
Search our Forums:


Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us