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 

SQL and STRIP command

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
sam01
Beginner


Joined: 03 Jan 2019
Posts: 9
Topics: 4

PostPosted: Mon Jan 21, 2019 3:16 am    Post subject: SQL and STRIP command Reply with quote

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
View user's profile Send private message
Nic Clouston
Advanced


Joined: 01 Feb 2007
Posts: 1075
Topics: 7
Location: At Home

PostPosted: Mon Jan 21, 2019 3:53 am    Post subject: Reply with quote

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
View user's profile Send private message
sam01
Beginner


Joined: 03 Jan 2019
Posts: 9
Topics: 4

PostPosted: Mon Jan 21, 2019 8:03 am    Post subject: Reply with quote

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
View user's profile Send private message
kolusu
Site Admin
Site Admin


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

PostPosted: Mon Jan 21, 2019 11:22 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website
sam01
Beginner


Joined: 03 Jan 2019
Posts: 9
Topics: 4

PostPosted: Tue Jan 22, 2019 3:32 am    Post subject: Reply with quote

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
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 Jan 22, 2019 11:00 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website
sam01
Beginner


Joined: 03 Jan 2019
Posts: 9
Topics: 4

PostPosted: Wed Jan 23, 2019 3:29 am    Post subject: Reply with quote

Thank's Kolusu, it works! Very Happy
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
Page 1 of 1

 
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