Select statement problem

[lasd]

Hi

I have pasted below part of the SQL statement i am using in my programme. I was just wondering if this is possible.
I am selecting the supplier name and address. In the database for some of the suppliers there is no info for C_ADDRESS_LINE2 and C_ADDRESS_LINE3. Would it be possible within the select statement to say if there is no data in this line to go to the next line, skipping the second and third address line, so that there won't be gaps for address line 2 and address line 3 in my report.

SELECT asic.vendor_name C_SUPPLIER_NAME,
asic.ADDRESS_LINE1 C_ADDRESS_LINE1,
asic.ADDRESS_LINE2 C_ADDRESS_LINE2,
asic.ADDRESS_LINE3 C_ADDRESS_LINE3,
asic.CITY C_CITY,
asic.COUNTRY C_COUNTRY,

thanks a million in advance for the help.
kindest regards
Lasd

 

[Flybridge]

Try using a series of nested CASE WHEN statements using IS NULL as the test (or ='', however the data is held). i.e. if address line 2 is null get address line 3. Similarly, for line 3 test if line 2 is null before getting the data because if it is null the line 3 data has been put into line 2 anyway.

 

[r937]

please do not cross-popst

http://www.tek-tips.com/viewthread.cfm?qid=1091930

r937.com | rudy.ca