hi team,
what output we will get when we execute this query...pls illustrate
SELECT
AUM.iResourceType, AUM.nvcMessageTitle, AUM.iMessageSeverityType, AUM.nvcMessageText,
AUM.dtMessageTime , AUT.RefFlag, AUM.iMessageId,
'CFF:' + '#ARC_VersionNumber#' as Version,
'#ARC_DeviceVendor#' as DeviceVendor,
'#ARC_DeviceProduct#' as DeviceProduct,
'#ARC_DeviceVersion#' as DeviceVersion
FROM
vwNiceDBKitAuditMessage AUM
left outer join
( select
iMessageType,
'RefFl' as RefFlag
from
vwNiceDBKitAuditMessageType ) AUT
on AUM.iMessageType=AUT.iMessageType
where datediff(hh,AUM.dtMessageTime,getdate()) <= 24
[ls explain me this?
Thanks in Advance,
Subhani
db2 source datastage
Moderators: chulett, rschirm, roy
Re: db2 source datastage
Hi dssubhani,
this is simply SQL it is not related to DataStage it is just executed through DataStage.
The two tables are joined on the iMessageType column and if a corresponding value exists in the vwNiceDBKitAuditMessageType table the
RefFlag is returned with the result set.
It seems that someone tried to restrict the result to the rows of the last 24 hours but there is no datediff function in DB2 so I assume this statement has been copied from somewhere else...
You can check out timestampdiff in DB2 instead.
So I suggest you search for someone who knows SQL in your company.
Ask your DBA if you do not know anyone else.
I highly recommand the DB2 SQL Cookbook from Graeme Birchall:
http://mysite.verizon.net/Graeme_Birchall/id1.html
this is simply SQL it is not related to DataStage it is just executed through DataStage.
The two tables are joined on the iMessageType column and if a corresponding value exists in the vwNiceDBKitAuditMessageType table the
RefFlag is returned with the result set.
It seems that someone tried to restrict the result to the rows of the last 24 hours but there is no datediff function in DB2 so I assume this statement has been copied from somewhere else...
You can check out timestampdiff in DB2 instead.
So I suggest you search for someone who knows SQL in your company.
Ask your DBA if you do not know anyone else.
I highly recommand the DB2 SQL Cookbook from Graeme Birchall:
http://mysite.verizon.net/Graeme_Birchall/id1.html
regards
Michael
Michael
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Note that DataStage regards # as a reserved character, which must be mapped (to "__035__" if I recall correctly when used in queries to DB2 to access table/column names containing that character.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 246
- Joined: Mon Jun 30, 2008 3:22 am
- Location: New York
- Contact: