Choosing which columns are returned in Select

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 2000 Forums

Transact-SQL (2000)

Choosing which columns are returned in Select

Author

Topic

DazlerD
Starting Member

8 Posts

Posted - 2006-12-13 : 16:33:19

Hi

I am using VB.NET 2005, with SQL Server 2005. I am writting a purchase order report using Crystal Reports which has 2 fields on the report CustomerName and CustomerAddress.

How do I get my SQL stored procedure to select between which set of data to return, as there is the customers name and address and also a delivery name and address.

The sql columns are

o.Delivery -- Do we use customer or delivery name and address
o.Customer,
o.Address_1,
o.Address_2,
o.Address_3,
o.Address_4,
o.Address_5,
o.D_Customer,
o.D_Address_1,
o.D_Address_2,
o.D_Address_3,
o.D_Address_4,
o.D_Address_5,

So i want something like (in pseudo) :

IF o.Delivery = true

o.Customer as CustomerName

ISNULL(o.Address_1 + @CRLF, '') + ISNULL(o.Address_2 + @CRLF, '') + ISNULL(o.Address_3 + @CRLF, '') + ISNULL(o.Address_4 + @CRLF, '') + ISNULL(o.Address_5, '') AS CustomerAddress,

ELSE

o.D_Customer as CustomerName

ISNULL(o.D_Address_1 + @CRLF, '') + ISNULL(o.D_Address_2 + @CRLF, '') + ISNULL(o.D_Address_3 + @CRLF, '') + ISNULL(o.D_Address_4 + @CRLF, '') + ISNULL(o.D_Address_5, '') AS CustomerAddress,

END IF

I need either the customer or the delivery address being returned.

Can somebody hepl me please.

Thanks very much

Darren

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 16:38:48

[code]SELECT o.Customer as CustomerName,
CASE
WHEN o.Delivery = 1 THEN o.Customer
ELSE o.D_Customer
END CustomerName,
CASE
WHEN o.Delivery = 1 THEN ISNULL(o.Address_1 + @CRLF, '') + ISNULL(o.Address_2 + @CRLF, '') + ISNULL(o.Address_3 + @CRLF, '') + ISNULL(o.Address_4 + @CRLF, '') + ISNULL(o.Address_5, '')
ELSE ISNULL(o.D_Address_1 + @CRLF, '') + ISNULL(o.D_Address_2 + @CRLF, '') + ISNULL(o.D_Address_3 + @CRLF, '') + ISNULL(o.D_Address_4 + @CRLF, '') + ISNULL(o.D_Address_5, '')
END CustomerAddress
FROM <YourTableNameHere>
WHERE <SomeConditionColumnNameHere> = <Some value here>[/code]

Peter Larsson
Helsingborg, Sweden

DazlerD
Starting Member

8 Posts

Posted - 2006-12-13 : 17:48:47

Thank you Peter

That was just what I was looking for.

I looked at CASE but I was added what i wanted the field to be called to the end of the if and else

e.g.
CASE
WHEN o.Delivery = 1 THEN o.Customer as CustomerName
ELSE o.D_Customer as CustomerName
END,

Now I know

Cheers

Darren

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources