Determine whether to use condition in SQL based on parameter

[StefL]

I've got a job that takes a parameter value ToDate (string), which determines which date to fetch data for from the source DB, which is Oracle 9.

So there is a condition at the end of the SQL statement in the following fashion:
Select <columns>
From <tables>
Where <conditions>
AND ToDate = to_date('#ToDate#',<formatstring>)

Now, I'd like to construct the DB stage in such a fashion that if the parameter ToDate is not given a value (i.e it will be 0), data for ALL days is to be fetched, that is, the last row of the SQL statement should be omitted.
Is there a way of accomplishing this without having to make two separate jobs - one with the ToDate condition row in the SQL and one without it?

It would be something like
IF #ToDate# = 0
THEN
Select <columns>
From <tables>
Where <conditions>
ELSE
Select <columns>
From <tables>
Where <conditions>
AND ToDate = to_date('#ToDate#',<formatstring>)
FI
Is this at all possible? I mean, what is written in the SQL window of the DB stage cannot as I understand it be Data Stage logic but must be SQL that the source DB accepts, all DS does is replace #param# statements with parameter values, or is there some way around this?

[baabi_26]

Sure there is a way to handle this. Comment out the last part in your SQL based on the Parameter value.

Where <condition>
#Paramcommstrt# AND ToDate = to_date('#ToDate#',<formatstring>) #paramcommend#

and in your sequencer uservariable activity check the value of the date. If it is null then set #Paramcommstrt# and #paramcommend# as "/*" and "*/" respectively. If it not null then set space for both. pass these values into the job. This is from top of my head. Others might give much better solution

[ray.wurlod]

An ingenious solution, and one which highlights the fact that parameter values can not be changed within the job itself.
You might consider constructing a query with a CASE statement. Without wasting a lot of thought on that approach, I can already see that it would be messy.

[baabi_26]

Ray, Just wondering what problems are you talking about. Can you please elaborate?

I thought CASE stmt can only be included in select list not as a part of your where clause. Am i wrong?

[chulett]

As you suspected, you can't do any IF-THEN-ELSE logic in your sql. It must be legal DML for the database in question, not any sort of procedural language, DataStage or otherwise.

Another option, one that we typically use when we need to do something like this, is to replace the entire 'extra' portion of your where clause with a single parameter. So, your query could turn into something like this in the job:

Code: Select all

Where <conditions> 
#WHERE_CLAUSE#

Then, rather than deciding whether to set your To_Date parameter or not, either take your date and build the entire phrase (including the AND)... or leave it empty.

Food for thought.

[ray.wurlod]

I thought CASE stmt can only be included in select list not as a part of your where clause. Am i wrong?

Depends which database you're using. You're right; most only allow it in the SELECT clause.

[StefL]

Baabi, that is really quite an idea!
Now, I see the problem that Ray points out that parameters cannot be changed from within a job.
In my case however, the job is called from a job sequence that has taken the ToDate value as in parameter and passes it on to the job that then uses it for the SELECT statement.
Now, if my memory doesn't fail me, you CAN use DS logic in the parameter assignment field, which means I can create the CommentParam in the job, and then when I call the job from the sequence use the value of ToDate to determine whether to assign a space or /* to CommentParam.
I'll dwell on this thought until Tuesday when I get back to the office (Monday is a holiday in Sweden) and can try it out for real .

[chulett]

Now, if my memory doesn't fail me, you CAN use DS logic in the parameter assignment field

Nope, 'fraid not.

That's why we tend to do everything in one parameter value which can easily be set in a routine and passed into a downstream parameter by a Sequencer. Unless you are writting your own job control code, then you are free to do pretty much anything you want.

[kcbland]

Oracle allows CASE in the columns, group, order, and where clauses. The CASE construct allows you to do IF-THEN-ELSE, just do it on the WHERE condition. WHERE conditions either are used for joins or for filters. In your situation, it's a conditional filter. If you have ToDate parameter value specified, then the dates must match to be TRUE. If you don't have a ToDate parameter value, then it's TRUE.

Just make sure your ToDate parameter data type in the DS job is String, because Date has a required value at runtime.

Code: Select all

Select <columns> 
From <tables> 
Where <conditions> 
AND
   CASE WHEN LENGTH('#ToDate#') > 0 AND ToDate = to_date('#ToDate#',<formatstring>) THEN 1 
        WHEN LENGTH('#ToDate#') = 0 THEN 1 
        ELSE 0 
   END

[roy]

Hi,
Just wanted to say I also use Craigs method.
It is dynamicly created at run time and you don't have to use/know DB specific syntax for "all" the ones out ther.

IHTH,

[StefL]

OK, so from what Craig says, I guess if I want to use the method with a CommentParam, I should use a routing upstream in the sequence to set it, based on the value of ToDate, and then just pass it along to the job.

Or I can use the CASE way suggested by Kenneth.

I'm still 'free' today though so it'll be until tomorrow before push comes to show on what method I'll eventually use.

[chulett]

Just make sure your ToDate parameter data type in the DS job is String, because Date has a required value at runtime.

Ken makes a good point about the usefullness of the CASE statement, so you could go that way if you are comfortable with that syntax.

Only wanted to point out what I quoted. I've done the same thing, using a String datatype for a Date so that it can be left blank. Otherwise, as Ken notes, a Date datatype will force you to input a valid date. The downside of the String type is you have no control over the format that people will input the date in or if it will even be a valid date. I usually only do this when I have custom Job Control validating the input, so that I can error out if something invalid is input.

Something to keep in mind.

[StefL]

Thanks Craig for pointing that out!
I've already noticed the use for using a string format for dates and taken a habit of doing so. I've tried using date as format when passing fields through Data Stage but it usually ends up getting messy so at least for parameters I do stick with strings and then use TO_DATE when using them in SQL.