RE: SQL ignore part of WHERE if parameter is null -- RPG400-L

What about something like this

Select *
From ...
Where Fld1 = Case When :HostFld1 > '' Then :HostFld1 Else Fld1
End
And Fld2 = Case When :HostFld2 <> 0 Then :HostFld2 Else Fld2
End
And Fld3 Between Case When :HostFldFrom > '' Then
:HostFldFrom Else Fld3 End
And Case When :HostFldTo > '' Then
:HostFldTo Else Fld3 End

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Darren
Strong
Sent: Montag, 9. März 2020 13:26
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SQL ignore part of WHERE if parameter is null

I've been doing this. You can use a zero in place of ' ', if your field is
numeric. It's a little backwards looking, but, it saves extra ands and ors.

select * from ncnotl
where :wnnorn in (' ',ncnlnorn) and ncnldtpco = :wndtpco

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Mónica
Anastácio
Sent: Saturday, March 7, 2020 3:50 AM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: SQL ignore part of WHERE if parameter is null

CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know the
content is safe.

select * from ncnotl
where ncnlnorn = :wnnorn and ncnldtpco = :wndtpco

anyone now if its possible to ignore this - ncnlnorn = :wnnorn - when the
field has no information and pass to the other condition.
--
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@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
--
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@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com