View previous topic :: View next topic |
Author |
Message |
sam01 Beginner
Joined: 03 Jan 2019 Posts: 9 Topics: 4
|
Posted: Mon Jan 21, 2019 3:16 am Post subject: SQL and STRIP command |
|
|
Hello,
i use this SQL quierie :
Code: |
SELECT STRIP(CHAINE)!!';'!!STRIP(DATEXE)!!';'
!!STRIP(HEUREXE)!!';'!!STRIP(DATESAV)!!';'
!!STRIP(HEURSAV)!!';'!!STRIP(CODRET)
FROM XDATE.REAL WHERE CHAINE LIKE 'XFDESQL%'
AND DATEXE BETWEEN '2018-01-01' AND '2018-12-31'
ORDER BY DATEXE ASC; |
STRIP command commands works for all columns except the first...
We can see that in the sysrec dataset by browsing it :
Code: |
BROWSE UT40.DB2.SELECT01
Command ===>
********************************* Top of Data ******
..ERTPRCQS;2018-01-02;155300;2018-01-02;173400;0000
..ERTPRCQ6;2018-01-02;125142;2018-01-02;171000;0000
..ERTPRCQ6;2018-01-03;070814;2018-01-03;160000;0000
..ERTPRCQS;2018-01-03;115719;2018-01-03;164000;0000
..ERTPRCQS;2018-01-04;120630;2018-01-04;163200;0000
..ERTPRCQ6;2018-01-04;070811;2018-01-04;160000;0000
..ERTPRCQ6;2018-01-05;070921;2018-01-05;160000;0000
..ERTPRCQS;2018-01-05;115802;2018-01-05;163000;0000
..ERTPRCQS;2018-01-08;110725;2018-01-08;165500;0000
|
There are two dots in the two very first columns and it seems the first strip command dosen't works..
For the following columns, it's ok. |
|
Back to top |
|
|
Nic Clouston Advanced
Joined: 01 Feb 2007 Posts: 1075 Topics: 7 Location: At Home
|
Posted: Mon Jan 21, 2019 3:53 am Post subject: |
|
|
I would suggest that the data set is VB and that the .. is the RDW which is not part of your data but control information. _________________ Utility and Program control cards are NOT, repeat NOT, JCL. |
|
Back to top |
|
|
sam01 Beginner
Joined: 03 Jan 2019 Posts: 9 Topics: 4
|
Posted: Mon Jan 21, 2019 8:03 am Post subject: |
|
|
Hello, no my dataset is in FB Format.
But i realised the strip command is unnusefull beucause when i remove it, the result dataset is the same !!!
Code: |
SELECT CHAINE!!';'!!DATEXE!!';'
!!HEUREXE!!';'!!DATESAV!!';'
!!HEURSAV!!';'!!CODRET
FROM XDATE.REAL WHERE CHAINE LIKE 'XFDESQL%'
AND DATEXE BETWEEN '2018-01-01' AND '2018-12-31'
ORDER BY DATEXE ASC;
|
|
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12367 Topics: 75 Location: San Jose
|
Posted: Mon Jan 21, 2019 11:22 am Post subject: |
|
|
sam01 wrote: | Hello, no my dataset is in FB Format.
But i realised the strip command is unnusefull beucause when i remove it, the result dataset is the same |
Sam01,
That is probably because your CHAINE column is defined as VARCHAR column. If you looked at the SYSPUNCH dataset in your job log you will find the attributes of the column.
PS : your sample data does not match the SQL given as you are looking for CHAINE LIKE 'XFDESQL%' but your sample data is shown with ERTPRC% So either your query is wrong or the results you show are wrong. And please for the sake readability and maintaining code the SQL in a formatted way.
something like this
Code: |
SELECT CHAINE ||
';' ||
DATEXE ||
';' ||
HEUREXE ||
';' ||
DATESAV ||
';' ||
HEURSAV ||
';' ||
CODRET
FROM XDATE.REAL
WHERE CHAINE LIKE 'XFDESQL%'
AND DATEXE BETWEEN '2018-01-01' AND '2018-12-31'
ORDER BY DATEXE ASC
; |
_________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
www.linkedin.com/in/kolusu |
|
Back to top |
|
|
sam01 Beginner
Joined: 03 Jan 2019 Posts: 9 Topics: 4
|
Posted: Tue Jan 22, 2019 3:32 am Post subject: |
|
|
Hello Kolusu, don't worry about the content of the dataset. I have changed it on purpose.
The quierie is good.
Here is the syspunch :
Code: |
LOAD DATA LOG NO INDDN SYSREC00 INTO TABLE
TBLNAME
(
"." POSITION( 1 )
VARCHAR
)
|
|
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12367 Topics: 75 Location: San Jose
|
Posted: Tue Jan 22, 2019 11:00 am Post subject: |
|
|
sam01,
As expected your CHAINE column is defined as VARCHAR and a varchar column has 2 bytes of length coded in the beginning. If you don't want that 2 bytes then you need to use CHAR function. something like this (untested)
Code: |
SELECT CHAR(STRIP(CHAINE) ||
';' ||
STRIP(DATEXE) ||
';' ||
STRIP(HEUREXE) ||
';' ||
STRIP(DATESAV) ||
';' ||
STRIP(HEURSAV) ||
';' ||
STRIP(CODRET))
FROM XDATE.REAL
WHERE CHAINE LIKE 'XFDESQL%'
AND DATEXE BETWEEN '2018-01-01' AND '2018-12-31'
ORDER BY DATEXE ASC
; |
_________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
www.linkedin.com/in/kolusu |
|
Back to top |
|
|
sam01 Beginner
Joined: 03 Jan 2019 Posts: 9 Topics: 4
|
Posted: Wed Jan 23, 2019 3:29 am Post subject: |
|
|
Thank's Kolusu, it works! |
|
Back to top |
|
|
|
|