1. Sang Chul Song
  2. PowerServer
  3. Sunday, 13 February 2022 15:32 PM UTC

If a case statement is used in the where clause of the select of the DDDW datawindow, an error occurs.

ex)

/* failed select */

select  a.major_cd as major_cd,
          a.minor_cd as minor_cd, 
          a.cd_nm as cd_nm
from   cob01t a
where a.use_yn = 'Y'
and    a.major_cd like case :as_arg2 when '*' then :as_arg2 else :as_arg1 end
and    a.minor_cd <> '*'
and    nvl(a.rel_cd1, '%') like case :as_arg2 when '*' then :as_arg2 else :as_arg1 end
order by a.minor_cd

 

/* success select */

select  a.major_cd as major_cd,
          a.minor_cd as minor_cd, 
          a.cd_nm as cd_nm
from   cob01t a
where a.use_yn = 'Y'
and    a.major_cd like :as_arg1
and    a.minor_cd <> '*'
and    nvl(a.rel_cd1, '%') like :as_arg1
order by a.minor_cd

 

 

 

 

Sang Chul Song Accepted Answer Pending Moderation
  1. Monday, 14 February 2022 00:33 AM UTC
  2. PowerServer
  3. # 1

PowerBuilder DB Painter run, no problems

DB Tool execute, no problems

only PowerServer REST API execute problems

Comment
  1. Armeen Mazda @Appeon
  2. Monday, 14 February 2022 01:15 AM UTC
Please open support ticket and provide test case, it is either unsupported feature or bug if this works in client/server but not PowerServer. Thanks.
  1. Helpful 2
  1. Sang Chul Song
  2. Monday, 14 February 2022 01:24 AM UTC
Thank's Armeen ^.^

I have already issued a ticket.

Ticket number is "Bug 7945"
  1. Helpful 1
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Sunday, 13 February 2022 16:52 PM UTC
  2. PowerServer
  3. # 2

I think maybe one of the problems might be in this part:

and    nvl(a.rel_cd1, '%') like case :as_arg2 when '*' then :as_arg2 else :as_arg1 end

What you are doing when rel_cd1 is null, is comparing:

'%' Like ........ whatever ........

I don't think that would work, like statements recognize '%" only behind the LIKE word on the right. I'm not sure, but I don't think that that would work.

EDIT: since you say that "and    nvl(a.rel_cd1, '%') like :as_arg1" does work.

 

Also, why are you doing a LIKE if you aren't using a '%' in the value of the like statement on the right?

regards

 

Comment
  1. Sang Chul Song
  2. Monday, 14 February 2022 00:35 AM UTC
DB Driver is Oracle, may be this issue is bug
  1. Helpful 1
  1. Sang Chul Song
  2. Monday, 14 February 2022 00:50 AM UTC
When comparing values, the preceding sentence is recognized as a character string, and the latter sentence is used as a sentence comparing the preceding character string.

You can think of the '%' in front as a meaningless string.

And since '%' may come after or an exact value may come, nvl(rel_cd1, '%') is just performed to handle the comparison failure.
  1. Helpful 1
  1. Miguel Leeuwe
  2. Monday, 14 February 2022 01:33 AM UTC
Yes, I definitely think it´s a bug. Oracle has had quite a few in the latest Powerserver versions.

Good luck!
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.