|
View previous topic :: View next topic
|
| Author |
Message |
murali922
New User
Joined: 25 Jul 2005
Posts: 92
Location: India
|
|
|
|
| Code: |
SELECT 'H' AS RECORD_TYPE
, CHAR(CURRENT DATE, ISO) || ' ' AS CARD_ACCT_PLSTC_NO
FROM SYSIBM.SYSDUMMY1 |
When I use the same query in SPUFI, it comes up fine. But when I put the record into a FILE, it comes up wrong.
In SPUFI the output looks like this :
---------+---------+---------+---------+---------+-
RECORD_TYPE CARD_ACCT_PLSTC_NO
---------+---------+---------+---------+---------+-
H 2007-08-02
When I unload that into a file, it results in something like this :-
..H..2007-08-02
I expect the output to be like this :
H2007-08-02
Thanks in advance for the help.  |
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Code: |
SELECT 'H' ||
CHAR(CURRENT DATE, ISO) || ' ' AS RECORD_TYPE_CARD_ACCT_PLSTC_NO
FROM SYSIBM.SYSDUMMY1; |
|
|
| Back to top |
|
|
socker_dad
Active User
Joined: 05 Dec 2006
Posts: 177
Location: Seattle, WA
|
|
|
|
Interesting.
I tried dbz's code and I still get hex values (X'000C') in columns 1 & 2, although the remainder looks good.
I wonder why that is.  |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
tis interesting that (X'000C') = 12 base 10, which is the length of the data.
probably unloading to variable length file, or you are not turning off the VLI generator during the output. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
actually 0c is 13, which is the length of the generated output.
just kidding - it really is 12. . . |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| When you use a literal like 'H' it is treated as a varchar field, if you cast it as a char(1) field then you won't get the 2 byte length field in front of it. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
thanks Craq,
we know why the vli, which is = 12. (despite my previous post!)
1 for H
10 for the date
1 for space after the date. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Code: |
SELECT CAST('H' ||CHAR(CURRENT DATE, ISO) || ' ' AS CHAR(12))
FROM SYSIBM.SYSDUMMY1;
|
|
|
| Back to top |
|
|
socker_dad
Active User
Joined: 05 Dec 2006
Posts: 177
Location: Seattle, WA
|
|
|
|
| Sweet! |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| thx to whomever edited my post, and i am not kidding. sehr peinlich. |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin
|
|
|
|
| My understanding was the unload utility unloads it to match the DCLGEN copybook for the table for use in COBOL. It preservers the comp-3 etc. |
|
| Back to top |
|
|
murali922
New User
Joined: 25 Jul 2005
Posts: 92
Location: India
|
|
|
|
My original SQL looks like this :-
| Code: |
SELECT 'H' AS RECORD_TYPE
, CHAR(CURRENT DATE, ISO) || ' ' AS CARD_ACCT_PLSTC_NO
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT CASE WHEN AMOUNT_NO = '00'
THEN 'B'
ELSE 'S'
END AS RECORD_TYPE
, CHAR(CURRENT DATE, ISO) || ' ' AS CARD_ACCT_PLSTC_NO
FROM SYSIBM.SYSDUMMY1B
UNION ALL
SELECT 'T' AS RECORD_TYPE
, ' ' AS CARD_ACCT_PLSTC_NO
FROM SYSIBM.SYSDUMMY1
; |
I would need to use the same layout in all of the 3 sections, as I use a UNION ALL. In that case, I would not be able to use the solution you all have suggested, as if I use yours I am not having the variable RECORD_TYPE at all.
Is there some other way I can remove the .. in front of all the variables.
Thanks |
|
| Back to top |
|
|
murali922
New User
Joined: 25 Jul 2005
Posts: 92
Location: India
|
|
|
|
SELECT CASE WHEN STU_NAME = '00'
THEN SCHOOL_NAME
ELSE ' '
END AS SCHOOL_NAME FROM TABLE_A;
SCHOOL NAME is defined in the table as CHAR(35).
The first record has
ALPHA MATRICULATION SCHOOL OF EDUCA
INDIAN SCHOOL OF BUSSINESS
The first record comes out fine. But the next record comes up with 2 blank spaces in the starting.
Since the value in the field would always not be CHAR(35), and sometimes might be less than 35 bytes,
I think there needs to be a solution to remove the spaces first.
I have used CAST, and it doesnt work. I have also tried modifying the SQL to be like this :
SELECT CASE WHEN STU_NAME = '00'
THEN SCHOOL_NAME
ELSE ' '
END AS SCHOOL_NAME FROM TABLE_A;
SELECT CASE WHEN STU_NAME = '00'
THEN SUBSTR(CAST(COMPANY_NM AS CHAR(35)),1,35)
ELSE ' '
END AS SCHOOL_NAME FROM TABLE_A;
SELECT CASE WHEN STU_NAME = '00'
THEN SUBSTR(CAST(COMPANY_NM AS CHAR(35)),1,
LENGTH(RTRIM(COMPANY_NM)))
ELSE ' '
END AS SCHOOL_NAME FROM TABLE_A;
Nothing works. Let me know if you have any other way I can solve this problem. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
Why not modify the receiving process to deal with the data the way your process creates it? As long as the data is consistent, it should not be a problem. . .? |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| you could break it up into different steps. It appears that you are creating a header, detail and trailer records. You could create the header in step 1, the details in step 2 and the trailer in step 3 and then merge the three files. Thus negating the need for a union in your detail record extraction |
|
| Back to top |
|
|
|
|
