Topic Options
#57082 - 05/30/19 04:04 PM SQLinside Database Properties
tbradley Offline
OL Guru

Registered: 05/03/11
Posts: 138
I am trying to figure out how to do a statement similar to the one below. When I try to use IFNULL it says it isn't supported when I run it. Can someone help?

SELECT Pflag,ESflag,RMFlag,Recipent
FROM TblECMmast
WHERE Trader = '%{MA_NAME}' and Type = 'CO' and Customer = '%{CustID}' and Location =
IFNULL ( (SELECT location
FROM TblECMmast
WHERE Trader = '%{MA_NAME}' and Type = 'CO'
and Customer = %{CustID}' and Location =
%{Loc} ), ' ' )

What I am trying to do is this...

I have a data base that has records like below

Field1 Field2 Field3 Field4
John Doe WALMART 1001 email@abc.com
John Doe WALMART 1285 email2@abc.com
John Doe WALMART email3@abc.com

So What I want is this..... if I have an exact match on Field 1,2, and 3 then I want to return field4. If I don't match on all 3 and match on Field 1 and 2 (so have nothing in field 3, therefore matching the 3rd one) return field4(email3@abc.com)

Any help would be greatly appreciated.

Top
#57090 - 05/31/19 10:55 AM Re: SQLinside Database Properties [Re: tbradley]
Philippe F. Offline
OL Expert

Registered: 09/06/00
Posts: 1926
Loc: Objectif Lune, Montreal, Qc
First, you need to make sure your Database Engine actually supports IFNULL. MySQL does, but not SQL Server or Oracle (although they both have equivalent methods).

So assuming your DB engine supports IFNULL and assuming you're aware that using it in a WHERE clause will cause performance issues (re: this page ), I still don't understand why you are using an entirely new sub-query in your WHERE clause when everything is taking place in the same table.

Couldn't you rewrite that query simply as :
Code:
SELECT Pflag, ESflag, RMFlag, Recipent, IFNULL(Location,' ') Location
FROM TblECMmast
WHERE 	Trader = '%{MA_NAME}' and 
		Type = 'CO' and 
		Customer = '%{CustID}' and 
		Location = %{Loc}  
_________________________
Technical Product Manager
I don't want to achieve immortality through my work; I want to achieve immortality through not dying - Woody Allen

Top
#57114 - 06/11/19 04:56 PM Re: SQLinside Database Properties [Re: tbradley]
tbradley Offline
OL Guru

Registered: 05/03/11
Posts: 138
I actually used ISNULL but the above code doesn't work. Because there is a number in location field3 in my example on the form that I am picking it up at. What I want is this. My form has a location number 1086. I want to go out to the database and see if there is a record where the Trader, Type, Customer and Location match. If there is then I want to return those fields. If there is not, then I want to check just Trader, Type, and Customer with blanks in the location and return those fields. The location number on the form will always have something in it, but the database may not always have a location number in it. I only shouldn't get a record is the trader, type, and customer don't match.

Top
#57117 - 06/13/19 05:23 PM Re: SQLinside Database Properties [Re: tbradley]
Philippe F. Offline
OL Expert

Registered: 09/06/00
Posts: 1926
Loc: Objectif Lune, Montreal, Qc
So since you always want to return the location (it'll automatically be blank if the field is empty), you don't need to use ISNULL in the SELECT part of your statement. It would seem to me that all you need is a slightly different WHERE clause, in which you use the ISNULL instruction:

Code:
SELECT Pflag, ESflag, RMFlag, Recipent, Location
FROM TblECMmast
WHERE 	Trader = '%{MA_NAME}' and 
		Type = 'CO' and 
		Customer = '%{CustID}' and 
		( 
                   Location = %{Loc} OR 
                   ISNULL(Location) 
                )

This will match Trader, Type and Customer exactly and will either match Location if it contains the proper location number or if it is empty.

Note that depending on your database engine, ISNULL() may have a different syntax so for instance, you may have to change the above query to something like this:
Code:
SELECT Pflag, ESflag, RMFlag, Recipent, Location
FROM TblECMmast
WHERE 	Trader = '%{MA_NAME}' and 
		Type = 'CO' and 
		Customer = '%{CustID}' and 
		( 
                   Location = %{Loc} OR 
                   ISNULL(TRIM(Location),"") =""
                )
_________________________
Technical Product Manager
I don't want to achieve immortality through my work; I want to achieve immortality through not dying - Woody Allen

Top