Nested IF's in SQL SELECT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Nested IF's in SQL SELECT

Author

Topic

jdgti
Starting Member

5 Posts

Posted - 2008-05-09 : 04:10:55

I am trying to get some functionality from nested IF's witin a SQL Select Statement. I do not want to create a stored procedure as I have another program that must use select statements that I will be using once I have this query written. Below is my code.


SELECT 
Sales.OrderID, 
Buyers.Userid, 
Buyers.Email, 
Sales.ShippingCo,
IF (Addresses.Company IS NULL OR Addresses.Company = '') 
	BEGIN
		IF ( RTRIM(LTRIM(Addresses.FirstName)) IS NULL 
			RTRIM(LTRIM(Adresses.LastName)) AS CompanyOrName,
		ELSE
			RTRIM(LTRIM(Addresses.FirstName)) & ' ' & RTRIM(LTRIM(Adresses.LastName)) AS CompanyOrName,
	END
ELSE
	Addresses.Company AS CompanyOrName,
END

All I am trying to do is join the first and last names as the company in my table if the company doesn't exist, and then only display the last name if the first name is null.

I keep getting the error "Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'IF'."

I know I am close to geting this to work, but for some reason googling for tutorials on writing IF statements isn't helping out.

Thanks ahead of time for any help. It will be greatly appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-09 : 04:23:11

IF is procedural, CASE is inline.
101 in any SQL class.

But in this scenario, there is no need for either

SELECT	Sales.OrderID, 
	Buyers.Userid, 
	Buyers.Email, 
	Sales.ShippingCo,
	COALESCE(Addresses.Company, RTRIM(LTRIM(Addresses.FirstName)) & ' ' & RTRIM(LTRIM(Adresses.LastName)), RTRIM(LTRIM(Adresses.LastName))) AS CompanyOrName
FROM	...

E 12°55'05.25"
N 56°04'39.16"

jdgti
Starting Member

5 Posts

Posted - 2008-05-09 : 04:57:40

Thank you Peso. Your help is appreciated. Your method throws a different error, but somehow I think it is not your code but the set up of my database. I get this error on the line you wrote with the COALSCE.

"The multi-part identifier "Adresses.LastName" could not be bound."

Do you know what this means?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-09 : 04:59:26

Yes. You must JOIN the Addresses table to the SELECT query.
You didn't post full query.

E 12°55'05.25"
N 56°04'39.16"

jdgti
Starting Member

5 Posts

Posted - 2008-05-09 : 05:16:04

I'm Sorry. Here is my full query. Even though you say it is the JOIN section, it shows the error on the first line of the query. I am sure you are right about the join, but I hopefully you can tell from my query where the err is...



SELECT  Sales.OrderID,
        Buyers.Email,
        COALESCE( Addresses.Company, RTRIM(LTRIM(Addresses.FirstName)) & ' ' & RTRIM(LTRIM(Adresses.LastName)), RTRIM(LTRIM(Adresses.LastName)) ) AS CompanyOrName,
		RTRIM(LTRIM(Addresses.FirstName)) & ' ' & RTRIM(LTRIM(Addresses.LastName)) AS Attention,
        Addresses.AddressLine1,
        Addresses.AddressLine2,
        Addresses.City ,
        Addresses.State ,
        Addresses.Zip,
        Addresses.Country ,
        COALESCE( Addresses.Phone, 'xxx-xxx-xxxx') AS Phone,
        Sales.StatusID,
        (Sales.WeightLbs)*(Sales.QtySold) AS WeightLbs ,
        Sales.Title ,
		CASE WHEN (Addresses.Country <> 'US') THEN Sales.QtySold
			ELSE NULL
		END AS QtySold,
        CASE WHEN (Addresses.Country <> 'US') THEN Sales.SalePrice
			ELSE NULL 
		END AS SalePrice,
        CASE WHEN (Addresses.Country <> 'US') THEN '02'
			ELSE NULL
		END AS ReasonForExport ,
        CASE WHEN (Addresses.Country <> 'US') THEN 'USD'
			ELSE NULL
		END AS CurrencyUPS,
        CASE WHEN (Addresses.Country <> 'US') THEN 'US'
			ELSE NULL
		END AS CountryofOrign ,
        CASE WHEN (Addresses.Country = 'CA') THEN 'NMB'
			WHEN (Addresses.Country<>'CA' AND Addresses.Country <>'US') THEN 'EA' 
			ELSE NULL
		END AS UnitMeas
FROM (((Sales
        INNER JOIN Addresses
        ON      Sales.ShippingAddressID=Addresses.AddressID)
        INNER JOIN Buyers
        ON      Sales.BuyerID=Buyers.BuyerID)
        INNER JOIN Listings
        ON      Sales.ListingID=Listings.ListingID)
        INNER JOIN ShippingServices
        ON      Sales.ShippingCo=ShippingServices.SSName
WHERE (((Sales.StatusID) = 15)
    AND ((Sales.IsArchive) = 'False')
    AND ((ShippingServices.SSID) BETWEEN 0 AND 3
     OR (ShippingServices.SSID)=11
     OR (ShippingServices.SSID)=27
     OR (ShippingServices.SSID)=26))
ORDER BY Sales.BuyerID,
        Sales.OrderID;

Errors:

Msg 402, Level 16, State 1, Line 1
The data types varchar and varchar are incompatible in the boolean AND operator.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Adresses.LastName" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Adresses.LastName" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Adresses.LastName" could not be bound.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-09 : 05:24:26

In SQL Server, you concatenate text with "+", not "&".

SELECT		Sales.OrderID,
		Buyers.Email,
		COALESCE(Addresses.Company, RTRIM(LTRIM(Addresses.FirstName)) + ' ' + RTRIM(LTRIM(Adresses.LastName)), RTRIM(LTRIM(Adresses.LastName))) AS CompanyOrName,
		RTRIM(LTRIM(Addresses.FirstName)) + ' ' + RTRIM(LTRIM(Addresses.LastName)) AS Attention,
		Addresses.AddressLine1,
		Addresses.AddressLine2,
		Addresses.City ,
		Addresses.State ,
		Addresses.Zip,
		Addresses.Country ,
		COALESCE(Addresses.Phone, 'xxx-xxx-xxxx') AS Phone,
		Sales.StatusID,
		1.0 * Sales.WeightLbs * Sales.QtySold AS WeightLbs,
		Sales.Title,
		CASE Addresses.Country
			WHEN 'US' THEN NULL
			ELSE Sales.QtySold
		END AS QtySold,
		CASE Addresses.Country
			WHEN 'US' THEN NULL
			ELSE Sales.SalePrice
		END AS SalePrice,
		CASE Addresses.Country
			WHEN 'US' THEN NULL
			ELSE '02'
		END AS ReasonForExport ,
		CASE Addresses.Country
			WHEN 'US' THEN NULL
			ELSE 'USD'
		END AS CurrencyUPS,
		CASE Addresses.Country
			WHEN 'US' THEN NULL
			ELSE 'US'
		END AS CountryofOrign ,
		CASE
			WHEN Addresses.Country = 'CA' THEN 'NMB'
			WHEN Addresses.Country <> 'US' THEN 'EA'
			ELSE NULL
		END AS UnitMeas
FROM		Sales
INNER JOIN	Addresses ON Addresses.AddressID = Sales.ShippingAddressID
INNER JOIN	Buyers ON Buyers.BuyerID = Sales.BuyerID
INNER JOIN	Listings ON Listings.ListingID = Sales.ListingID
INNER JOIN	ShippingServices ON ShippingServices.SSName = Sales.ShippingCo
WHERE		Sales.StatusID = 15
		AND Sales.IsArchive = 'False'
		AND ShippingServices.SSID IN (0, 1, 2, 3, 11, 26, 27)
ORDER BY	Sales.BuyerID,
		Sales.OrderID

E 12°55'05.25"
N 56°04'39.16"

jdgti
Starting Member

5 Posts

Posted - 2008-05-09 : 05:31:30

Wow... you are very helpful Peso. Your query looks very nice compared to mine. I am still getting the multi-part errors though. I am sure it is database structure at this point. The errors I am getting are:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Adresses.LastName" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Adresses.LastName" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Adresses.LastName" could not be bound.

In my address table the LastName field is a varchar(255)... Could this be an issue?

After some googling I have found that some people are having this issue when they haven't aliased their joined tables. Is it because I am not aliasing the INNER JOINS onto the Sales and Address Tables?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-09 : 05:38:59

The one reason for this error I can think of right now is that you have conflict between SCHEMA and table name.

E 12°55'05.25"
N 56°04'39.16"

jdgti
Starting Member

5 Posts

Posted - 2008-05-09 : 05:54:13

Well, I changed the Join statement to have an alias for the Address table, and then changed all of the referencing of that address table before the join to be that alias instead of Addresses.[Field] and it seems to run fine now. Odd...

Just for clarity I will post the join and the new referencing way. Please let me know why this is happening if you know.



SELECT ...
...
COALESCE(a.Company, RTRIM(LTRIM(a.FirstName)) + ' ' + RTRIM(LTRIM(a.LastName)), RTRIM(LTRIM(a.LastName))) AS CompanyOrName,
...
FROM	Sales
INNER JOIN	Addresses a ON a.AddressID = Sales.ShippingAddressID
...

And voila, it works??? This seems wrong. But for now for some reason this is a solution. Again, if you know a way that seems less shotty, I would appreciate the help. Thanks to all who viewed the post and Peso is the man.

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-05-09 : 07:11:02

You just had a typo earlier: Adresses instead of Addresses.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources