CODEWITHSUNDEEP
sql-server-2008asp-classic
Affan
Affan

Reputation: 359

checking two sql conditions and display it in a same column

I am having sql result with two conditions..i need to print this two conditions in a same column in asp..

sql query

select P.[Port Name], CR.Name as country,CASE
when SIH.[Ship-to Code] = '' then (select C.Name  from [Customer] C where C.No_ = SIH.[Sell-to Customer No_])
end as name,
case 
when SIH.[Ship-to Code] = '' then (select C.Address  from [Customer] C where C.No_ = SIH.[Sell-to Customer No_])
end as addr, 
case
when SIH.[Ship-to Code] = '' then  (select C.[City] from [Customer] C where C.No_ = SIH.[Sell-to Customer No_])
end as city
from [Sales Invoice Header] SIH, [Port] P, [Country_Region] CR where No_ = 'PEXP1213-523' and P.Code = SIH.Port
and CR.Code = SIH.[Country of Final Destination]
union all
select P.[Port Name], CR.Name as country,
case
when SIH.[Ship-to Code] <> '' then (select C.Name from [Ship-to Address] C where C.Code = SIH.[Ship-to Code] and C.[Customer No_] = SIH.[Sell-to Customer No_])
end as name,
case
when SIH.[Ship-to Code] <> '' then  (select C.[Address] from [Ship-to Address] C where C.Code = SIH.[Ship-to Code] and C.[Customer No_] = SIH.[Sell-to Customer No_])
end as addr,
case
when SIH.[Ship-to Code] <> '' then  (select C.[City] from [Ship-to Address] C where C.Code = SIH.[Ship-to Code] and C.[Customer No_] = SIH.[Sell-to Customer No_])
end as city
 from [Sales Invoice Header] SIH, [Port] P, [Country_Region] CR where No_ = 'PEXP1213-524' and P.Code = SIH.Port
 and CR.Code = SIH.[Country of Final Destination]

Upvotes: 0

Views: 435

Answers (1)

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

select P.[Port Name], CR.Name as country, c.Name, c.Address, c.City
from [Sales Invoice Header] SIH
inner join Port P On P.Code = SIH.Port
inner join Country_Region CR On CR.Code = SIH.[Country of Final Destination]
inner join Customer C.No_ = SIH.[Sell-to Customer No_]) 
where No_ = 'PEXP1213-523' and SIH.[Ship_to Code] = ''
union all
select P.[Port Name], CR.Name as country, SIH.Name, SIH.Address,SIH.City
From [Sales Invoice Header] SIH
inner join Port P On P.Code = SIH.Port
inner join Country_Region CR On CR.Code = SIH.[Country of Final Destination]
Where No_ = 'PEXP1213-524' and SIH.[Ship_to Code] <> ''

is a lot easier to read and might help you figure out what's wrong. Use ansi join syntax, not that pre 92 stuff and for Cthulhu's sake at least pick one naming convention for your columns and tables.

Oh and you might have to prefix No_ with whatever table it's in, because I haven't a clue.

Upvotes: 2

Related Questions