Solved

if-statement for customer bill-to address  different than regular  address

Posted on 2008-10-10
6
152 Views
Last Modified: 2010-03-20
Hi, my custómer table has the bill-to address null, whenever the customer's bill-to address is the same as the regular adress.  please give me the complete SQL that lists the customer bill-to adress, but if the customer doesn't have a bill-to address, then I want it to default to the regular address.
my criteria:
1.  if C.BILL_TO_ADDR_1 is null, then I want to list ADDR_1
2.  if C.BILL_TO_ADDR_1 is null, then I want it to list ADDR_2, if it is not null, then I wan to list C.BILL_TO_ADDR_2
3.  if C.BILL_TO_ADDR_1 is null, then I want it to list ADDR_3, if it is not null, then I wanto list C.BILL_TO_ADDR_3
4.  if C.BILL_TO_ADDR_1 is null, then I want it to list C.CITY, if it is not null, then I wanto list C.BILL_TO_CITY
5.  if C.BILL_TO_ADDR_1 is null, then I want it to list C.STATE, if it is not null, then I wanto list C.BILL_TO_STATE
6.  if C.BILL_TO_ADDR_1 is null, then I want it to list C.COUNTRY, if it is not null, then I wanto list C.BILL_TO_COUNTRY


please give me the complete sql, for me to copy/paste. (I dont know SQL) thank you very much

SELECT C.ID AS customer_ID, C.NAME, C.ADDR_1, C.ADDR_2, C.ADDR_3, C.CITY, C.STATE, C.COUNTRY, C.BILL_TO_NAME, C.BILL_TO_ADDR_1, C.BILL_TO_ADDR_2, C.BILL_TO_CITY, C.BILL_TO_STATE, C.BILL_TO_COUNTRY

FROM SYSADM_CUSTOMER AS C

GROUP BY C.ID, C.NAME, C.ADDR_1, C.ADDR_2, C.ADDR_3, C.CITY, C.STATE, C.ZIPCODE, C.COUNTRY, C.BILL_TO_NAME, C.BILL_TO_ADDR_1, C.BILL_TO_ADDR_2, C.BILL_TO_CITY, C.BILL_TO_STATE, C.BILL_TO_COUNTRY;

Open in new window

0
Comment
Question by:gringotani
  • 3
  • 2
6 Comments
 
LVL 17

Expert Comment

by:xDJR1875
ID: 22690368
SELECT C.ID AS customer_ID, C.NAME, C.ADDR_1, C.ADDR_2, C.ADDR_3, C.CITY, C.STATE, C.COUNTRY, coalesce(C.BILL_TO_NAME,C.NAME) [BILL_TO_NAME], coalesce(C.BILL_TO_ADDR_1,C.ADDR_1) [BILL_TO_ADDR_1]
, coalesce(C.BILL_TO_ADDR_2,C.ADDR_2) [BILL_TO_ADDR_2]
, coalesce(C.BILL_TO_CITY,C.ADDR_2) [BILL_TO_CITY]
, coalesce(C.BILL_TO_STATE,C.ADDR_2) [BILL_TO_STATE]
, coalesce(C.BILL_TO_COUNTRY,C.ADDR_2) [BILL_TO_COUNTRY]
FROM SYSADM_CUSTOMER AS C
GROUP BY C.ID, C.NAME, C.ADDR_1, C.ADDR_2, C.ADDR_3, C.CITY, C.STATE, C.ZIPCODE, C.COUNTRY, C.BILL_TO_NAME, C.BILL_TO_ADDR_1, C.BILL_TO_ADDR_2, C.BILL_TO_CITY, C.BILL_TO_STATE, C.BILL_TO_COUNTRY;
0
 
LVL 17

Expert Comment

by:xDJR1875
ID: 22690380
oops, in my hurry, I forgot to update the 2nd value in the last 3 items. they should be

, coalesce(C.BILL_TO_CITY,C.CITY) [BILL_TO_CITY]
, coalesce(C.BILL_TO_STATE,C.STATE) [BILL_TO_STATE]
, coalesce(C.BILL_TO_COUNTRY,C.COUNTRY) [BILL_TO_COUNTRY]
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22690518
Although I would consider it an anomaly, your solution could provide inconsistent data of the address2 field would ever be null for the bill but not the general customer address.

So if the customers address (addr1, addr2, city, state and country) all have values, but the BILL to address has only addr1, city state and country, you would get addr2 from the customer table as if it were part of the billing address.

To get around this, it should be implemented with a case statement (or iif for access).

Make sense:

Test these results.
SELECT C.ID AS customer_ID, C.NAME, C.ADDR_1, C.ADDR_2, C.ADDR_3, C.CITY, C.STATE, C.COUNTRY

, iif(c.bill_to_name is null, c.name, c.bill_to_name) [bill_to_name]

, iif(c.bill_to_addr_1 is null, c.addr_1, c.bill_to_addr1) [bill_to_addr1]
 

, iif(c.bill_to_addr_1 is null, c.ADDR_2, c.bill_to_addr2) [bill_to_addr1]

, iif(c.bill_to_addr_1 is null, c.CITY, c.BILL_TO_CITY) [bill_to_addr1]

, iif(c.bill_to_addr_1 is null, c.STATE, c.BILL_TO_STATE) [bill_to_addr1]

, iif(c.bill_to_addr_1 is null, c.COUNTRY, c.BILL_TO_COUNTRY) [bill_to_addr1]
 

FROM SYSADM_CUSTOMER AS C

GROUP BY C.ID, C.NAME, C.ADDR_1, C.ADDR_2, C.ADDR_3, C.CITY, C.STATE, C.ZIPCODE, C.COUNTRY, C.BILL_TO_NAME, C.BILL_TO_ADDR_1, C.BILL_TO_ADDR_2, C.BILL_TO_CITY, C.BILL_TO_STATE, C.BILL_TO_COUNTRY;

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:gringotani
ID: 22691427
Brandon, I tried ur query , but got the following error messsage:Syntax error (missing operator) in query expression 'IIF(C.BILL_TO_NAME IS NULL, C.NAME,C.BILL_TO_NAME) [BILL_TO_NAME'.]

pls advise, thanks
0
 
LVL 17

Accepted Solution

by:
xDJR1875 earned 50 total points
ID: 22691562
remove the '. from teh [BILL_TO_NAME'.]  so it is [BILL_TO_NAME]
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22694100
You have this tagged as access.  Are you running it in access, or as a passthrough query to a different db engine such as sql server?
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now