MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Convert rows with numbers to string with flags
Goto page 1, 2  Next
 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
bauer
Intermediate


Joined: 10 Oct 2003
Posts: 314
Topics: 48
Location: germany

PostPosted: Tue Aug 17, 2021 11:27 am    Post subject: Convert rows with numbers to string with flags Reply with quote

Hi,

I have a table something like this:

Code:

Field1    Field2
------------------
A         1   
B         1
B         3
C         3


Field2 is limited from 1 (minvalue) to lets say 5 (max value)


I like to retrieve from this table with the where condition where field1 = A now an string like this YNNNN, first position = Y because 1 is assigned to A.

With WHERE field2 = B the result should be YNYNN, number 1 and 3 is assigned to B, so position 1 and 3 are Y in the string.

With WHERE field1 = C the result should be NNYNN.


Listagg can convert rows to a single column. So far ok.

But any comments how to build the string?

I'm confused.

Thank you,
bauer
Back to top
View user's profile Send private message
bauer
Intermediate


Joined: 10 Oct 2003
Posts: 314
Topics: 48
Location: germany

PostPosted: Tue Aug 17, 2021 1:13 pm    Post subject: Reply with quote

Ok, using a support table which contains all numbers from MINVALUE up to MAXVALUE this solution works.

Code:


CREATE TABLE MYSCHEMA.ORIGINAL
       (F1 SMALLINT, F2 CHAR(1));

CREATE TABLE MYSCHEMA.SUPPORT
       (F1 SMALLINT) ;

INSERT INTO MYSCHEMA.SUPPORT VALUES (1);
INSERT INTO MYSCHEMA.SUPPORT VALUES (2);
INSERT INTO MYSCHEMA.SUPPORT VALUES (3);
INSERT INTO MYSCHEMA.SUPPORT VALUES (4);
INSERT INTO MYSCHEMA.SUPPORT VALUES (5);

INSERT INTO MYSCHEMA.ORIGINAL VALUES (1,'A');
INSERT INTO MYSCHEMA.ORIGINAL VALUES (1,'B');
INSERT INTO MYSCHEMA.ORIGINAL VALUES (3,'B');
INSERT INTO MYSCHEMA.ORIGINAL VALUES (3,'C');

SELECT LISTAGG
           (CASE WHEN (T2.F1 IS NULL) THEN 'N'
                                      ELSE 'Y'
            END
           )
       WITHIN GROUP (ORDER BY T1.F1 ASC)
       AS FLAGLIST

FROM MYSCHEMA.SUPPORT  T1

LEFT  OUTER JOIN
      ( SELECT T3.F1 FROM MYSCHEMA.ORIGINAL T3
        WHERE T3.F2 = 'C'
      ) T2
      ON T1.F1 = T2.F1




If anybody has a better solution let me know,
thank you,

bauer
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12354
Topics: 75
Location: San Jose

PostPosted: Tue Aug 17, 2021 4:05 pm    Post subject: Reply with quote

bauer,

If you only have 5 different values then a simple case statement should give you the desired results

Something like this

Code:

SELECT * FROM (SELECT CASE WHEN MAX(F1) = 1 THEN CHAR('YNNNN')   
                           WHEN MAX(F1) = 2 THEN CHAR('NYNNN')   
                           WHEN MAX(F1) = 3 THEN CHAR('NNYNN')   
                           WHEN MAX(F1) = 4 THEN CHAR('NNNYN')   
                           WHEN MAX(F1) = 5 THEN CHAR('NNNNY')   
                           ELSE CHAR('NNNNN')                   
                           END AS SRCHSTR                       
                 FROM MYSCHEMA.ORIGINAL                                   
                GROUP BY F2) A                                   
  WHERE A.SRCHSTR = 'YNNNN'                                     
;                                                               


You would pass the string to be searched as a host variable
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
bauer
Intermediate


Joined: 10 Oct 2003
Posts: 314
Topics: 48
Location: germany

