DSXchange

One possibility:

Write a shellscript that does the following query against your database:

select count(*) from table where field='N';

Catch the result into a variable in your controlling job. If it's 0, kick off the load else don't (I must concede that this only works, if the value of the field in question definitely can only be 'Y' or 'N' [:I]).

Hope this helps



Stephan

Alok,

I will gladly try, though I'm not an Oracle expert, and I have some experience in scripting on UNIX, not NT. Let's assume we had an informix database, so we would use dbaccess (is this sqlplus for oracle?) as frontend. Within the shellscript, I would have a lines like this:

* possibly some enviromental stuff

count=$(dbaccess dbname &LT&LT! 2&GT/dev/null|egrep -v "^$|count"
select count(*) from table where field='N';
!
)

- read the number into a variable called count
- exclude empty lines, other stuff you don't need by the egrep
- expression, which you might have to elaborate, to get only the number you want

Afterwards you would have to echo the result back to a caller, possibly without newline:

echo "$countc"

This must be the only output your script does.
Within a controlling job, call the script using the following syntax:

DSExecute('UNIX', path/nameofscript, Output, SystemReturnCode)

Take a look into the online help for more details.

If successful, the Output variable holds your number and you could proceed:

If Not(Output) then
Load
end else
Don't
end


Stephan

If other values than 'Y' and 'N' can exist, simply change the logic to where field 'Y'.[:D]

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

Unless, of course, one of the 'other values' is null. [:)] In that case, add "or is null" to the query.

-craig

Aha! In DataStage BASIC, NULL always takes the ELSE path, on the grounds that it can't be asserted to be true. Therefore, field 'Y' as an output constraint will not select rows where field IS NULL.

There seems to be a display problem with a line of code in my last reply:

Looking correct when editing, the line:

count=$(dbaccess dbname ...

displays completely different after being saved. Any idea how to avoid that?

This stuff is all HTML-based. I suspect it's seeing your < as the beginning of an HTML tag. You can use & L T ; (with no intervening spaces) to represent < and, similarly, & G T ; for >.

Thank you, Raj, it looks better now, though I can hardly spot the blank between '!' and '2', not even when I tried with multiple spaces.

Stephan

quote:Originally posted by Ray.Wurlod
Aha! In DataStage BASIC, NULL always takes the ELSE path, on the grounds that it can't be asserted to be true. Therefore, field 'Y' as an output constraint will not select rows where field IS NULL.

Aha back at you! This is *always* true in the three-value-logic world, not just in DataStage BASIC. Hence my point - if you want to make sure all rows are marked with a 'Y' and the field can be null, a simple " 'Y'" check won't cut the mustard. (whatever that is supposed to mean... who cuts mustard?) [:)]

-craig

Stephan,

Another thing you can do, which I sometimes do with code in these posts, is to precede it with the <pre> tag (pre-formatted) and follow it with the </pre> tag. For example:
LOCATE Arg1 In CodeList By "AL" Setting Location
Else
Ins Arg1 Before CodeList
End

Craig,

Anything is possible in a Boolean context for particular values of "true". [:)]

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

Hi,
Just wanted to point that I always prefer to remain within the tool if I can,
So I would implement this in pure DS and not go out to the command line utilities.

But again this is only my humble opinion [:)]

Good Luck,

Roy R.

Roy,

My reading of the original post is that he wanted some condition to apply before kicking off the ETL job. To be sure, the determination could be performed by an earlier DataStage job all under the control of a job sequence of job control routine, but the technique of determining that the requisite condition is in place still has to be resolved before invoking that from a DataStage tool.
(In fact, that earlier DataStage job could use SELECT DISTINCT ... and parse the output from that.)

Regards,

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518