subquery - 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)

subquery

Author

Topic

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-20 : 15:12:11

I got this sub-select to return results. Can anyone see any obvious issues with it without seeing what the actual data looks like?

select hr.prvdr_num,
hn.Itm_val_num,

(select max(hn.Itm_val_num)
from hosp_nmrc hn INNER JOIN hosp_rpt hr
ON hn.RPT_REC_NUM = hr.RPT_REC_NUM
where (hn.wksht_cd like 'S300001%'and hn.line_num = '01200' and hn.Clmn_num = '01500')
and (hr.prvdr_num in (490011, 490017, 490069, 490094, 490059, 490136, 490041,
493301, 490032, 490119, 490093, 490046, 490044, 490007,
490057, 420004, 490024))
and (hr.fy_end_dt > '2007-12-31'))

from hosp_nmrc hn INNER JOIN hosp_rpt hr
ON hn.RPT_REC_NUM = hr.RPT_REC_NUM

where (hn.wksht_cd like 'S300001%'and hn.line_num = '01200' and hn.Clmn_num = '0600')
and (hr.prvdr_num in (490011, 490017, 490069, 490094, 490059, 490136, 490041,
493301, 490032, 490119, 490093, 490046, 490044, 490007,
490057, 420004, 490024))
and (hr.fy_end_dt > '2007-12-31')

order by hr.prvdr_num

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-20 : 15:20:20

quote:
Originally posted by jcb267

I got this sub-select to return results. Can anyone see any obvious issues with it without seeing what the actual data looks like?

select hr.prvdr_num,
hn.Itm_val_num,

(select max(hn.Itm_val_num)
from hosp_nmrc hn INNER JOIN hosp_rpt hr
ON hn.RPT_REC_NUM = hr.RPT_REC_NUM
where (hn.wksht_cd like 'S300001%'and hn.line_num = '01200' and hn.Clmn_num = '01500')
and (hr.prvdr_num in (490011, 490017, 490069, 490094, 490059, 490136, 490041,
493301, 490032, 490119, 490093, 490046, 490044, 490007,
490057, 420004, 490024))
and (hr.fy_end_dt > '2007-12-31'))

from hosp_nmrc hn INNER JOIN hosp_rpt hr
ON hn.RPT_REC_NUM = hr.RPT_REC_NUM

where (hn.wksht_cd like 'S300001%'and hn.line_num = '01200' and hn.Clmn_num = '0600')
and (hr.prvdr_num in (490011, 490017, 490069, 490094, 490059, 490136, 490041,
493301, 490032, 490119, 490093, 490046, 490044, 490007,
490057, 420004, 490024))
and (hr.fy_end_dt > '2007-12-31')

order by hr.prvdr_num

I altered this a little. Here it is:

--1. TEST SUB SELECT Average LOS from {total days/total discharges}
--Total Days (S-3 Part I, Line 12, Col. 6)
select hr.prvdr_num,
hn.Itm_val_num as valDAY,

(select hn.Itm_val_num as valDIS
from hosp_nmrc hn INNER JOIN hosp_rpt hr
ON hn.RPT_REC_NUM = hr.RPT_REC_NUM
where (hn.wksht_cd like 'S300001%'and hn.line_num = '01200' and hn.Clmn_num = '01500')
and (hr.prvdr_num in (490011, 490017, 490069, 490094, 490059, 490136, 490041,
493301, 490032, 490119, 490093, 490046, 490044, 490007,
490057, 420004, 490024))
and (hr.fy_end_dt > '2007-12-31'))

from hosp_nmrc hn INNER JOIN hosp_rpt hr
ON hn.RPT_REC_NUM = hr.RPT_REC_NUM

where (hn.wksht_cd like 'S300001%'and hn.line_num = '01200' and hn.Clmn_num = '0600')
and (hr.prvdr_num in (490011, 490017, 490069, 490094, 490059, 490136, 490041,
493301, 490032, 490119, 490093, 490046, 490044, 490007,
490057, 420004, 490024))
and (hr.fy_end_dt > '2007-12-31')

order by hr.prvdr_num

However, in the results I see this:

Prvdr_num valDay (no column name)
1 420004 154205 NULL
2 490032 166526 NULL
3 493301 52915 NULL

What am I doing wrong? Why is the column name in the subselect not being recognized?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-20 : 16:58:26

Can you format your post? align the select, from, where.. clauses please. Its much easier to comprehend then. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources