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.
| Author |
Topic |
|
jcb267
Constraint Violating Yak Guru
291 Posts |
 Posted - 2009-03-20 : 14:36:50
|
| I am trying to run a query within a query so that I will have the results from one query (days) next the results from my subquery (discharges). This is what I have so far, but it will not work.--1. 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, hn.wksht_cd, hn.line_num, hn.Clmn_num, hr.fy_end_dt --Total discharges (S-3 Part I, line 12, col. 15) (select hr.prvdr_num, hn.Itm_val_num, hn.wksht_cd, hn.line_num, hn.Clmn_num, hr.fy_end_dt 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')Can anyone help with this? |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-03-20 : 15:35:24
|
| >>but it will not work.Why? wrong answers or errors or what? If error post the error.Be One with the OptimizerTG |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-03-20 : 15:39:32
|
| Ok - I see.You are attempting to use one query in the SELECT list to display multiple columns. Try moving your sub-query down into your FROM clause to make it a "derived table". Include appropriate columns to JOIN to your main tables. Then you can list whatever columns you want from your derived table in your main SELECT list.Be One with the OptimizerTG |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-03-20 : 15:47:10
|
| Actually, now I'm not sure what you're trying to do. Do you want your sub-query columns to display as additional columns or as additional rows? If rows then it looks like all you need to do is remove the subquery and change: and hn.Clmn_num = '0600'to this:and hn.Clmn_num in ('0600', '01500')because all the other criteria is exactly the same.Ok, I'm done guessing, if you can't figure it out then post your specific problem and your expected results...Be One with the OptimizerTG |
|
|
|
jcb267
Constraint Violating Yak Guru
291 Posts |
Posted - 2009-03-20 : 16:22:42
|
quote:
Originally posted by TG
Actually, now I'm not sure what you're trying to do. Do you want your sub-query columns to display as additional columns or as additional rows? If rows then it looks like all you need to do is remove the subquery and change: and hn.Clmn_num = '0600'to this:and hn.Clmn_num in ('0600', '01500')because all the other criteria is exactly the same.Ok, I'm done guessing, if you can't figure it out then post your specific problem and your expected results...Be One with the OptimizerTG
AHHHH. Thank you, that is what I am trying to do..... |
|
|
|
jcb267
Constraint Violating Yak Guru
291 Posts |
Posted - 2009-03-20 : 16:29:23
|
quote:
Originally posted by jcb267
quote:
Originally posted by TG
Actually, now I'm not sure what you're trying to do. Do you want your sub-query columns to display as additional columns or as additional rows? If rows then it looks like all you need to do is remove the subquery and change: and hn.Clmn_num = '0600'to this:and hn.Clmn_num in ('0600', '01500')because all the other criteria is exactly the same.Ok, I'm done guessing, if you can't figure it out then post your specific problem and your expected results...Be One with the OptimizerTG
AHHHH. Thank you, that is what I am trying to do.....
This is what I have now, it is not working:select hr.prvdr_num, hn.Itm_val_num, hn.wksht_cd, hn.line_num, hn.Clmn_num, hr.fy_end_dtfrom 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 in ('0600', '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')order by hr.prvdr_numI would expect to see the value of col 6 next to the value in col 15. What am I doing wrong? |
|
|
|
|
|
|
|
|
subselect