Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Low values Results from VARCHAR FORMAT

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
balaji81_k

New User


Joined: 29 Jun 2005
Posts: 86

PostPosted: Thu Oct 20, 2016 1:18 am    Post subject: Low values Results from VARCHAR FORMAT
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Thu Oct 20, 2016 1:32 am    Post subject: Reply to: Low values Results from VARCHAR FORMAT
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

New User


Joined: 29 Jun 2005
Posts: 86

PostPosted: Thu Oct 20, 2016 1:35 am    Post subject:
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

New User


Joined: 29 Jun 2005
Posts: 86

PostPosted: Thu Oct 20, 2016 1:45 am    Post subject:
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: 331
Location: USA

PostPosted: Thu Oct 20, 2016 2:25 am    Post subject: Reply to: Low values Results from VARCHAR FORMAT
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Thu Oct 20, 2016 2:39 am    Post subject: Reply to: Low values Results from VARCHAR FORMAT
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

New User


Joined: 29 Jun 2005
Posts: 86

PostPosted: Thu Oct 20, 2016 2:39 am    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Thu Oct 20, 2016 3:01 am    Post subject:
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

New User


Joined: 29 Jun 2005
Posts: 86

PostPosted: Thu Oct 20, 2016 3:01 am    Post subject:
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

New User


Joined: 29 Jun 2005
Posts: 86

PostPosted: Thu Oct 20, 2016 3:06 am    Post subject:
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

New User


Joined: 29 Jun 2005
Posts: 86

PostPosted: Thu Oct 20, 2016 3:13 am    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Overlaying one set of charater values... Kevin Lindsley SYNCSORT 7 Sat Nov 05, 2016 3:21 am
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
No new posts output data in CSV Format janmejay COBOL Programming 8 Sat Oct 15, 2016 2:20 pm
This topic is locked: you cannot edit posts or make replies. output data in CSV Format janmejay DFSORT/ICETOOL 3 Sat Oct 15, 2016 2:16 pm
No new posts How can we create a flat file in JAVA... rakesh.v18 Java & MQSeries 7 Fri Sep 23, 2016 10:46 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us