MVSFORUMS.com :: View topic - Convert rows with numbers to string with flags

Convert rows with numbers to string with flags

through

FAQ

[^/[Print]\]

MVSFORUMS.com -> Database

#1: Convert rows with numbers to string with flags Author: bauer, Location: Germany

Posted: Tue Aug 17, 2021 11:27 am
—
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

#2: Author: bauer, Location: Germany

Posted: Tue Aug 17, 2021 1:13 pm
—
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

#3: Author: kolusu, Location: San Jose

Posted: Tue Aug 17, 2021 4:05 pm
—
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

#4: Author: bauer, Location: Germany

Posted: Wed Aug 18, 2021 4:50 am
—
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

#5: Author: kolusu, Location: San Jose

Posted: Wed Aug 18, 2021 11:03 am
—
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 ?

#6: Author: bauer, Location: Germany

Posted: Thu Aug 19, 2021 1:18 am
—
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

#7: Author: bauer, Location: Germany

Posted: Thu Aug 19, 2021 1:30 am
—
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

#8: Author: kolusu, Location: San Jose

Posted: Thu Aug 19, 2021 11:04 am
—
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
;

#9: Author: bauer, Location: Germany

Posted: Thu Aug 19, 2021 11:39 am
—
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

#10: Author: kolusu, Location: San Jose

Posted: Thu Aug 19, 2021 11:59 am
—

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.

#11: Author: kolusu, Location: San Jose

Posted: Thu Aug 19, 2021 2:36 pm
—

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.

#12: Author: bauer, Location: Germany

Posted: Thu Aug 19, 2021 3:58 pm
—
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

#13: Author: kolusu, Location: San Jose

Posted: Thu Aug 19, 2021 4:19 pm
—

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.

#14: Author: kolusu, Location: San Jose

Posted: Thu Aug 19, 2021 8:42 pm
—
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
;

#15: Author: bauer, Location: Germany

Posted: Mon Aug 23, 2021 11:35 am
—
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

#16: Author: kolusu, Location: San Jose

Posted: Tue Aug 24, 2021 8:29 am
—

bauer wrote:

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

bauer,

Thanks for the feedback. I liked the challenge and was able to come up with the solution.

MVSFORUMS.com -> Database

output generated using printer-friendly topic mod. All times are GMT - 5 Hours

Page 1 of 1