Sensing for a particular field value in source
Moderators: chulett, rschirm, roy
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
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 <<! 2>/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
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 <<! 2>/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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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