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

Transact-SQL (2000)

Multiple Selects

Author

Topic

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2005-02-02 : 15:18:14

Hey everyone!

Could really use some super wise advise here.

This is fictional code as the real one is too long and cumbersome, but you'll get the picture. In theory, this is what I need,

SELECT COMPONENT, STARTDATE, COMPLETEDATE, STATUS, T_ID,
(SELECT COMPONENT FROM SPE_PLAN_DTL WHERE T_ID=T_ID+1)AS OP FROM SPE_PLAN_DTL WHERE STATUS <> 'COMPLETE'

The syntax parses but OP is always Null. I'm basically asking it to stop, jump down a row to get a value (next in line, so to speak), and return it to the previous row.

Is there a less evil way that actually works? Can someone lend a new direction?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-02 : 15:23:54

SELECT COMPONENT, STARTDATE, COMPLETEDATE, STATUS, T_ID,
(SELECT COMPONENT FROM SPE_PLAN_DTL WHERE T_ID=x.T_ID+1)AS OP FROM SPE_PLAN_DTL x WHERE STATUS <> 'COMPLETE'

Jay White

TimS
Posting Yak Master

198 Posts

Posted - 2005-02-02 : 15:24:11

Try this it may work

SELECT a.COMPONENT, a.STARTDATE, a.COMPLETEDATE, a.STATUS, a.T_ID,
(SELECT b.COMPONENT FROM SPE_PLAN_DTL b WHERE b.T_ID= a.T_ID+1)AS OP
FROM SPE_PLAN_DTL a
WHERE a.STATUS <> 'COMPLETE'

Tim S

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2005-02-02 : 16:05:40

Man, you guys are quick!

And accurate! She works! Thanks for the tips. Here's what I ended up using.

SELECT TOP 100 PERCENT a.COMPONENT, a.LN_NO, a.ITEMNUMBER, a.QTY, b.SCHEDULE, a.MO_STATUS, a.STARTDATE, a.COMPLETEDATE,
SUM(c.GOOD_PCS) AS GOOD_PCS, a.T_ID, (SELECT d.COMPONENT FROM SPE_PLAN_DTL d WHERE a.T_ID = d.T_ID + 1) AS OP

FROM dbo.SPE_PLANNING b RIGHT OUTER JOIN
dbo.SPE_PLAN_DTL a ON b.ROW_ID = a.MOHEADERKEY FULL OUTER JOIN
dbo.SPE_REPORT c ON a.T_ID = c.T_ID

GROUP BY b.MONUMBER, a.LN_NO, a.COMPONENT, a.ITEMNUMBER, a.QTY, b.SCHEDULE, a.MO_STATUS, a.STARTDATE, a.COMPLETEDATE, a.QTYTP,
a.RECTYPE, a.T_ID

HAVING (a.COMPONENT = 'C624') AND (a.MO_STATUS <> 'Complete') AND (a.QTYTP = 'I') AND (a.RECTYPE = 'D') ORDER BY a.STARTDATE DESC

Thanks again! Another satisfied customer!

X002548
Not Just a Number

15586 Posts

Posted - 2005-02-02 : 16:11:38

quote:
Originally posted by Girlnet

Man, you guys are quick! And accurate! She works! Thanks for the tips.

What's so suprising? This is SQL Team

Up, up, and aw....oh wait...

Brett

8-)

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2005-02-02 : 16:32:56

LOL!

Brett, how do you do it??? I just love coming into this forum.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources