February 17, 2009 at 9:20 pm
When I run the select statement below in Query Analyzer, I get the desired results. However, when I try to create a View based on the same Select statment, I get a syntax error. The error is focused on my use of the "select table.field where...." statement.
Any suggestions?
SELECT orderfrom.cust_group,orderfrom.cust_subgroup,
(select partmstr.part_grp where partmstr.part_grp in ('A','AUT','B','C','CH','COM','D','F','FE','G','MER'))as 'SEC A-G',
(select partmstr.part_grp where partmstr.part_grp in ('E'))as 'SEC E',
(select partmstr.part_grp where partmstr.part_grp in ('I'))as 'FM INSUL',
(select partmstr.part_grp where partmstr.part_grp in ('R'))as 'RBR INSUL',
(select partmstr.part_grp where partmstr.part_grp in ('?'))as 'SEC H-I',
(select partmstr.part_grp where partmstr.part_grp in ('CF','WCF'))as 'CF',
(select partmstr.part_grp where partmstr.part_grp in ('PB'))as 'PEX/PBITES/STUBOUTS',
(select partmstr.part_grp where partmstr.part_grp in ('TS'))as 'TS/TS1',
(select partmstr.part_grp where partmstr.part_grp in ('BGN','MIF'))as 'BGN/MIF',
(select partmstr.part_grp where partmstr.part_grp in ('?'))as 'CRC',
(select partmstr.part_grp where partmstr.part_grp in ('MP','TUB','U','V'))as 'OTHER',
case left(invitem.invoice_numb,1) when 'T' then (sum(ext_price*-1)) else (sum(invitem.ext_price)) END AS [ext_price]
FROM invitem INNER JOIN
invhead ON invitem.invoice_numb = invhead.invoice_numb INNER JOIN
partmstr ON invitem.part_code = partmstr.part_code INNER JOIN
orderfrom ON invhead.cust_code = orderfrom.cust_code
where invitem.item_ntprice < (partmstr.part_price *.42)AND invhead.ship_date between '1/1/2009' and '1/31/2009'
group by orderfrom.cust_group,orderfrom.cust_subgroup,orderfrom.cust_code,partmstr.part_grp, INVITEM.INVOICE_NUMB
order by orderfrom.cust_group
February 18, 2009 at 8:31 am
I don't see any syntax errors in the query. And from where are you creating the view? Is it from Enterprise Manager or QA? If you are using EM, then why don't you try creating the view in QA?
CREATEVIEW dbo.vw_InvItems
AS
SELECTorderfrom.cust_group, orderfrom.cust_subgroup,
(SELECT partmstr.part_grp WHERE partmstr.part_grp in ('A','AUT','B','C','CH','COM','D','F','FE','G','MER')) AS 'SEC A-G',
(SELECT partmstr.part_grp WHERE partmstr.part_grp in ('E')) AS 'SEC E',
(SELECT partmstr.part_grp WHERE partmstr.part_grp in ('I')) AS 'FM INSUL',
(SELECT partmstr.part_grp WHERE partmstr.part_grp in ('R')) AS 'RBR INSUL',
(SELECT partmstr.part_grp WHERE partmstr.part_grp in ('?')) AS 'SEC H-I',
(SELECT partmstr.part_grp WHERE partmstr.part_grp in ('CF','WCF')) AS 'CF',
(SELECT partmstr.part_grp WHERE partmstr.part_grp in ('PB')) AS 'PEX/PBITES/STUBOUTS',
(SELECT partmstr.part_grp WHERE partmstr.part_grp in ('TS')) AS 'TS/TS1',
(SELECT partmstr.part_grp WHERE partmstr.part_grp in ('BGN','MIF')) AS 'BGN/MIF',
(SELECT partmstr.part_grp WHERE partmstr.part_grp in ('?')) AS 'CRC',
(SELECT partmstr.part_grp WHERE partmstr.part_grp in ('MP','TUB','U','V')) AS 'OTHER',
CASE LEFT(invitem.invoice_numb,1) WHEN 'T' then (SUM(ext_price*-1)) else (SUM(invitem.ext_price)) END AS [ext_price]
FROMinvitem
INNER JOIN invhead ON invitem.invoice_numb = invhead.invoice_numb
INNER JOIN partmstr ON invitem.part_code = partmstr.part_code
INNER JOIN orderfrom ON invhead.cust_code = orderfrom.cust_code
WHEREinvitem.item_ntprice < (partmstr.part_price *.42)
AND invhead.ship_date BETWEEN '1/1/2009' and '1/31/2009'
GROUP BY orderfrom.cust_group, orderfrom.cust_subgroup, orderfrom.cust_code, partmstr.part_grp,
invitem.invoice_numb
--Ramesh
February 18, 2009 at 8:59 am
Oh. Ummm...good idea.
Thanks Ramesh. That worked.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply