May 11, 2005 at 1:58 pm
I am trying to create a view from a SELECT statement that I've delared a variable in. I keep getting a message indicating incorrect syntax near the keyword 'DELCARE'
Is this something that I should be able to do? If not, does any one have any suggestions as to how to get around it? I have 5 queries that I need to join together the results for and one of them has an extra column, thus the need for delaring a variable in 4 of them.
Here is one of them:
CREATE VIEW opvw_projections_OpenExpress AS
DECLARE @Projtype CHAR(10)
SET @Projtype = 'N/A'
SELECT RTrim(op_o.team) AS [Team],
op_o.last_rep AS [Consultant],
RTrim(op_o.type) AS [Opportunity Type],
Convert(Char, op_o.open_date, 101) AS [Opportuntiy Create Date],
RTrim(op_o.curr_mile) AS [Milestone],
Convert(Char, op_m.start_date, 101) AS [Milestone Start Date],
(os_g.OrderTotal - os_g.ShippingCharge - os_g.Tax) AS [Projected Revenue],
os_g.OrderDate AS [Projected Milestone Completion Date],
op_o.enid AS [Group ID],
RTrim(en_e1.formal_name) AS [Organizaiton],
RTrim(en_e.formal_name) AS [Group],
RTrim(op_o.description) AS [Opportunity Name],
RTrim(op_o.description) AS [Project Name],
@Projtype AS [Project Type]
FROM op_opportunity op_o INNER JOIN en_entity en_e ON op_o.enid = en_e.enid
INNER JOIN op_milestones op_m ON op_o.opid = op_m.opid and op_o.curr_mile = op_m.mile_code
INNER JOIN os_generalinfo os_g ON op_o.opid = os_g.opid
INNER JOIN en_entity AS en_e1 ON en_e.orgid = en_e1.enid
WHERE op_o.type = 'Express' and op_o.curr_mile_status = 'In Progress'
May 11, 2005 at 2:21 pm
You can't declare a variable in a view.. this will do the trick :
SELECT RTrim(op_o.team) AS [Team],
op_o.last_rep AS [Consultant],
RTrim(op_o.type) AS [Opportunity Type],
Convert(Char, op_o.open_date, 101) AS [Opportuntiy Create Date],
RTrim(op_o.curr_mile) AS [Milestone],
Convert(Char, op_m.start_date, 101) AS [Milestone Start Date],
(os_g.OrderTotal - os_g.ShippingCharge - os_g.Tax) AS [Projected Revenue],
os_g.OrderDate AS [Projected Milestone Completion Date],
op_o.enid AS [Group ID],
RTrim(en_e1.formal_name) AS [Organizaiton],
RTrim(en_e.formal_name) AS [Group],
RTrim(op_o.description) AS [Opportunity Name],
RTrim(op_o.description) AS [Project Name],
'N/A' AS [Project Type]
FROM op_opportunity op_o INNER JOIN en_entity en_e ON op_o.enid = en_e.enid
INNER JOIN op_milestones op_m ON op_o.opid = op_m.opid and op_o.curr_mile = op_m.mile_code
INNER JOIN os_generalinfo os_g ON op_o.opid = os_g.opid
INNER JOIN en_entity AS en_e1 ON en_e.orgid = en_e1.enid
WHERE op_o.type = 'Express' and op_o.curr_mile_status = 'In Progress'
May 11, 2005 at 5:06 pm
Works great. Thanks.
May 12, 2005 at 7:25 am
HTH.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply