Case and If statement issue in the sql
Moderators: chulett, rschirm, roy
Case and If statement issue in the sql
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
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
Re: Case and If statement issue in the sql
What type of problems?sshettar wrote:...but having problems executing the statement...
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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
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
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
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?
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?
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
-
- Participant
- Posts: 9
- Joined: Tue Sep 20, 2005 2:10 am
Re: Case and If statement issue in the sql
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.
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
Re: Case and If statement issue in the sql
No. Both ways are equivalent.Plagvreugd wrote:
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
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
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
-
- Participant
- Posts: 9
- Joined: Tue Sep 20, 2005 2:10 am
Re: Case and If statement issue in the sql
Yes I figured that out myself and already had removed most of my postpriyadarshikunal wrote: No. Both ways are equivalent.
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
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
-
- Participant
- Posts: 9
- Joined: Tue Sep 20, 2005 2:10 am
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.
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.
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
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
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
This seems overly complicated to me. Why not just parameterize the where clause?
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.
Code: Select all
Select distinct CASH_CODE CANCEL_SEQ, CREATE_DATE from AP.TJMSTRINV #pWhereClause#
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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
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