Concantenate string - 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)

Concantenate string

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-02-23 : 07:49:33

Hi

I have this query that works but I need to concantenate a string depending on weather there are any values or not in some columns.


SELECT     SUM(dbo.tbl_Order.Quantity * dbo.tbl_Order.ProductPrice) AS TotalSum, dbo.tbl_OrderInfo.CartID, dbo.tbl_OrderInfo.DateOrdered, 
                      ISNULL(dbo.tbl_OrderInfo.OrderIsFetched, 0) AS OrderIsFetched, COUNT(dbo.tbl_OrderInfo.CartID) AS TotalFiles
FROM         dbo.tbl_OrderInfo INNER JOIN
                      dbo.tbl_Order ON dbo.tbl_OrderInfo.CartID = dbo.tbl_Order.CartID INNER JOIN
                      dbo.tbl_Products ON dbo.tbl_Order.NodeID = dbo.tbl_Products.NodeId INNER JOIN
                      dbo.tbl_Login ON dbo.tbl_Order.UserUniqueID = dbo.tbl_Login.UID INNER JOIN
                      dbo.tbl_Customer ON dbo.tbl_Login.CustID = dbo.tbl_Customer.CustID
GROUP BY dbo.tbl_OrderInfo.CartID, dbo.tbl_OrderInfo.DateOrdered, dbo.tbl_OrderInfo.OrderIsFetched, dbo.tbl_Customer.CustID
HAVING      (dbo.tbl_Customer.CustID = 20)

when I try to add this..


CASE WHEN dbo.tbl_Order.Adress2 IS NULL OR [dbo.tbl_Order.Adress2] = '' THEN 0 ELSE 1 END + 
                      CASE WHEN dbo.tbl_Order.PostalCode2 IS NULL OR [dbo.tbl_Order.PostalCode2] = '' THEN 0 ELSE 1 END AS SecondaryAdress

I get a invalid column error, why is that and how do I correct this?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-23 : 07:56:47

change it to,

CASE WHEN dbo.tbl_Order.Adress2 IS NULL OR dbo.tbl_Order.[Adress2] = '' THEN 0 ELSE 1 END + 
                      CASE WHEN dbo.tbl_Order.PostalCode2 IS NULL OR dbo.tbl_Order.[PostalCode2] = '' THEN 0 ELSE 1 END AS SecondaryAdress

Hope you see the difference between what you posted and above.

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-02-23 : 08:12:34

Hi

Thanks that worked fine until I addedsome more columns like this..


SELECT     SUM(dbo.tbl_Order.Quantity * dbo.tbl_Order.ProductPrice) AS TotalSum, dbo.tbl_OrderInfo.CartID, dbo.tbl_OrderInfo.DateOrdered, 
                      ISNULL(dbo.tbl_OrderInfo.OrderIsFetched, 0) AS OrderIsFetched, COUNT(dbo.tbl_OrderInfo.CartID) AS TotalFiles, CASE WHEN dbo.tbl_Order.Att IS NULL OR
                      [Att] = '' THEN 0 ELSE 1 END + CASE WHEN dbo.tbl_Order.Company IS NULL OR
                      [Company] = '' THEN 0 ELSE 1 END + CASE WHEN dbo.tbl_Order.Adress IS NULL OR
                      [Adress] = '' THEN 0 ELSE 1 END + CASE WHEN dbo.tbl_Order.PostalCode IS NULL OR
                      [PostalCode] = '' THEN 0 ELSE 1 END + CASE WHEN dbo.tbl_Order.City IS NULL OR
                      [City] = '' THEN 0 ELSE 1 END + CASE WHEN dbo.tbl_Order.Liability IS NULL OR
                      [Liability] = '' THEN 0 ELSE 1 END + CASE WHEN dbo.tbl_Order.Adress2 IS NULL OR
                      [Adress2] = '' THEN 0 ELSE 1 END + CASE WHEN dbo.tbl_Order.PostalCode2 IS NULL OR
                      [PostalCode2] = '' THEN 0 ELSE 1 END AS SecondaryAdress
FROM         dbo.tbl_OrderInfo INNER JOIN
                      dbo.tbl_Order ON dbo.tbl_OrderInfo.CartID = dbo.tbl_Order.CartID INNER JOIN
                      dbo.tbl_Products ON dbo.tbl_Order.NodeID = dbo.tbl_Products.NodeId INNER JOIN
                      dbo.tbl_Login ON dbo.tbl_Order.UserUniqueID = dbo.tbl_Login.UID INNER JOIN
                      dbo.tbl_Customer ON dbo.tbl_Login.CustID = dbo.tbl_Customer.CustID
