December 8, 2010 at 9:26 am
Hello.
I've used case statments before, but this one is a little different. I know the code below is not the correct syntax but I'm explaining what I need to happen:
WHEN [Priority] = 1 SHOW [VendorName] and [VendorEmail]
WHEN [Priority] = 2 SHOW [VendorName] and [VendorEmail]
WHEN [Priority] = 3 SHOW [VendorName] and [VendorEmail]
GROUP BY [Part]
Okay Priority, vendorname and vendor are their own columns. I'm grouping it By [Part]. Vendors and their emails are constant. the priority is a variable depending on which [part] it is.
Thanks!
December 8, 2010 at 9:27 am
I'm not clear on what you're starting with and what you want to end up with.
- 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 8, 2010 at 9:36 am
Priority is either 1, 2, or 3 based on a couple of different things. The math for that is already done in the first view. this is the second view.
I need to get the vendor name and vendor email when the priority = 1, 2, or 3 depending on which part it is.
December 8, 2010 at 9:41 am
select vendorname, vendoremail
from dbo.MyView
where priority between 1 and 3;
Won't do it?
- 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 8, 2010 at 9:44 am
No. Because I need them listed in 3 different columns. For instance, when looking at my purchasing queue.. When I want to order a particular part I want to be able to see 3 columns. 1.) first priority vendor email. 2.) second priority venfor email. 3.) 3rd priority vendor email.. get it?
I think a pviot table would work but unfamiliar with them.
December 8, 2010 at 9:45 am
Can you post the view you're trying to query this from?
- 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 8, 2010 at 9:46 am
I need them listed in 3 different columns. For instance, when looking at my purchasing queue.. When I want to order a particular part I want to be able to see 3 columns. 1.) first priority vendor email. 2.) second priority venfor email. 3.) 3rd priority vendor email.. get it?
I think a pviot table would work but unfamiliar with them.
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')
ORDER BY dbo.invend.fpriority
This whole 'priority' is a little addition to the Purchasing Queue I've already made. Going to pull it all into an Excel Spreadsheet
December 8, 2010 at 10:03 am
You could do something like this:
WITH CTE
AS (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'))
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;
If I had the actual table definitions and such from the database, I could probably help make that better, but this should get you started.
- 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 8, 2010 at 10:30 am
I think you can also use cross apply for it.
SELECT dbo.invend.fpartno, A.FirstEmail,B.SecondEmail,C.ThirdEmail
FROM dbo.invend INNER JOIN
CROSS APPLY(SELECT top 1 dbo.apvend.fcemail as FirstEmail from dbo.apvend.fcemail AF inner join dbo.invend INV
ON AF.fvendno = INV.fvendno where INV.fpriority = '1' and INV.fvendno = dbo.invend.fvendno) A
CROSS APPLY(SELECT top 1 dbo.apvend.fcemail as SecondEmail from dbo.apvend.fcemail AF inner join dbo.invend INV
ON AF.fvendno = INV.fvendno where INV.fpriority = '2' and INV.fvendno = dbo.invend.fvendno) B
CROSS APPLY(SELECT top 1 dbo.apvend.fcemail as ThirdEmail from dbo.apvend.fcemail AF inner join dbo.invend INV
ON AF.fvendno = INV.fvendno where INV.fpriority = '3' and INV.fvendno = dbo.invend.fvendno) C
December 8, 2010 at 10:34 am
Cross apply does the same thing.
- 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 9, 2010 at 5:04 am
Well your CTE looks like it would work. I wonder why I'm getting an error that says: 'Common Table Expression' is not available in this server version. I am using:
Microsoft SQL Server Management Studio9.00.1399.00
Microsoft Analysis Services Client Tools2005.090.1399.00
Microsoft Data Access Components (MDAC)2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer7.0.5730.13
Microsoft .NET Framework2.0.50727.3615
Operating System5.2.3790
Will try some other things and post more later.
December 9, 2010 at 7:00 am
What's the SQL version? Select @@version.
- 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 9, 2010 at 7:03 am
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
I've always wondered why I couldnt use CTE ...
EDIT: I've added your MSN if you want to chat on there
December 9, 2010 at 7:25 am
edit
December 9, 2010 at 7:46 am
Move the CTE into a derived table in the From clause. See if that does what you need.
- 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 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply