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

 

[PHV]

This is a report issue, not a SQL one.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lasd]

Sorry about that.. I just thought it was an SQL programming issue. I thought something like the decode function could be used to solve the problem...

if there is something in the address2
then give back address 2
if not then have it blank and skip to next line.

 

[PHV]

But the SQL returns only one row for each vendor, didn't it ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lasd]

The SQL in some cases returns Address line1, address line2, address line3, city and country but in other cases it just returns address line 1, city and country as there is no entry for the address line2 and address line3 in the database. The output looks wrong as in some cases i am getting
**************
*************
***********
**************
*************
**************
(all of the lines have an output)
but in other cases i am getting
**************
**************


**************
**************

I as wondering if it is possible to take away the third and fourth lines and have no blanks.
Thanks lasd

 

[r937]

yes, there is a way to take away the blank lines, but it must be done with whatever application program is formatting the lines

the query returns one row per vendor, and your application program is turning that one row into several lines -- so that's where you make the fix, not the sql

r937.com | rudy.ca