Joined: 10 Oct 2003 Posts: 315 Topics: 49 Location: Germany
Posted: Tue Aug 17, 2021 11:27 am Post subject: Convert rows with numbers to string with flags
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.
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,
Joined: 26 Nov 2002 Posts: 12375 Topics: 75 Location: San Jose
Posted: Tue Aug 17, 2021 4:05 pm Post subject:
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
www.linkedin.com/in/kolusu
Joined: 26 Nov 2002 Posts: 12375 Topics: 75 Location: San Jose
Posted: Wed Aug 18, 2021 11:03 am Post subject:
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.
Joined: 10 Oct 2003 Posts: 315 Topics: 49 Location: Germany
Posted: Thu Aug 19, 2021 1:18 am Post subject:
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.
Joined: 26 Nov 2002 Posts: 12375 Topics: 75 Location: San Jose
Posted: Thu Aug 19, 2021 11:04 am Post subject:
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
;
Joined: 10 Oct 2003 Posts: 315 Topics: 49 Location: Germany
Posted: Thu Aug 19, 2021 11:39 am Post subject:
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.
Nice task for the evening to think about the general approach.
Thank you again for your time and providing a new idea.
Joined: 26 Nov 2002 Posts: 12375 Topics: 75 Location: San Jose
Posted: Thu Aug 19, 2021 11:59 am Post subject:
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.
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
www.linkedin.com/in/kolusu
Joined: 26 Nov 2002 Posts: 12375 Topics: 75 Location: San Jose
Posted: Thu Aug 19, 2021 2:36 pm Post subject:
bauer wrote:
But values from 1 to 255, even if I use big integer instead of small, I do not have enough digits.
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
www.linkedin.com/in/kolusu
Joined: 26 Nov 2002 Posts: 12375 Topics: 75 Location: San Jose
Posted: Thu Aug 19, 2021 8:42 pm Post subject:
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
;
Joined: 10 Oct 2003 Posts: 315 Topics: 49 Location: Germany
Posted: Mon Aug 23, 2021 11:35 am Post subject:
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".
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
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
.