db2 source datastage

[dssubhani]

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

[MT]

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

[dssubhani]

Thanks for u r help michel

Regards,
Subhani

[ray.wurlod]

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.

[arunkumarmm]

Even the job parameters or ENV variables are used in that way