| Author |
Message
|
| Siddu |
 Posted: Tue Dec 04, 2012 3:28 am Post subject: Fetching a rows from DB2 which are changed in last 7 days |
 |
|
Apprentice
Joined: 22 Aug 2012
Posts: 44
|
Hi All,
I'm trying to fetch the records from DB2 which have been changed in last 7 days.
My Query looks like like this:
SET Environment.XMLNSC.Result.AppAudit1[] = (SELECT T.* FROM Database.{DSNNAME2}.{DBSCHEMA2}.{DBTABLE2} AS T WHERE ROW CHANGE TIMESTAMP FOR T <= CURRENT TIMESTAMP AND ROW CHANGE TIMESTAMP FOR T >= CURRENT TIMESTAMP - 7 DAYS);
But it is saying syntax error.
Is it allowed to use DB2 keywords directly in esql or we need to modify the statements?
Please someone guide me on this...
_________________
"Be honest. It is one of the few things that you can control in your life." |
|
| Back to top |
|
 |
| kash3338 |
| Posted: Tue Dec 04, 2012 3:59 am Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Dec 04, 2012 4:27 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| kash3338 wrote: |
| Try running this query using PASSTHRU |
Make sure to alter the syntax of it to remove the ESQL specific parts and ensure it is compliant with the version of SQL that is in force in your database. |
|
| Back to top |
|
|
| Siddu |
| Posted: Tue Dec 04, 2012 4:31 am Post subject: |
|
|
Apprentice
Joined: 22 Aug 2012
Posts: 44
|
| kash3338 wrote: |
| Try running this query using PASSTHRU |
Hi Kash,
Thanks for the response.
Itried this:
SET MyQry = 'SELECT T.* FROM TABLE dbo.tblPESDelta AS T WHERE T.DTTM_Stamp >= T.DTTM_Stamp - INTERVAL 5 DAY';
SET Environment.XMLNSC.Result.AppAudit1[] = PASSTHRU (MyQry) TO Database.DSNNAME2;
I also tried below one:
SET Environment.XMLNSC.Result.AppAudit1[] = PASSTHRU 'SELECT T.* FROM TABLE dbo.tblPESDelta AS T WHERE T.DTTM_Stamp >= T.DTTM_Stamp - INTERVAL 5 DAY' TO Database.DSNNAME2;
But still getting the error saying:
Syntax error. Valid options include : ||/=>= etc.
Where syntax is wrong... I went through Info centre but still couldn't find the solution... 
_________________
"Be honest. It is one of the few things that you can control in your life." |
|
| Back to top |
|
|
| JosephGramig |
| Posted: Tue Dec 04, 2012 7:13 am Post subject: |
|
|
Grand Master
Joined: 09 Feb 2006
Posts: 1244
Location: Gold Coast of Florida, USA
|
| Did you get the query to work in the DB2 product itself first? Like at the DB2 command prompt... |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Tue Dec 04, 2012 7:34 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
I'd probably sort out the interval first and supply that to the query.
AFAIK, INTERVAL does not easily translate into DB2 SQL.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| kash3338 |
| Posted: Tue Dec 04, 2012 7:58 am Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
| smdavies99 wrote: |
| AFAIK, INTERVAL does not easily translate into DB2 SQL. |
Infact DB2 does not support INTERVAL datatype. Below quote from one of IBM's infocenter says that,
| Quote: |
| INTERVAL — DB2 does not support an equivalent type, but does support date and time labelled durations for manipulating DATE, TIME, and TIMESTAMP values and generating date and time intervals. |
|
|
| Back to top |
|
|
| JosephGramig |
| Posted: Tue Dec 04, 2012 8:13 am Post subject: |
|
|
Grand Master
Joined: 09 Feb 2006
Posts: 1244
Location: Gold Coast of Florida, USA
|
| Since you are using PASSTHRU, you need to use the syntax of the database service you are PASSingTHRU to. It is sent to the database service as is for interpretation same as if you were at the DB2 prompt (for DB2 for instance). |
|
| Back to top |
|
|
|
|
