By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,658 Members | 1,218 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,658 IT Pros & Developers. It's quick & easy.

Column XXXX not in specified tables. (#-206) <--- Please HELP!

P: n/a
Gb
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!!!!!!!!
Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a

"Gb" <do*****************@yahoo.com> a écrit dans le message de news:85**************************@posting.google.c om...
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!!!!!!!!


Hi,
You can create a stored procedure in which you paste your sql stm to return a result set and call this stored procedure from the
client to retrieve it.
hope that helps -- Philippe --

C/EXEC SQL DECLARE C1 CURSOR FOR
C+ select myTable.*,
C+ Case
C+ When ...
C+ ...
C+ end AS Problem_Type_id
C+ FROM ...
C+ where ...
C/END-EXEC
C*
C/EXEC SQL
C+ OPEN C1
C/END-EXEC
C/EXEC SQL
C+ SET RESULT SETS CURSOR C1
C/END-EXEC
Nov 12 '05 #2

P: n/a
Gb wrote:
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)

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!!!!!!!!


Nov 12 '05 #3

P: n/a
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" <do*****************@yahoo.com> wrote in message
news:85**************************@posting.google.c om...
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!!!!!!!!

Nov 12 '05 #4

P: n/a
Gb
Thank you, that works like a charm!!!

Many thanks to everybody else...

"Saml" <none@no_such_isp.com> wrote in message news:<Md****************@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" <do*****************@yahoo.com> wrote in message
news:85**************************@posting.google.c om...
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!!!!!!!!

Nov 12 '05 #5

P: n/a
Gb wrote:
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.

"Saml" <none@no_such_isp.com> wrote in message news:<Md****************@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" <do*****************@yahoo.com> wrote in message
news:85**************************@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!!!!!!!!


Nov 12 '05 #6

P: n/a
Works at V4R5, too.

Saml wrote:
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" <do*****************@yahoo.com> wrote in message
news:85**************************@posting.google.c om...
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!!!!!!!!



Nov 12 '05 #7

P: n/a
Gb wrote:
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
Jul 17 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.