|
View previous topic :: View next topic
|
| Author |
Message |
peeterparker
New User
Joined: 17 Nov 2006
Posts: 6
|
|
|
|
Hi,
I have a table which has to be unloaded, only those rows which are not older than 180 days from current date.
Ex:
select * from abcd.xyx_pqr where sct_evt_dt + 180 days
> current_date
the difficulty here is that i am not supposed to use IKJEFT1A utility but have to use DSNUTILB pgm to download the data.
So wanted to know if there is any possibility of
unloading selected rows which match the query above.
Though i have tried the WHEN clause , it doesn't allow any arithmetic operators.
Regards,
Sana.
P.S: No SQL select statement has to be used. |
|
| Back to top |
|
 |
PeD
Active User
Joined: 26 Nov 2005
Posts: 459
Location: Belgium
|
|
|
|
maybe something like
| Code: |
UNLOAD TABLESPACE abcd.xyx_pqr
FROM TABLE abcd.xyx_pqr
HEADER NONE
WHEN ( sct_evt_dt + 180 days )
SHRLEVEL CHANGE |
Not tested for arithmetic operation. Have a try. |
|
| Back to top |
|
|
wanderer
Active User
Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka
|
|
|
|
| According to the manual, it is possible. If it doesn't work, you may post your complete UNLOAD statement and somebody will suggest something here. |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
See unload is nothing but bulk select.Normally when you run a spufi you have some limits to that you cant fetch all the ROWS for your query.That is why we opt for running a spufi in batch.Just like your normal unload give the datasets sysrec and syspunch.In SYSIN you can give your statement
like
| Code: |
select empid,emdno,salary from employee where salary>6000;
|
This will work as the same unload.
Hope this helps.
Thanks,
Prem |
|
| Back to top |
|
|
peeterparker
New User
Joined: 17 Nov 2006
Posts: 6
|
|
|
|
UNLOAD DATA PUNCHDDN SYSPUNCH UNLDNN SYSRECCOO
FROM TABLE ABC.PR_XYZ
WHEN ( STRT_DT + 2 DAYS > CURRENT_DATE )
The error is
INVALID KEYWORD - + in the WHEN line. |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
can you please copy paste your code becasue I find your code to contain
some errors.
Please use the coding conventions used in this forum and then paste your code.
Thanks,
Prem |
|
| Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006
Posts: 1042
Location: Richmond, Virginia
|
|
|
|
The UNLOAD WHEN clause allows "between" as well as >, >=, <, <=.
Can you use several compares with AND or OR?
The compare can be with a labeled-duration-expression, which includes the various date and timestamp components. See UNLOAD syntax documentation for the details.
Does this help? |
|
| Back to top |
|
|
wanderer
Active User
Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka
|
|
|
|
This should work,
| Code: |
UNLOAD DATA PUNCHDDN SYSPUNCH UNLDNN SYSRECCOO
FROM TABLE ABC.PR_XYZ
WHEN ( STRT_DT > (CURRENT_DATE - 2 DAYS) ) |
|
|
| Back to top |
|
|
peeterparker
New User
Joined: 17 Nov 2006
Posts: 6
|
|
|
|
Code:
UNLOAD DATA PUNCHDDN SYSPUNCH UNLDNN SYSRECCOO
FROM TABLE ABC.PR_XYZ
WHEN ( STRT_DT > (CURRENT_DATE - 2 DAYS) )
thnax a zillion!!! |
|
| Back to top |
|
|
wanderer
Active User
Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka
|
|
|
|
You are welcome!  |
|
| Back to top |
|
|
|
|
