IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Low values Results from VARCHAR FORMAT


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 155

PostPosted: Thu Oct 20, 2016 1:18 am
Reply with quote

Hi Everyone,

I apologize for my last post as i didnt furnish complete Query in it , but this time i have tested SIMPLE query on VARCHAR FORMAT and i found low values
resulted from the below one
Code:

SELECT
CAST(VARCHAR_FORMAT(CURRENT_DATE,'YYYY.MM.DD') AS CHAR(10)),
'|',
CAST(VARCHAR_FORMAT(CURRENT_DATE,'YYYY/MM/DD') AS CHAR(10)),
'|'
FROM SYSIBM.SYSDUMMY1


Output file with HEX ON:-
Code:

2016.10.19..|2016/10/19..|
FFFF4FF4FF004FFFF6FF6FF004
2016B10B1901F201611011901F
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Thu Oct 20, 2016 1:32 am
Reply with quote

the result posted shows ( repeated twice )

10 bytes for the first date
a varchar representation of the "|" ==> two bytes for the length one byte for the data

Code:

0        1         2 
12345678901234567890123456

FFFF4FF4FF004FFFF6FF6FF004
2016B10B1901F201611011901F
^         ^ ^       
|         | |
|         | |<- one byte of data
|         |<- two bytes for the length 0001 ==> one byte
|<- 10 bytes for the date

Back to top
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 155

PostPosted: Thu Oct 20, 2016 1:35 am
Reply with quote

Hi Enrico,

Do we need to use STRIP or TRIM or any other function to avoid this because we need this seperator "|" between the dates .
Please advise.
Back to top
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 155

PostPosted: Thu Oct 20, 2016 1:45 am
Reply with quote

Hi Enrico,
I coded this way
Code:

SELECT
CAST(VARCHAR_FORMAT(CURRENT_DATE,'YYYY.MM.DD') AS CHAR(10)),
SUBSTR('|',1,1),
CAST(VARCHAR_FORMAT(CURRENT_DATE,'YYYY/MM/DD') AS CHAR(10)),
SUBSTR('|',1,1)
FROM SYSIBM.SYSDUMMY1


It is working as expected.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Thu Oct 20, 2016 2:25 am
Reply with quote

balaji81_k,

I ran both of your queries and both of them gave me same result and None of them had any low-values in them.

I was running them through SPUFI. How did you run them?

And, I am Not able to comprehend enrico's reply. I am not sure if that made you do something to resolve your issue.

.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Thu Oct 20, 2016 2:39 am
Reply with quote

Quote:
And, I am Not able to comprehend enrico's reply.


I was trying to explain the why of the unprintables
Back to top
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 155

PostPosted: Thu Oct 20, 2016 2:39 am
Reply with quote

Hi Rahul,

I am running SPUFI from Mainframe as well as using QMF from Windows session , but i am able to see low values in both not sure what is going on .
Also running SQL thru JCL . Even selecting numeric value (i,e) like SELECT 10 FROM SYSIBM.SYSDUMMY1 shows '...' when giving HEX ON i am able to see its equivalent.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3049
Location: NYC,USA

PostPosted: Thu Oct 20, 2016 3:01 am
Reply with quote

Code:
SELECT
VARCHAR_FORMAT(CURRENT_DATE,'YYYY.MM.DD')
 ||'|'||
VARCHAR_FORMAT(CURRENT_DATE,'YYYY/MM/DD')
 || '|'
 FROM SYSIBM.SYSDUMMY1


Output:
Code:
 1
 ----------------------
 2016.10.19|2016/10/19|

This should work for you, there is no reason to use cast unless required also why you gave ',' after each date? I thought you wanted as one string. What you posted may not be true as we don't see that is happening and just because you say so we cannot waste time on that. please try with this and let us know.
Back to top
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 155

PostPosted: Thu Oct 20, 2016 3:01 am
Reply with quote

Hi Enrico,

I am trying to print 10 along with the SQL query but i am able to see its equivalent only by giving HEX on , can you please advise how to tune this query to print this 10 using DIGITS function?
Code:

SELECT
CAST(VARCHAR_FORMAT(CURRENT_DATE,'YYYY.MM.DD') AS CHAR(10)),
SUBSTR('|',1,1),
CAST(VARCHAR_FORMAT(CURRENT_DATE,'YYYY/MM/DD') AS CHAR(10)),
SUBSTR('|',1,1),
10,
SUBSTR('|',1,1)
FROM SYSIBM.SYSDUMMY1


OUTPUT WITH HEXON:-

Code:

2016.10.19..|2016/10/19..|....|
FFFF4FF4FF004FFFF6FF6FF00400004
2016B10B1901F201611011901F000AF

Back to top
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 155

PostPosted: Thu Oct 20, 2016 3:06 am
Reply with quote

Enrico,

Posting correct output data with HEX ON.
Code:

2016.10.19|2016/10/19|....|
FFFF4FF4FF4FFFF6FF6FF400004
2016B10B19F2016110119F000AF
Back to top
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 155

PostPosted: Thu Oct 20, 2016 3:13 am
Reply with quote

Enrico,

Used SUBSTR(DIGITS(10),9,2)) and i am able to print it,
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Populate last day of the Month in MMD... SYNCSORT 2
No new posts INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
No new posts Modifying Date Format Using DFSORT DFSORT/ICETOOL 9
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts Need to convert date format DFSORT/ICETOOL 20
Search our Forums:

Back to Top