| 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 |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
