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.myTab le myTable
where (myTable.proble m_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_T YPE_ID not in specified tables. (#-206)
How can I fix this problem? I dont want to use this option:
where (myTable.proble m_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!!!!!!!!
7  7130 
"Gb" <do************ *****@yahoo.com > a écrit dans le message de news:85******** *************** ***@posting.goo gle.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.myTab le myTable
where (myTable.proble m_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_T YPE_ID not in specified tables. (#-206)
How can I fix this problem? I dont want to use this option:
where (myTable.proble m_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
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.myTab le myTable
where (myTable.proble m_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_T YPE_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.proble m_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.myTab le 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.proble m_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!!!!!!!!
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.myTab le myTable
)
select * from xxx
where (xxx.problem_CR EATION_date < 20040531 ) AND xxx.Problem_Typ e_id
in(1,4)
I think this works on V5R1 and later.
Sam
"Gb" <do************ *****@yahoo.com > wrote in message
news:85******** *************** ***@posting.goo gle.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.myTab le myTable
where (myTable.proble m_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_T YPE_ID not in specified tables. (#-206)
How can I fix this problem? I dont want to use this option:
where (myTable.proble m_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!!!!!!!!
Thank you, that works like a charm!!!
Many thanks to everybody else...
"Saml" <none@no_such_i sp.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.myTab le myTable
)
select * from xxx
where (xxx.problem_CR EATION_date < 20040531 ) AND xxx.Problem_Typ e_id
in(1,4)
I think this works on V5R1 and later.
Sam
"Gb" <do************ *****@yahoo.com > wrote in message
news:85******** *************** ***@posting.goo gle.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.myTab le myTable
where (myTable.proble m_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_T YPE_ID not in specified tables. (#-206)
How can I fix this problem? I dont want to use this option:
where (myTable.proble m_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!!!!!!!!
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_i sp.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.myTab le myTable
)
select * from xxx
where (xxx.problem_CR EATION_date < 20040531 ) AND xxx.Problem_Typ e_id
in(1,4)
I think this works on V5R1 and later.
Sam
"Gb" <do************ *****@yahoo.com > wrote in message
news:85****** *************** *****@posting.g oogle.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.myTab le myTable
where (myTable.proble m_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_T YPE_ID not in specified tables. (#-206)
How can I fix this problem? I dont want to use this option:
where (myTable.proble m_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!!!!!!!!
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.myTab le myTable
)
select * from xxx
where (xxx.problem_CR EATION_date < 20040531 ) AND xxx.Problem_Typ e_id
in(1,4)
I think this works on V5R1 and later.
Sam
"Gb" <do************ *****@yahoo.com > wrote in message
news:85******** *************** ***@posting.goo gle.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.myTab le myTable
where (myTable.proble m_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_T YPE_ID not in specified tables. (#-206)
How can I fix this problem? I dont want to use this option:
where (myTable.proble m_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!!!!!!!!
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.myTab le myTable
where (myTable.proble m_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_T YPE_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.myTab le myTable ) AS x
WHERE x.problem_CREAT ION_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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: ndsoumah |
last post by:
Hello Guys
I'm trying to run this query
$uneRequete = "SELECT * FROM Usager WHERE motDePasse =
{$loginPassword}";
and I get this error message : Error 1054: Unknown column 'xxxx' in
WHERE clause.....
where 'xxxx' is the content of $loginPassword
|
by: Philip Herlihy |
last post by:
If I'm reading my reference books correctly, I should be able to pick out
cells in a table by combining a <col> selector with a class selector, like
this:
col#thisid td.thisclass {color: red; }
... but it doesn't work. I can get the unadorned <col> selector to work in
IE6 and Opera (not Dreamweaver 2004):
col#thiscol {color: red; }
... but when I add a descendent selector it stops working. I'd be grateful
for advice or comments.
|
by: robert |
last post by:
can someone explain why (other than: You Can't Do That) it is that
the ALTER TABLE ...RESTART WITH 999 is not allowed on 390/v6??
looking at the SYSIBM tables SYSSEQUENCES and SYSSEQUENCESDEP structures
(i don't have grant to see the ...DEP table for some reason), it's
clear that the IDENTITY columns are supported by these tables.
it is frustrating to tell my developer colleagues that we have to
nuke tables just to restart the ID...
|
by: Jerry Camel |
last post by:
Let's try this again... I think I sent half a message before.
Things were working just fine and I'm not sure what changed. The code is pretty simple. But now I can't send a message anymore. I get this exception:
System.Web.HttpException: Could not access 'CDO.Message' object. ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException...
|
by: pl1 |
last post by:
Hi everyone,
I'm using MS-Access 2003, OS: Window XP Professional sp3.
In Northwind database (sample Database),the Suppliers table have column "SupplierID" which is also present in Products table, my question is how can I use same column in 2 tables without entering data in them manually
Also the relationship between 'Suppliers' table and 'Products' table is one-to-many. The column type in Products table for column "SuppliersID" is...
| | |
by: jigu |
last post by:
hi all
When i try to delete records i receieve this error Could not delete from specified tables.
please help soon.
my code is here
<%
dim rs,myconnection
dim sql, mysql
set myconnection=server.CreateObject("ADODB.Connection")
myconnection.provider = "Microsoft.Jet.OLEDB.4.0"
Set rs=server.CreateObject("ADODB.Recordset")
|
by: Big Moxy |
last post by:
Problem URL - http://projects.zanalysts.com/ariviewer/display.asp?ID=11
I'm using Access as the "database".
I get this error in the above URL:
ADODB.Fields error '800a0cc1'
Item cannot be found in the collection corresponding to the requested
name or ordinal.
/ariviewer/display.asp, line 498
|
by: metanarcissus |
last post by:
hi
i am looking for different values (quantity) in the same column in two tables with same format (same column headings).
One is the old version of our db and the other one is the new version.
These were originally excel files that i downloaded into Access as two tables and looked for unmatched records in them.
so 2 tables are exactly the same format but there has been some changes in the new table mainly quantity of the orders.
i...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
