DisplyRecord - SQL Server Forums

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

Transact-SQL (2005)

DisplyRecord

Author

Topic

krushnapanda
Starting Member

18 Posts

Posted - 2007-08-31 : 02:26:03

Hi dear,
How to display record from master and child table,
if match record not found in child table then display null
example : slno,service from mastertable
service, amount,currencytype from childtable

result is : display all service from mastertable
then check if currencytype is usd then display amount
if currencytype is not usd then convert it to usd (amount/45)

finally (after convert to usd) check if amount is greater than 5000 then display
record
if amount is not greater than 5000 then display all fields are null except the service field

Thank you

Thanks

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-08-31 : 02:38:43

Select slno,
service,
Case when amount > 5000 then amount else NULL end as Amount
From
(Select a.slno,
a.service,
Case when b.currencytype = 'USD' then b.amount else b.amount/45 end as Amount,
from mastertable a left join childtable b
on a.service = b.service) as d

--------------------------------------------------
S.Ahamed

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2007-08-31 : 02:43:04

try this.
select m.service, amount =
case currencytype when 'usd'
case when amount> 5000 then amount else null end
else case when amount/45 > 5000 then amount else null end
end from master m
left outer join child c
on m.service = c.service

krushnapanda
Starting Member

18 Posts

Posted - 2007-08-31 : 04:43:42

hi dear,
thank you
your code execute perfectly, but
my main objective is , if amount is less than 5000 then except service field all field should be display NULL, else display the data.
your query display all data when amount is less than 5000
plz suggest
thank you

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-31 : 04:49:05

[code]select service,
slno,
yak
from (
select m.service,
m.slno,
sum(case
when m.slno = 'usd' then c.amount
else c.amount / 45.0
end) as yak
from mastertable as m
left join childtable as c on c.service = m.service
group by m.service,
m.slno
) as d
where d.yak is null
or d.yak > 5000.0
order by service,
slno[/code]

E 12°55'05.25"
N 56°04'39.16"

krushnapanda
Starting Member

18 Posts

Posted - 2007-08-31 : 06:03:34

hi peso,
the query gives below error, plz suggest

Msg 207, Level 16, State 1, Line 20
Invalid column name 'd'.
Msg 207, Level 16, State 1, Line 20
Invalid column name 'd'

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources