|
|
|
|
|
|
| Author |
Message |
Gb
*nix forums beginner
Joined: 16 Jul 2006
Posts: 1
|
 Posted: Sun Jul 16, 2006 4:20 am Post subject:
Column XXXX not in specified tables. (#-206) <--- Please HELP!
|
|
|
Hi There,
I have:
select myTable.*,
Case
When myTable.problem_CLOSE_date = myTable.problem_creation_date then
2
When myTable.problem_CLOSE_date >0 and myTable.problem_status='C'
then 3
When myTable.problem_CLOSE_date >0 and myTable.problem_status='R'
then 5
When myTable.problem_status='N' then 4
When myTable.problem_status='O' then 1
Else 0
end AS Problem_Type_id
FROM Blah.Blah.myTable myTable
where (myTable.problem_CREATION_date < 20040531 ) AND Problem_Type_id
in(1,4)
I get:
ODBC--call failed.
[IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0206
- Column QACSM_PROBLEM_TYPE_ID not in specified tables. (#-206)
How can I fix this problem? I dont want to use this option:
where (myTable.problem_CREATION_date < 20040531 ) AND (Case
When myTable.problem_CLOSE_date = myTable.problem_creation_date then
2
When myTable.problem_CLOSE_date >0 and myTable.problem_status='C'
then 3
When myTable.problem_CLOSE_date >0 and myTable.problem_status='R'
then 5
When myTable.problem_status='N' then 4
When myTable.problem_status='O' then 1
Else 0
end ) in (1,4))
THANK YOU!!!!!!!! |
|
| Back to top |
|
 |
Jonathan Ball
*nix forums beginner
Joined: 12 Oct 2005
Posts: 3
|
| Posted: Sun Jul 16, 2006 4:20 am Post subject:
Re: Column XXXX not in specified tables. (#-206) <--- Please HELP!
|
|
|
Gb wrote:
| Quote: | Hi There,
I have:
select myTable.*,
Case
When myTable.problem_CLOSE_date = myTable.problem_creation_date then
2
When myTable.problem_CLOSE_date >0 and myTable.problem_status='C'
then 3
When myTable.problem_CLOSE_date >0 and myTable.problem_status='R'
then 5
When myTable.problem_status='N' then 4
When myTable.problem_status='O' then 1
Else 0
end AS Problem_Type_id
FROM Blah.Blah.myTable myTable
where (myTable.problem_CREATION_date < 20040531 ) AND Problem_Type_id
in(1,4)
I get:
ODBC--call failed.
[IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0206
- Column QACSM_PROBLEM_TYPE_ID not in specified tables. (#-206)
|
Because the column alias Problem_Type_id will *only*
have a value of 1 or 4 when the underlying table column
myTable.problem_status is 'N' or 'O', you could make
your WHERE clause read:
where (myTable.problem_CREATION_date < 20040531 )
and myTable.problem_status in ('N','O')
Of course, this brings up the question: if you're only
interested in the calculated values 1 and 4, why do you
even have the other values being calculated?
Another way to do it is to use a nested table expression:
select * from
(select myTable.*,
Case
When myTable.problem_CLOSE_date =
myTable.problem_creation_date
then 2
When myTable.problem_CLOSE_date > 0 and
myTable.problem_status='C'
then 3
When myTable.problem_CLOSE_date >0 and
myTable.problem_status='R'
then 5
When myTable.problem_status='N'
then 4
When myTable.problem_status='O'
then 1
Else 0
end AS Problem_Type_id
FROM Blah.Blah.myTable myTable
where myTable.problem_CREATION_date < 20040531 ) NT
where Problem_Type_id in (1,4)
| Quote: |
How can I fix this problem? I dont want to use this option:
where (myTable.problem_CREATION_date < 20040531 ) AND (Case
When myTable.problem_CLOSE_date = myTable.problem_creation_date then
2
When myTable.problem_CLOSE_date >0 and myTable.problem_status='C'
then 3
When myTable.problem_CLOSE_date >0 and myTable.problem_status='R'
then 5
When myTable.problem_status='N' then 4
When myTable.problem_status='O' then 1
Else 0
end ) in (1,4))
THANK YOU!!!!!!!! |
|
|
| Back to top |
|
|
Gb
*nix forums beginner
Joined: 16 Jul 2006
Posts: 1
|
| Posted: Sun Jul 16, 2006 4:20 am Post subject:
Re: Column XXXX not in specified tables. (#-206) <--- Please HELP!
|
|
|
Thank you, that works like a charm!!!
Many thanks to everybody else...
"Saml" <none@no_such_isp.com> wrote in message news:<Md9vc.913$_d4.7523@eagle.america.net>...
| Quote: | Depending on which OS release you are on, something like this:
with xxx as
(
select myTable.*,
Case
When myTable.problem_CLOSE_date = myTable.problem_creation_date then 2
When myTable.problem_CLOSE_date >0 and myTable.problem_status='C' then 3
When myTable.problem_CLOSE_date >0 and myTable.problem_status='R' then 5
When myTable.problem_status='N' then 4
When myTable.problem_status='O' then 1
Else 0
end AS Problem_Type_id
FROM Blah.Blah.myTable myTable
)
select * from xxx
where (xxx.problem_CREATION_date < 20040531 ) AND xxx.Problem_Type_id
in(1,4)
I think this works on V5R1 and later.
Sam
"Gb" <dontsendmecrapagain@yahoo.com> wrote in message
news:8557a7c4.0406010617.3bfcaa2e@posting.google.com...
Hi There,
I have:
select myTable.*,
Case
When myTable.problem_CLOSE_date = myTable.problem_creation_date then
2
When myTable.problem_CLOSE_date >0 and myTable.problem_status='C'
then 3
When myTable.problem_CLOSE_date >0 and myTable.problem_status='R'
then 5
When myTable.problem_status='N' then 4
When myTable.problem_status='O' then 1
Else 0
end AS Problem_Type_id
FROM Blah.Blah.myTable myTable
where (myTable.problem_CREATION_date < 20040531 ) AND Problem_Type_id
in(1,4)
I get:
ODBC--call failed.
[IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0206
- Column QACSM_PROBLEM_TYPE_ID not in specified tables. (#-206)
How can I fix this problem? I dont want to use this option:
where (myTable.problem_CREATION_date < 20040531 ) AND (Case
When myTable.problem_CLOSE_date = myTable.problem_creation_date then
2
When myTable.problem_CLOSE_date >0 and myTable.problem_status='C'
then 3
When myTable.problem_CLOSE_date >0 and myTable.problem_status='R'
then 5
When myTable.problem_status='N' then 4
When myTable.problem_status='O' then 1
Else 0
end ) in (1,4))
THANK YOU!!!!!!!! |
|
|
| Back to top |
|
|
Jonathan Ball
*nix forums beginner
Joined: 12 Oct 2005
Posts: 3
|
| Posted: Sun Jul 16, 2006 4:20 am Post subject:
Re: Column XXXX not in specified tables. (#-206) <--- Please HELP!
|
|
|
Gb wrote:
| Quote: | Thank you, that works like a charm!!!
Many thanks to everybody else...
|
His solution and mine are very similar. His uses a
common table expression; mine used a nested table
expression. In this instance, they work identically.
I tend to use a n.t.e. for this kind of thing more
often than a c.t.e. because I've known about them a bit
longer; I do use them both, however.
| Quote: |
"Saml" <none@no_such_isp.com> wrote in message news:<Md9vc.913$_d4.7523@eagle.america.net>...
Depending on which OS release you are on, something like this:
with xxx as
(
select myTable.*,
Case
When myTable.problem_CLOSE_date = myTable.problem_creation_date then 2
When myTable.problem_CLOSE_date >0 and myTable.problem_status='C' then 3
When myTable.problem_CLOSE_date >0 and myTable.problem_status='R' then 5
When myTable.problem_status='N' then 4
When myTable.problem_status='O' then 1
Else 0
end AS Problem_Type_id
FROM Blah.Blah.myTable myTable
)
select * from xxx
where (xxx.problem_CREATION_date < 20040531 ) AND xxx.Problem_Type_id
in(1,4)
I think this works on V5R1 and later.
Sam
"Gb" <dontsendmecrapagain@yahoo.com> wrote in message
news:8557a7c4.0406010617.3bfcaa2e@posting.google.com...
Hi There,
I have:
select myTable.*,
Case
When myTable.problem_CLOSE_date = myTable.problem_creation_date then
2
When myTable.problem_CLOSE_date >0 and myTable.problem_status='C'
then 3
When myTable.problem_CLOSE_date >0 and myTable.problem_status='R'
then 5
When myTable.problem_status='N' then 4
When myTable.problem_status='O' then 1
Else 0
end AS Problem_Type_id
FROM Blah.Blah.myTable myTable
where (myTable.problem_CREATION_date < 20040531 ) AND Problem_Type_id
in(1,4)
I get:
ODBC--call failed.
[IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0206
- Column QACSM_PROBLEM_TYPE_ID not in specified tables. (#-206)
How can I fix this problem? I dont want to use this option:
where (myTable.problem_CREATION_date < 20040531 ) AND (Case
When myTable.problem_CLOSE_date = myTable.problem_creation_date then
2
When myTable.problem_CLOSE_date >0 and myTable.problem_status='C'
then 3
When myTable.problem_CLOSE_date >0 and myTable.problem_status='R'
then 5
When myTable.problem_status='N' then 4
When myTable.problem_status='O' then 1
Else 0
end ) in (1,4))
THANK YOU!!!!!!!! |
|
|
| Back to top |
|
|
Knut Stolze
*nix forums Guru
Joined: 28 Jul 2005
Posts: 755
|
| Posted: Mon Jul 17, 2006 7:13 am Post subject:
Re: Column XXXX not in specified tables. (#-206) <--- Please HELP!
|
|
|
Gb wrote:
| Quote: | Hi There,
I have:
select myTable.*,
Case
When myTable.problem_CLOSE_date = myTable.problem_creation_date then
2
When myTable.problem_CLOSE_date >0 and myTable.problem_status='C'
then 3
When myTable.problem_CLOSE_date >0 and myTable.problem_status='R'
then 5
When myTable.problem_status='N' then 4
When myTable.problem_status='O' then 1
Else 0
end AS Problem_Type_id
FROM Blah.Blah.myTable myTable
where (myTable.problem_CREATION_date < 20040531 ) AND Problem_Type_id
in(1,4)
I get:
ODBC--call failed.
[IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0206
- Column QACSM_PROBLEM_TYPE_ID not in specified tables. (#-206)
|
You have to remember how SQL statements are evaluated. Namely, the WHERE
clause is evaluated _before_ the SELECT list. Thus, you simply can't
declare an expression in the select list and use it in a predicate in the
WHERE clause.
One solution was already given with common table expressions. Another is a
simple subquery:
SELECT *
FROM ( SELECT myTable.*,
CASE ... END AS Problem_Type_id
FROM Blah.Blah.myTable myTable ) AS x
WHERE x.problem_CREATION_date < 20040531 AND
x.Problem_Type_id IN (1,4)
Here you will see that the subquery is evaluated first. It produces a table
against which your predicate is applied.
--
Knut Stolze
DB2 Information Integration Development
IBM Germany |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
|
The time now is Mon Dec 01, 2008 11:19 pm | All times are GMT
|
|
Ringtones | Adverse Credit Remortgage | Cash ISA | Credit Cards | Home Loan
|
|
Copyright © 2004-2005 DeniX Solutions SRL
|
|
|
|
Other DeniX Solutions sites:
Unix/Linux blog |
electronics forum |
medicine forum |
science forum |
|
|
Privacy Policy
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
IBM DB2
