" />

  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.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.