Case and If statement issue in the sql

[sshettar]

Hi All,

I have a senario where i need to execute different sql
based on the parameter value that i pass. i tried using case statement and also if statement , but having problems executing the statement.
the statement i used was

IF statement :

Select if (Select 1 from sysibm.sysdummy1) =1
then (Select distinct COMPANY from Table1)
else
(Select distinct VENDOR from Table1)
end) from Table1

Case Statement:

SELECT
CASE (Select 1 from sysibm.sysdummy1)
WHEN 1 THEN (Select distinct COMPANY from Table1)
ELSE (Select distinct VENDOR from Table1)
END
FROM Table1

[ArndW]

...but having problems executing the statement...

What type of problems?

[sshettar]

when i run the statement 1 i get this error
42601(-104)[IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token "1" was found following "Select if (Select ". Expected tokens may include: "<space>". SQLSTATE=42601
(0.15 secs)

and when i run statement 2 i get this error

42804(-581)[IBM][CLI Driver][DB2/AIX64] SQL0581N The data types of the result-expressions of a CASE expression are not compatible. SQLSTATE=42804
(0.14 secs)


Well in both cases i am writing sql instead of calling a parameter ( as i am just testing if that will work ) , but once the job is done the parameter value is called in to check and run the right select sql

[priyadarshikunal]

are you passing anything in the query as parameter?? Have you tried to find out what query it tried to execute??

You haven't provided enough details to figure out the problem.

I believe the first statement says its treating "1" as a token instead of literal. why don't you use 1=1 instead for selecting it from dummy table.

second statement has also the same problem as the selected type doesn't match the value after 'when'.

Have you tried running the sql in DB2 directly?

[Plagvreugd]

An alternative approach might be, steering away from parameterized SAL and towards DataStage-functionality, to use a sequence in which you use a UserVariables-Activity. You can use an if-function here to 'calculate' the correct SQL based on your parameter and then insert the value of this variable into a parameter of the job and then just run the SQL that is in that parameter.

[priyadarshikunal]

I suppose you're having problems executing the statement due to problems writing it.

The correct CASE-statement that I know has the syntax
CASE WHEN <condition> THEN <expression> ELSE <other expression> END

No. Both ways are equivalent.

case 1 when 1 then <> Else <> end
case when 1=1 then <> else <> end

Read this topic for more details:

http://publib.boulder.ibm.com/infocente ... 023458.htm

[Plagvreugd]

No. Both ways are equivalent.

Yes I figured that out myself and already had removed most of my post

[sshettar]

Let me explain the senario,

i have 3 jobs which almost the same . the only difference the number of records that is being pulled . Hence i thought i could make them all as one job and parameterize the job so that the db2 stage selects the right query depending on the parameter value that i pass.

the number of columns for all the 3 jobs are the same , its just the number of records .

my db2 stage will have all 3 queries within it and would only execute the right one based on the value that i pass.

I guess i have expalined it a little better this time .
Any thoughts on how to impelement this ( how to write such a query)

Any help is highly appreciated

Thanks

[Plagvreugd]

To me this desciption is a bit different from the example in your OP. In your first post the result will not just have a different number of records, but it contains different columns: COMPANY instead of VENDOR.

Have you tried, as priyadarshikunal suggests, to run these queries directly in DB2? That at least will tell you (and us) if there's an SQL-issue or a DataStage-issue.

To me the CASE-error suggests that COMPANY and VENDOR are columns of a different datatype.

[sshettar]

well i am trying to execute this query in AQT ( advanced Query Tool ) directly on DB2 . Well pardon me , probably the example i gave was wrong, well my sql should output about 15 columns . The other sql as well should output the same 15 columns but after doing a union with some other table . But the net number of columns are same and identical.

I am trying with some different example much closer to what i need and i am trying this query using the case statment , but the sql is throwing in an error stating

" 42823(-412)[IBM][CLI Driver][DB2/AIX64] SQL0412N Multiple columns are returned from a subquery that is allowed only one column. SQLSTATE=42823
(0.14 secs)"
the sql is as below


SELECT * from (Select
(CASE 1
WHEN 1 THEN (Select distinct CASH_CODE, CANCEL_SEQ, CREATE_DATE from AP.TJMSTRINV)
ELSE (Select distinct CASH_CODE CANCEL_SEQ, CREATE_DATE from AP.TJMSTRINV where CASH_CODE <> '020' )
END)
FROM AP.TJMSTRINV)

I do understand the error as the output of a case statement should be just one column where as my query is outouputing 3 columns .

Can somebody help me accomplish my task.
Any help is highly appreciated

Thanks in advance

[Sainath.Srinivasan]

Your SQL is performing select on the same table in the inner query from which you are selecting the outer query.

What are you planning to achieve ?

Did you try running in db2 query tool itself ?

[chulett]

This seems overly complicated to me. Why not just parameterize the where clause?

Code: Select all

Select distinct CASH_CODE CANCEL_SEQ, CREATE_DATE from AP.TJMSTRINV #pWhereClause#

That way if you want all records you leave the parameter blank or set it to " where CASH_CODE <> '020' " (etc) to filter the results.

[sshettar]

Hi Craig.


Well it sems like i am not explaining the senario well.there are this two jobs which exist for different division numbers.
the 2 jobs that already exist differ only by the sql in the db2 stage
and the sql difference is that one job just selects couple of fields from the table1 while the other job's sql selects the same couple of fields from the same table1 as well but after doing a union all with another table table2 .
Well the 2nd sql also just outputs the same set of fields nothing less or nothing more even though it is doing the union all with another table2.

so i wanted to make it just one job by paramertising it where in it picks the right sql based on the job that i am trying to run.

In this case the 2 jobs is for 2 different division numbers. so i want to create one such varible which hold the division num and based on the variable value the db2 stage should pick the right appropriate sql.

I hope i have explained the senario well this time.

[chulett]

OK. First make sure your SQL syntax is valid outside of DataStage and then you should be able to get a job to run it properly.

If both SQL queries select the same number / type / order of columns, then perhaps you would find the "sql file" approach cleaner. Both sql statements could go into files and then the stage could load one or the other in at runtime based on your parameter. I've done this by ensuring that the parameter value is part of the sql filenames in question, or you could parameterize the entire filename if you like.

[sshettar]

Hi Craig,

Thanks for the suggestion . I'm trying to do that .I have an issue with it , my sql has div number as parameter and looks like the sql that we run from a file will not accept any parameter . Is there a way to acheive this ?
I tried this with one simple query which is like this
SELECT COUNT(*) as count FROM #$p6KSingSrcSchema#.TJMSTRIN

and the error i'm getting is like this

"APT_CombinedOperatorController,0: Fatal Error: Fatal: [IBM][CLI Driver][DB2/AIX64] SQL0204N "#$P6KSINGSRCSCHEMA#.TJMSTRINV" is an undefined name. SQLSTATE=42704
SQLExecDirect: Error executing statement 'SELECT COUNT(*) as count FROM #$p6KSingSrcSchema#.TJMSTRINV'. See following DB2 message for details."

How can i parameterise the sql now

Thanks in advance