View previous topic :: View next topic
|
Author |
Message |
balaji81_k
Active User
Joined: 29 Jun 2005 Posts: 155
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
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 |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005 Posts: 155
|
|
|
|
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 |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005 Posts: 155
|
|
|
|
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 |
|
|
RahulG31
Active User
Joined: 20 Dec 2014 Posts: 446 Location: USA
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
Quote: |
And, I am Not able to comprehend enrico's reply. |
I was trying to explain the why of the unprintables |
|
Back to top |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005 Posts: 155
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3049 Location: NYC,USA
|
|
|
|
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 |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005 Posts: 155
|
|
|
|
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 |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005 Posts: 155
|
|
|
|
Enrico,
Posting correct output data with HEX ON.
Code: |
2016.10.19|2016/10/19|....|
FFFF4FF4FF4FFFF6FF6FF400004
2016B10B19F2016110119F000AF
|
|
|
Back to top |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005 Posts: 155
|
|
|
|
Enrico,
Used SUBSTR(DIGITS(10),9,2)) and i am able to print it, |
|
Back to top |
|
|
|