PostPosted: Wed Aug 18, 2021 4:50 am    Post subject: Reply with quote

Hi Kolusu,

thank you for your answer - but this is not 100% the situation, see my sample.

There might be expected results like 'YNYNN' (or more digits like 'NNNNNNNY......) Sad .

This means: Currently I will use my solution. I have no better ideas than using this "myschema.support" table to create the result.

kind regard and thank you for your time,
bauer
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12354
Topics: 75
Location: San Jose

PostPosted: Wed Aug 18, 2021 11:03 am    Post subject: Reply with quote

bauer,

I guess I am missing something here. What version of DB2 did the above posted query run? I tried to run on DB2 V11 and it ends with error on GROUP statement.

Can you please post the output from your query ?
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
bauer
Intermediate


Joined: 10 Oct 2003
Posts: 314
Topics: 48
Location: germany

PostPosted: Thu Aug 19, 2021 1:18 am    Post subject: Reply with quote

Hallo kolusu,

thank you for asking again.

The allocation of the tables should be clear.

Table MySchema.SUPPORT contains all valid numbers from 1 up to MAX_allowed_value. In the sample 5. The required string with the flags has thus always length 5.

Table MySchema.ORIGINAL contains any Keyfeld (here F2). To this key field are numbers assigned. In the sample is 1 and 3 assigned to B. Thus the required string with flags should be YNYNN . First Y means number 1 is assigned to B, next N measn number 2 not assigend to B, next Y means number 3 assigned to B and so on. Up to the maxlength, maximum allowed numbers.

This

Code:


 -- FLAGS FOR VALUE 'B'                                                         
    SELECT LISTAGG                                                             
               (CASE WHEN (T2.F1 IS NULL) THEN 'N'                             
                                          ELSE 'Y'                             
                END                                                             
               )                                                               
           WITHIN GROUP (ORDER BY T1.F1 ASC)                                   
           AS FLAGLIST                                                         
                                                                               
    FROM MYSCHEMA.SUPPORT T1                                                   
                                                                               
    LEFT  OUTER JOIN                                                           
          ( SELECT T3.F1 FROM MYSCHEMA.ORIGINAL T3                             
            WHERE T3.F2 = 'B'                                                   
          ) T2                                                                 
          ON T1.F1 = T2.F1                                                     
    ;                     




provides this:

Code:


YNYNN




The query runs with CURRENT FUNCTION LEVEL(V12R1M501).

Does this clarify the topic?

Please see personal message, I will try to send / upload to complete SPUFI output file.

bauer
Back to top
View user's profile Send private message
bauer
Intermediate


Joined: 10 Oct 2003
Posts: 314
Topics: 48
Location: germany

PostPosted: Thu Aug 19, 2021 1:30 am    Post subject: Reply with quote

Hallo *,

this documentation

https://www.ibm.com/docs/en/db2-for-zos/12?topic=functions-listagg

says, that LISTAGG function works with required function level FL 501.

bauer
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12354
Topics: 75
Location: San Jose

PostPosted: Thu Aug 19, 2021 11:04 am    Post subject: Reply with quote

bauer,

Thanks for the clarification, I got an idea about the results. What is the result of this data?

Code:

INSERT INTO MYSCHEMA.ORIGINAL VALUES (1,'A');
INSERT INTO MYSCHEMA.ORIGINAL VALUES (2,'A');
INSERT INTO MYSCHEMA.ORIGINAL VALUES (3,'A');
INSERT INTO MYSCHEMA.ORIGINAL VALUES (4,'A');
INSERT INTO MYSCHEMA.ORIGINAL VALUES (5,'A');
INSERT INTO MYSCHEMA.ORIGINAL VALUES (1,'B');
INSERT INTO MYSCHEMA.ORIGINAL VALUES (3,'B');
INSERT INTO MYSCHEMA.ORIGINAL VALUES (3,'C');


Would it be ?
Code:

YYYYY    << "A" has all value 1 thru 5
YNYNN    << "B" has 1 and 3
NNYNN    << "C" has only 3


If that is the expected result, then you can use the following query
Code:

SELECT REPLACE(REPLACE(SUBSTR(DIGITS(SUM(CASE             
                       WHEN F1 = 1 THEN SMALLINT(10000)   
                       WHEN F1 = 2 THEN SMALLINT(01000)   
                       WHEN F1 = 3 THEN SMALLINT(00100)   
                       WHEN F1 = 4 THEN SMALLINT(00010)   
                       WHEN F1 = 5 THEN SMALLINT(00001)   
                       ELSE SMALLINT(00000)               
                       END)),6,5),'1','Y'),'0','N')       
  FROM MYSCHEMA.ORIGINAL                                           
  GROUP BY F2                                             

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
bauer
Intermediate


Joined: 10 Oct 2003
Posts: 314
Topics: 48
Location: germany

PostPosted: Thu Aug 19, 2021 11:39 am    Post subject: Reply with quote

kolusu,

yes, you are right about the expected result. Your idea with the SUM operator is great, looks pretty good.

But: In my sample I used 5 digits, 5 possible values. The real SQL should handle 255 digits (maximum allowable char length in DB2 for non varying fields).

Thus your CASE does not really look very nice.

The performance might be much better than my solution with with additional table.

But values from 1 to 255, even if I use big integer instead of small, I do not have enough digits. Sad


Nice task for the evening to think about the general approach.

Thank you again for your time and providing a new idea.

Bauer
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12354
Topics: 75
Location: San Jose

PostPosted: Thu Aug 19, 2021 11:59 am    Post subject: Reply with quote

bauer wrote:
kolusu,

yes, you are right about the expected result. Your idea with the SUM operator is great, looks pretty good.

But: In my sample I used 5 digits, 5 possible values. The real SQL should handle 255 digits (maximum allowable char length in DB2 for non varying fields).

Thus your CASE does not really look very nice.

The performance might be much better than my solution with with additional table.

But values from 1 to 255, even if I use big integer instead of small, I do not have enough digits. Sad


Nice task for the evening to think about the general approach.

Thank you again for your time and providing a new idea.

Bauer


Bauer,

Since you mentioned only 5 possible values I combined them into a single string of 5 byte numeric. But I think we can scale up the solution to handle it.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12354
Topics: 75
Location: San Jose

PostPosted: Thu Aug 19, 2021 2:36 pm    Post subject: Reply with quote

bauer wrote:

But values from 1 to 255, even if I use big integer instead of small, I do not have enough digits. Sad


bauer,

here is a sql to generate a string of 255 bytes. Basically it does some basic math on the value of F1 in relation to 255 and then it just pads to the left and right the values of 'N'. I tried this on variations of 1 thru 255

Code:

SELECT CHAR(IFNULL                                                     
       (REPLACE(SUBSTR(A.MY_STR,1,A.LPAD_VAL)      ||                 
                CHAR('Y')                          ||                 
                SUBSTR(A.MY_STR,A.RPOS,A.RPAD_VAL),' ','N'),' '),255) 
  FROM (SELECT RPAD(' ',256,'N')           AS MY_STR                   
              ,SMALLINT(254 - (255 - F1))  AS LPAD_VAL                 
              ,SMALLINT(255 - F1)          AS RPAD_VAL                 
              ,SMALLINT(F1 +1 )            AS RPOS                     
          FROM MYSCHEMA.ORIGINAL) A                 


Note : This is not summarized version of the records, it is on individual record basis. The duplicates are not eliminated.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
bauer
Intermediate


Joined: 10 Oct 2003
Posts: 314
Topics: 48
Location: germany

PostPosted: Thu Aug 19, 2021 3:58 pm    Post subject: Reply with quote

Hi kolusu,

my impression is: This does not work.

See the discussed sample above: YNYNN << "B" has 1 and 3

How is this generated?

The provided SQL pads from the left some 'N', one 'Y', and from the right some 'N'.


Something like WHERE MySchema.ORIGINAL.F2 = <AnyValue> seems missing.

Or did I miss something?

bauer
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12354
Topics: 75
Location: San Jose

PostPosted: Thu Aug 19, 2021 4:19 pm    Post subject: Reply with quote

bauer wrote:
Hi kolusu,

my impression is: This does not work.

See the discussed sample above: YNYNN << "B" has 1 and 3

How is this generated?

The provided SQL pads from the left some 'N', one 'Y', and from the right some 'N'.


Something like WHERE MySchema.ORIGINAL.F2 = <AnyValue> seems missing.

Or did I miss something?

bauer


not really, you just missed my note at the bottom
kolusu wrote:

Note : This is not summarized version of the records, it is on individual record basis. The duplicates are not eliminated.

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12354
Topics: 75
Location: San Jose

PostPosted: Thu Aug 19, 2021 8:42 pm    Post subject: Reply with quote

bauer,

Here is the updated summarized version. As I mentioned earlier, we can get the result with basic math.

Since we are dealing with 1 - 255 I just divided the value by 16 and also got the remainder. Dividing by 16 gives you 16 different slots(0 thru 15) where the flags need to go.

For sample data
Code:

---------+--
F2      F1 
---------+--
A        1 
A        2 
A        3 
A        4 
A        5 
A      255 
B        1 
B        5 
C        4 
D        6 
D      256        << will be ignored.
E       16 


After the division and remainder we have the following values

Code:

F2      F1         SLOT     FLAG_POS
---------+---------+---------+-------
A        1            0            1
A        2            0            2
A        3            0            3
A        4            0            4
A        5            0            5
A      255           15           15
B        1            0            1
B        5            0            5
C        4            0            4
D        6            0            6
D      256           16            0 <<< ignored
E       16            1            0

Now the remainder will be used to set the actual flag. First we set flags based on the remainder and sum them up. Once the flags are summed, we will validate the slot # as where the flags should go in and then summarize the slots. Now we have everything we need, and it is just a matter of replacing '1' to 'Y' and '0' to 'N'. The output of REPLACE is a varchar and I just trimmed it to CHAR format and also handled if there is a chance of getting NULL value. (this is optional, you can remove it if you don't need it)
Code:

SELECT CHAR(SUBSTR(IFNULL(                                                     
       REPLACE(REPLACE(SUBSTR(DIGITS(B.S00),4,16) ||                           
                       SUBSTR(DIGITS(B.S01),4,16) ||                           
                       SUBSTR(DIGITS(B.S02),4,16) ||                           
                       SUBSTR(DIGITS(B.S03),4,16) ||                           
                       SUBSTR(DIGITS(B.S04),4,16) ||                           
                       SUBSTR(DIGITS(B.S05),4,16) ||                           
                       SUBSTR(DIGITS(B.S06),4,16) ||                           
                       SUBSTR(DIGITS(B.S07),4,16) ||                           
                       SUBSTR(DIGITS(B.S08),4,16) ||                           
                       SUBSTR(DIGITS(B.S09),4,16) ||                           
                       SUBSTR(DIGITS(B.S10),4,16) ||                           
                       SUBSTR(DIGITS(B.S11),4,16) ||                           
                       SUBSTR(DIGITS(B.S12),4,16) ||                           
                       SUBSTR(DIGITS(B.S13),4,16) ||                           
                       SUBSTR(DIGITS(B.S14),4,16) ||                           
                       SUBSTR(DIGITS(B.S15),4,16)                               
                ,'1','Y')                                                       
        ,'0','N'),' '),2,255))                                                 
  FROM( SELECT F2                                                               
       ,SUM(CASE WHEN SLOT = 00 THEN C01 ELSE BIGINT(0) END) AS S00             
       ,SUM(CASE WHEN SLOT = 01 THEN C01 ELSE BIGINT(0) END) AS S01             
       ,SUM(CASE WHEN SLOT = 02 THEN C01 ELSE BIGINT(0) END) AS S02             
       ,SUM(CASE WHEN SLOT = 03 THEN C01 ELSE BIGINT(0) END) AS S03             
       ,SUM(CASE WHEN SLOT = 04 THEN C01 ELSE BIGINT(0) END) AS S04             
       ,SUM(CASE WHEN SLOT = 05 THEN C01 ELSE BIGINT(0) END) AS S05             
       ,SUM(CASE WHEN SLOT = 06 THEN C01 ELSE BIGINT(0) END) AS S06             
       ,SUM(CASE WHEN SLOT = 07 THEN C01 ELSE BIGINT(0) END) AS S07             
       ,SUM(CASE WHEN SLOT = 08 THEN C01 ELSE BIGINT(0) END) AS S08             
       ,SUM(CASE WHEN SLOT = 09 THEN C01 ELSE BIGINT(0) END) AS S09             
       ,SUM(CASE WHEN SLOT = 10 THEN C01 ELSE BIGINT(0) END) AS S10             
       ,SUM(CASE WHEN SLOT = 11 THEN C01 ELSE BIGINT(0) END) AS S11             
       ,SUM(CASE WHEN SLOT = 12 THEN C01 ELSE BIGINT(0) END) AS S12             
       ,SUM(CASE WHEN SLOT = 13 THEN C01 ELSE BIGINT(0) END) AS S13             
       ,SUM(CASE WHEN SLOT = 14 THEN C01 ELSE BIGINT(0) END) AS S14             
       ,SUM(CASE WHEN SLOT = 15 THEN C01 ELSE BIGINT(0) END) AS S15             
           FROM (SELECT F2          AS F2                                         
                ,INT(F1/16)         AS SLOT                             
           ,CASE WHEN MOD(F1,16) = 00 THEN BIGINT(1000000000000000)             
                 WHEN MOD(F1,16) = 01 THEN BIGINT(0100000000000000)             
                 WHEN MOD(F1,16) = 02 THEN BIGINT(0010000000000000)             
                 WHEN MOD(F1,16) = 03 THEN BIGINT(0001000000000000)             
                 WHEN MOD(F1,16) = 04 THEN BIGINT(0000100000000000)             
                 WHEN MOD(F1,16) = 05 THEN BIGINT(0000010000000000)             
                 WHEN MOD(F1,16) = 06 THEN BIGINT(0000001000000000)             
                 WHEN MOD(F1,16) = 07 THEN BIGINT(0000000100000000)             
                 WHEN MOD(F1,16) = 08 THEN BIGINT(0000000010000000)             
                 WHEN MOD(F1,16) = 09 THEN BIGINT(0000000001000000)             
                 WHEN MOD(F1,16) = 10 THEN BIGINT(0000000000100000)             
                 WHEN MOD(F1,16) = 11 THEN BIGINT(0000000000010000)             
                 WHEN MOD(F1,16) = 12 THEN BIGINT(0000000000001000)             
                 WHEN MOD(F1,16) = 13 THEN BIGINT(0000000000000100)             
                 WHEN MOD(F1,16) = 14 THEN BIGINT(0000000000000010)             
                 WHEN MOD(F1,16) = 15 THEN BIGINT(0000000000000001)             
                 ELSE BIGINT(0) END AS C01                                     
           FROM MYSCHEMA.ORIGINAL) A         
       GROUP BY A.F2) B
 ;


_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
bauer
Intermediate


Joined: 10 Oct 2003
Posts: 314
Topics: 48
Location: germany

PostPosted: Mon Aug 23, 2021 11:35 am    Post subject: Reply with quote

kolusu,

sorry for my late feedback.

First of all thank you for your time.

Yes, this obviously works. My solutuion with the additional table and the left outer join might be easier to understand - but, without measurement at the moment, your solution might save CPU time, looks faster.

Great.

Thank you again for your time and "stressed convolutions of your brain".

Bauer
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group