Originally posted by: SystemAdmin

Hi,

I need execute a query from Job Control and it return me a value and this value keeps into a variable, like at this:

DimEnteMax=ExecSQL('datamart','select count(*) from d2714entben', 1)

Is there some way to do it?.

Thanks,

Victor.
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: SystemAdmin

you'll have to setup an output card as a temporary / scratch card set to SINK adapter (FILE adapter and ignore the results). you can use this card as something like a variable in datastage tx.

just make sure that your DBLOOKUP and your SQL query is valid. you'll also want to wrap around VALID function for error handling around the DBLOOKUP as well.

for example:

output card #1

OUTPUT_1

DUMMYITEM[1] := VALID (DBLOOKUP ("...SQL query...", "... DB OPTIONS..."), "###FAILED###")
DUMMYITEM[2] := IF (DUMMYITEM[1] = "###FAILED###", FAIL ("...error message..."), DUMMYITEM[1])

and from subsequent output cards, you can reference the SQL result set by using DUMMYITEM[1]:OUTPUT_1

hope this helps.
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: SystemAdmin

oops, correction on the syntax...

DUMMYITEM[2] := IF (DUMMYITEM[1]b:2a3caabcc0:OUTPUT_1[/b:2a3caabcc0] = "###FAILED###", FAIL ("...error message..."), DUMMYITEM[1]b:2a3caabcc0:OUTPUT_1[/b:2a3caabcc0])
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender