December 9, 2010 at 8:09 am
This part runs fine
SELECT *
FROM (SELECT
dbo.invend.fpartno,
dbo.pqsumm.fcavname AS Vendor,
dbo.invend.fpriority AS Priority,
dbo.apvend.fcemail AS Email,
dbo.pqsumm.fcavendno AS [Vend #]
FROM
dbo.invend INNER JOIN dbo.apvend ON dbo.invend.fvendno = dbo.apvend.fvendno
INNER JOIN dbo.pqsumm ON dbo.apvend.fvendno = dbo.pqsumm.fcavendno
WHERE
(dbo.invend.fpriority = '1')
OR (dbo.invend.fpriority = '2')
OR (dbo.invend.fpriority = '3')) AS CTE
Then we get to this part:
SELECT DISTINCT
fpartno,
(SELECT
FROM
CTE AS CTE2
WHERE
Priority = '1'
AND fpartno = CTE1.fpartno) AS FirstEmail,
(SELECT
FROM
CTE AS CTE3
WHERE
Priority = '2'
AND fpartno = CTE1.fpartno) AS SecondEmail,
(SELECT
FROM
CTE AS CTE4
WHERE
Priority = '3'
AND fpartno = CTE1.fpartno) AS ThirdEmail
FROM
CTE AS CTE1;
And I don't know how to editi it to make it work. Looking at the code it seems to make sense to me, just don't know why it wont execute.
Here is the error i'm recieving:
(186029 row(s) affected)
Msg 208, Level 16, State 1, Line 16
Invalid object name 'CTE'.
Msg 208, Level 16, State 1, Line 16
Invalid object name 'CTE'.
Msg 208, Level 16, State 1, Line 16
Invalid object name 'CTE'.
An explanation would be much appreciated!
December 10, 2010 at 6:13 am
You need to put the complex Select on top of the CTE, before From:
SELECT DISTINCT
fpartno,
(SELECT
FROM
CTE1 AS CTE2
WHERE
Priority = '1'
AND fpartno = CTE1.fpartno) AS FirstEmail,
(SELECT
FROM
CTE1 AS CTE3
WHERE
Priority = '2'
AND fpartno = CTE1.fpartno) AS SecondEmail,
(SELECT
FROM
CTE1 AS CTE4
WHERE
Priority = '3'
AND fpartno = CTE1.fpartno) AS ThirdEmail
FROM (SELECT
dbo.invend.fpartno,
dbo.pqsumm.fcavname AS Vendor,
dbo.invend.fpriority AS Priority,
dbo.apvend.fcemail AS Email,
dbo.pqsumm.fcavendno AS [Vend #]
FROM
dbo.invend INNER JOIN dbo.apvend ON dbo.invend.fvendno = dbo.apvend.fvendno
INNER JOIN dbo.pqsumm ON dbo.apvend.fvendno = dbo.pqsumm.fcavendno
WHERE
(dbo.invend.fpriority = '1')
OR (dbo.invend.fpriority = '2')
OR (dbo.invend.fpriority = '3')) AS CTE1;
Try that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 10, 2010 at 6:18 am
We are running on Compatability (80). I know such things as Pivot tables are not do-able at this level. What about CTE's ?. I tried this code on (80):
SELECT 'Priority' AS PriortyOrder, [1],[2],[3]
FROM
(SELECT fcpartno, fcemail, fpriority
FROM dbo.invend INNER JOIN
dbo.apvend ON dbo.invend.fvendno = dbo.apvend.fvendno) AS SourceTable
PIVOT
(
[fcemail]
FOR [fpriority IN ([1], [2], [3])
) AS PivotTable
Not even sure if that will do what want it to do.. but just to point out that I'm getting this error because of my compatability level:
Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near 'PIVOT
I cant change the level to (90) because we're running SQL Server 2000 using managment studio 2005 :hehe:
But back to your CTE.. Recieving errors still:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'CTE1'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'CTE1'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'CTE1'.
I'm guessing our compatability level is giving me syntax errors. Looking forward to your response!
Thanks
December 10, 2010 at 6:52 am
Those errors mean you didn't name the derived table "CTE1". That's all.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 10, 2010 at 6:55 am
I'm pretty positive I did. I may be mistaken though..
FROM (SELECT
dbo.invend.fpartno,
dbo.pqsumm.fcavname AS Vendor,
dbo.invend.fpriority AS Priority,
dbo.apvend.fcemail AS Email,
dbo.pqsumm.fcavendno AS [Vend #]
FROM
dbo.invend INNER JOIN dbo.apvend ON dbo.invend.fvendno = dbo.apvend.fvendno
INNER JOIN dbo.pqsumm ON dbo.apvend.fvendno = dbo.pqsumm.fcavendno
WHERE
(dbo.invend.fpriority = '1')
OR (dbo.invend.fpriority = '2')
OR (dbo.invend.fpriority = '3')) AS CTE1;
December 10, 2010 at 8:38 am
lol wow. you were right.. it was as simple as:
SELECT fcpartno, ISNULL
((SELECT TOP 1 Email
FROM jeffguerra.qPurchVendorMain
WHERE (Priority = '1') AND (Part = dbo.pqsumm.fcpartno)), '') AS [Email 1], ISNULL
((SELECT TOP 1 Email
FROM jeffguerra.qPurchVendorMain AS qPurchVendorMain_2
WHERE (Priority = '2') AND (Part = dbo.pqsumm.fcpartno)), '') AS [Email 2], ISNULL
((SELECT TOP 1 Email
FROM jeffguerra.qPurchVendorMain AS qPurchVendorMain_1
WHERE (Priority = '3') AND (Part = dbo.pqsumm.fcpartno)), '') AS [Email 3]
FROM dbo.pqsumm
Thanks for the help. this is complete now.
December 13, 2010 at 6:51 am
You're welcome. Glad I could help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply