Re: Case in where-clause version dependent?

Oh - it's just too early and on a Sunday - to my brain seems to be on vacation already.

Of course it has to be:

where Case
When :wildcard ='%'
And objname like trim( :wxlib )
Then 1
When :wildcard = '*'
And objname = objname
Then 1
When :wildcard = ' '
And objname = :wxlib
Then 1
Else 0
End = 1

That way should work.

When you have multiple conditions on a when you cannot use the simple case form - my fault.

HTH
Daniel

Am 22.12.2024 um 10:32 schrieb stefan@xxxxxxxxxx:

Hi Daniel,
Thanks for helping me out,

Now I have:

exec sql
declare tctest08_cursor1 scroll cursor for
select objlib, objname, objtype, objowner
from table ( qsys2.object_statistics( 'QSYS', 'LIB', '*ALL' ))
where Case :wildcard
When '%'
And objname like trim( :wxlib )
Then 1
When '*'
And objname = objname
Then 1
When ' '
And objname = :wxlib
Then 1
Else 0
End = 1
for read only
;

Which gives me a similar error:
SQL7906 30 5964 Position 26 SQL function not supported on prior release.
Function is - IS TRUE.
Pointing at all three "When"

Am I misunderstanding something?

Best regards

Stefan

--
No trees were killed in the sending of this message, but a large number of electrons were terribly upset.

Stefan Tageson
+46 732 369934
stefan@xxxxxxxxxx

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Daniel Gross
Sent: Sunday, December 22, 2024 10:20 AM
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Subject: Re: Case in where-clause version dependent?

Hi Stefan,

First - 7.4 doesn't know TRUE or FALSE as stand alone expressions - so you need to do a compare.

Second - the the CASE expression is very strange IMHO.

Without testing it, this should work:

where Case :wildcard
When '%'
And objname like trim( :wxlib )
Then 1
When '*'
And objname = objname
Then 1
When ' '
And objname = :wxlib
Then 1
Else 0
End = 1

The case expression returns a value (here 1 or 0) and you have to compare it.

In 7.5 you can simply return TRUE or FALSE as these are a complete expression - it they don't exist in 7.4 or lower.

And better "flatten" the case expressions - no need for "else case" - a case can have multiple when and one else.

HTH
Daniel

Am 22.12.2024 um 10:08 schrieb stefan@xxxxxxxxxx:

where Case When :wildcard = '%'

Then objname like trim( :wxlib )

else

Case When :wildcard = '*'

Then objname = objname

else

Case When :wildcard = ' '

Then objname = :wxlib

end

end

end

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.


--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.