I wanted to use the CASE command in a query on a DB2 Table (just like the DECODE function of Oracle), but it seems that BO does not like it. I get the error “SQL30073N “2411” Parameter value “0000” is not supported.”. The query looks like this :
select
case
when v1 >= 0 then ‘+’ concat v2
when v1 < 0 then ‘-’ concat v2
end
from tab1
What middleware are you using to connect to DB2? What version of BO? If you are connecting via CAE there are a few patches on BO & CAE that need to be installed.
I wanted to use the CASE command in a query on a DB2 Table (just like the DECODE function of Oracle), but it seems that BO does not like it. I get the error “SQL30073N “2411” Parameter value “0000” is not supported.”. The query looks like this :
select
case
when v1 >= 0 then ‘+’ concat v2
when v1 < 0 then ‘-’ concat v2
end
from tab1
BusinessObjects supports all your datbase features in the SQL definition. Of course it supports CASE syntax. Unfortunately, your statement seems to be incorrect. (V2 seems to be numeric - you can’t concatenate numeric and text fields). So, run this SQL directly into DB2, you should get the same error…
Walter.
DI Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna / Austria Tel: +43-1-8151456-12, Fax: +43-1-8151456-21 e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at
The DB2 CASE function is new to DB2 v5. Business Objects is not yet supporting this version. I added the function to my .PRM file but I’ve also gotten an SQL error, even though the same SQL works fine outside of BusObj. I guess we will have to wait for them to support the current version of DB2.
The DB2 CASE function is new to DB2 v5. Business Objects is not yet
supporting this version. I added the
function to my .PRM file but I’ve
also gotten an SQL error, even though
the same SQL works fine outside
of BusObj. I guess we will have to wait for them to support the current version of DB2.
I don’t think this is true. The SQL errors (at least the one Pierrot got) are returned from your database not BusinessObjects. It is the database that doesn’t like that SQL, and whoever suggested that Pierrot’s problem was related to a mixed format issue is probably right. That is the kind of message you can get. There is something wrong with his SQL. Just to satisfy myself I created an object with a case statement and then put the object in a query and it worked great. You can use the case statement with the current BusinessObjects.
I’m using BO 4.1.2, DB2 Connect V5, DB2 V5 on OS/390 1.3. I’ve tried my query in DB2 on the mainframe by simply cutting and pasting it : it works perfectly. Then I tried it with the DB2 Connect Command Center and I got this error : we started a trace in DB2 (on the mainframe) and noticed that the query was not even sent to DB2. So it seems that the problem is in DB2 Connect.
I’ve tried this simple query :
select comm,
case
when comm >= 1000 then ‘> 1000’
when comm < 1000 then ‘< 1000’
end
from q.staff
This query works perfectly in QMF, but running it from the Command Center of DB2 Connect gives me the error
SQL0332N There is no available conversion for the source code page “0” to the target code page “1252”. Reason code 1. (1252 is the codepage we have on our NT workstations).
Is there a patch for this problem ?
Pierrot
The DB2 CASE function is new to DB2 v5. Business Objects is not yet
supporting this version. I added the
function to my .PRM file but I’ve
also gotten an SQL error, even though
the same SQL works fine outside
of BusObj. I guess we will have to wait for them to support the current version of DB2.
I don’t think this is true. The SQL errors (at least the one Pierrot got) are returned from your database not BusinessObjects. It is the database that doesn’t like that SQL, and whoever suggested that Pierrot’s problem was related to a mixed format issue is probably right. That is the kind of message you can get. There is something wrong with his SQL. Just to satisfy myself I created an object with a case statement and then put the object in a query and it worked great. You can use the case statement with the current BusinessObjects.
The DB2 CASE function is new to DB2 v5. Business Objects is not yet supporting this version. I added the function to my .PRM file but I’ve also gotten an SQL error, even though the same SQL works fine outside of BusObj. I guess we will have to wait for them to support the current version of DB2.
DB2 V5 (Universal Server) is already supported…
Walter
DI Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna / Austria Tel: +43-1-8151456-12, Fax: +43-1-8151456-21 e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at
I’m using BO 4.1.2, DB2 Connect V5, DB2 V5 on OS/390 1.3. I’ve tried my query in DB2 on the mainframe by simply cutting and pasting it : it works perfectly. Then I tried it with the DB2 Connect Command Center and I got this error : we started a trace in DB2 (on the mainframe) and noticed that the query was not even sent to DB2. So it seems that the problem is in DB2 Connect.
I’ve tried this simple query :
select comm,
case
when comm >= 1000 then ‘> 1000’
when comm < 1000 then ‘< 1000’
end
from q.staff
One of our customers uses exactly this configuration: DB2 V5, with DB2 connect Vsomething. This something is what I don’t know… But there, it works perfectly. Read in the release notes, I think I remember something was written there on DB2V5 connectivity and patches on DB2 Connect…
Walter
DI Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna / Austria Tel: +43-1-8151456-12, Fax: +43-1-8151456-21 e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at
I got an answer from IBM in Toronto. They believe it’s a bug. I sent them a trace and I am waiting now for more informations. Thank you all
Pierrot
I’m using BO 4.1.2, DB2 Connect V5, DB2 V5 on OS/390 1.3. I’ve tried my query in DB2 on the mainframe by simply cutting and pasting it : it works perfectly. Then I tried it with the DB2 Connect Command Center and I got this error : we started a trace in DB2 (on the mainframe) and noticed that the query was not even sent to DB2. So it seems that the problem is in DB2 Connect.
I’ve tried this simple query :
select comm,
case
when comm >= 1000 then ‘> 1000’
when comm < 1000 then ‘< 1000’
end
from q.staff
One of our customers uses exactly this configuration: DB2 V5, with DB2
connect
Vsomething. This something is what I don’t know… But there, it works perfectly. Read in the release notes, I think I remember something was written there on DB2V5 connectivity and patches on DB2
Connect…
in the SQL definition. Of course it supports CASE syntax. Unfortunately, your statement seems to be incorrect. (V2 seems to be numeric - you can’t concatenate numeric and text fields). So, run this SQL directly into DB2, you should get the same error…