GROUP BY dbo.tbl_OrderInfo.CartID, dbo.tbl_OrderInfo.DateOrdered, dbo.tbl_OrderInfo.OrderIsFetched, dbo.tbl_Customer.CustID, CASE WHEN dbo.tbl_Order.Att IS NULL OR
                      [Att] = '' THEN 0 ELSE 1 END + CASE WHEN dbo.tbl_Order.Company IS NULL OR
                      [Company] = '' THEN 0 ELSE 1 END + CASE WHEN dbo.tbl_Order.Adress IS NULL OR
                      [Adress] = '' THEN 0 ELSE 1 END + CASE WHEN dbo.tbl_Order.PostalCode IS NULL OR
                      [PostalCode] = '' THEN 0 ELSE 1 END + CASE WHEN dbo.tbl_Order.City IS NULL OR
                      [City] = '' THEN 0 ELSE 1 END + CASE WHEN dbo.tbl_Order.Liability IS NULL OR
                      [Liability] = '' THEN 0 ELSE 1 END + CASE WHEN dbo.tbl_Order.Adress2 IS NULL OR
                      [Adress2] = '' THEN 0 ELSE 1 END + CASE WHEN dbo.tbl_Order.PostalCode2 IS NULL OR
                      [PostalCode2] = '' THEN 0 ELSE 1 END
HAVING      (dbo.tbl_Customer.CustID = 20)

And now I get a ambiguous column name on..

Att
Company
PostalCode
City
Liability

Do you know why I get this error?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-23 : 08:16:05

These columns exist in more than 1 tables with the same name. So specify the right alias or the table name whenever you are using these columns.
Like, change Att reference to dbo.tablename.[Att]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-23 : 08:16:07

quote:

Originally posted by magmo

Hi

Thanks that worked fine until I addedsome more columns like this..


SELECT     SUM(dbo.tbl_Order.Quantity * dbo.tbl_Order.ProductPrice) AS TotalSum, dbo.tbl_OrderInfo.CartID, dbo.tbl_OrderInfo.DateOrdered, 
                      ISNULL(dbo.tbl_OrderInfo.OrderIsFetched, 0) AS OrderIsFetched, COUNT(dbo.tbl_OrderInfo.CartID) AS TotalFiles, CASE WHEN dbo.tbl_Order.Att IS NULL OR
                      [Att] = '' THEN 0 ELSE 1 END + CASE WHEN dbo.tbl_Order.Company IS NULL OR
                      [Company] = '' THEN 0 ELSE 1 END + CASE WHEN dbo.tbl_Order.Adress IS NULL OR
                      [Adress] = '' THEN 0 ELSE 1 END + CASE WHEN dbo.tbl_Order.PostalCode IS NULL OR
                      [PostalCode] = '' THEN 0 ELSE 1 END + CASE WHEN dbo.tbl_Order.City IS NULL OR
                      [City] = '' THEN 0 ELSE 1 END + CASE WHEN dbo.tbl_Order.Liability IS NULL OR
                      [Liability] = '' THEN 0 ELSE 1 END + CASE WHEN dbo.tbl_Order.Adress2 IS NULL OR
                      [Adress2] = '' THEN 0 ELSE 1 END + CASE WHEN dbo.tbl_Order.PostalCode2 IS NULL OR
                      [PostalCode2] = '' THEN 0 ELSE 1 END AS SecondaryAdress
FROM         dbo.tbl_OrderInfo INNER JOIN
                      dbo.tbl_Order ON dbo.tbl_OrderInfo.CartID = dbo.tbl_Order.CartID INNER JOIN
                      dbo.tbl_Products ON dbo.tbl_Order.NodeID = dbo.tbl_Products.NodeId INNER JOIN
                      dbo.tbl_Login ON dbo.tbl_Order.UserUniqueID = dbo.tbl_Login.UID INNER JOIN
                      dbo.tbl_Customer ON dbo.tbl_Login.CustID = dbo.tbl_Customer.CustID
GROUP BY dbo.tbl_OrderInfo.CartID, dbo.tbl_OrderInfo.DateOrdered, dbo.tbl_OrderInfo.OrderIsFetched, dbo.tbl_Customer.CustID, CASE WHEN dbo.tbl_Order.Att IS NULL OR
                      [Att] = '' THEN 0 ELSE 1 END + CASE WHEN dbo.tbl_Order.Company IS NULL OR
                      [Company] = '' THEN 0 ELSE 1 END + CASE WHEN dbo.tbl_Order.Adress IS NULL OR
                      [Adress] = '' THEN 0 ELSE 1 END + CASE WHEN dbo.tbl_Order.PostalCode IS NULL OR
                      [PostalCode] = '' THEN 0 ELSE 1 END + CASE WHEN dbo.tbl_Order.City IS NULL OR
                      [City] = '' THEN 0 ELSE 1 END + CASE WHEN dbo.tbl_Order.Liability IS NULL OR
                      [Liability] = '' THEN 0 ELSE 1 END + CASE WHEN dbo.tbl_Order.Adress2 IS NULL OR
                      [Adress2] = '' THEN 0 ELSE 1 END + CASE WHEN dbo.tbl_Order.PostalCode2 IS NULL OR
                      [PostalCode2] = '' THEN 0 ELSE 1 END
HAVING      (dbo.tbl_Customer.CustID = 20)

And now I get a ambiguous column name on..

Att
Company
PostalCode
City
Liability

Do you know why I get this error?

Make sure you qualify all the columns with table name

Madhivanan

Failing to plan is Planning to fail

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-02-23 : 08:18:14

Ahh, yes. Of course. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources