Sensing for a particular field value in source

spracht · Post by **spracht** » Thu Aug 14, 2003 12:20 pm

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

spracht · Post by **spracht** » Thu Aug 14, 2003 1:04 pm

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

ray.wurlod · Post by **ray.wurlod** » Thu Aug 14, 2003 5:01 pm

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

chulett · Post by **chulett** » Thu Aug 14, 2003 5:15 pm

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

-craig

ray.wurlod · Post by **ray.wurlod** » Thu Aug 14, 2003 11:53 pm

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.

spracht · Post by **spracht** » Fri Aug 15, 2003 1:10 am

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?

ray.wurlod · Post by **ray.wurlod** » Fri Aug 15, 2003 3:40 am

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

spracht · Post by **spracht** » Fri Aug 15, 2003 4:56 am

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

chulett · Post by **chulett** » Fri Aug 15, 2003 5:26 am

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

ray.wurlod · Post by **ray.wurlod** » Fri Aug 15, 2003 6:15 pm

Stephan,

Another thing you can do, which I sometimes do with code in these posts, is to precede it with the &LT;pre&GT; tag (pre-formatted) and follow it with the &LT;/pre&GT; 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

roy · Post by **roy** » Fri Aug 15, 2003 6:45 pm

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.

ray.wurlod · Post by **ray.wurlod** » Sat Aug 16, 2003 5:04 pm

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