|
View previous topic :: View next topic
|
| Author |
Message |
ramsri
Active User
Joined: 18 Oct 2008
Posts: 380
Location: India
|
|
|
|
Hi,
I am running a SQL query to pull 10 character field into a 80 LRECL dataset. It is populating 10 characters perfectly but filling rest of 70 length with low-values. I am using SORT to get rid of those low-values. Can I achieve it with SQL itself to fill rest of 70 length with spaces instead of low-values?
Please help.
Thanks. |
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
well, until you tell us exactly what you are doing,
we can not offer you any solutions,
and you are stuck with your sort solution - which should not be necessary. |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| What language is your query embedded in? That program is IMHO the proper place to edit the output record, not SQL or *SORT. |
|
| Back to top |
|
|
gylbharat
Active Member
.jpg)
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
Hi Ramsri...
Modify this query according to your needs...
| Code: |
SELECT SUBSTR(CHAR('COLUMN WHICH YOU WANT TO UNLOAD')||CHAR(' ',80),1,80)
FROM SYSIBM.SYSDUMMY1
WITH UR;
|
|
|
| Back to top |
|
|
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| If you absolutely have no use for the remaining 70 bytes then why dont you set the record length as 10 |
|
| Back to top |
|
|
ramsri
Active User
Joined: 18 Oct 2008
Posts: 380
Location: India
|
|
|
|
gylbharat, my field does not reside on SYSIBM.SYSDUMMY1 table but in a different table.
I am asked to use a 80 byte dataset but just (low-values) should not appear after first 10 bytes is the requirement.
Thanks. |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| ramsri wrote: |
gylbharat, my field does not reside on SYSIBM.SYSDUMMY1 table but in a different table.
. |
It would be really difficult to change the table name to the correct one. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
ramsri,
if this a SPUFI select?, is this an unload? is this imbedded SQL?
until you tell us which process you are using to generate the output,
you will get nothing but silly answers, as the above post prove. |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| gylbharat wrote: |
Hi Ramsri...
Modify this query according to your needs...
| Code: |
SELECT SUBSTR(CHAR('COLUMN WHICH YOU WANT TO UNLOAD')||CHAR(' ',80),1,80)
FROM SYSIBM.SYSDUMMY1
WITH UR;
|
|
GYLBHARAT
| Code: |
SELECT CHAR(COLUMN NAME,80) FROM TABLE
|
should be a better I guess the above function doesnt work for version less than 8
Experts please correct me if am wrong |
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
| gylbharat wrote: |
Hi Ramsri...
Modify this query according to your needs...
|
| ramsri wrote: |
| gylbharat, my field does not reside on SYSIBM.SYSDUMMY1 table but in a different table. |
Ramsri, you have one guess as to which part need modification! |
|
| Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
| ramsri wrote: |
gylbharat, my field does not reside on SYSIBM.SYSDUMMY1 table but in a different table.
I am asked to use a 80 byte dataset but just (low-values) should not appear after first 10 bytes is the requirement.
Thanks. |
Hi...
You just need to change the table name and the column name... |
|
| Back to top |
|
|
ramsri
Active User
Joined: 18 Oct 2008
Posts: 380
Location: India
|
|
|
|
Hi gylbharat,
I have used the solution you've suggested and got the expected results. Can you please explain how it works.....
Thanks. |
|
| Back to top |
|
|
|
|
