Declare in a view

hello,
I have a quick question, can you declare a varchar within a view?
the code at the bottom generate error: Incorrect syntax near the keyword
'declare'.
CODE:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*----
--
05/25/06 - RA : starting code to query data from shamrock db
----
--*/
ALTER VIEW vw_customer_usage_bgcolor
AS
declare @.loc_east varchar (20)
declare @.loc_west varchar (20)
set @.loc_west = (select sum(inv_loc.qty_on_hand) from inv_loc INNER
JOIN inv_mast ON ( inv_mast.inv_mast_uid = inv_loc.inv_mast_uid )
WHERE ( inv_mast.item_id not like '0%' and inv_mast.item_id not like
'0%' ) AND
( inv_mast.item_id like '_____-___-___' or inv_mast.item_id like
'_____-___' ) AND
( inv_mast.delete_flag = 'N' ) AND
(inv_loc.location_id='102230' )
)
set @.loc_east = (select sum(inv_loc.qty_on_hand) from inv_loc INNER
JOIN inv_mast ON ( inv_mast.inv_mast_uid = inv_loc.inv_mast_uid )
WHERE ( inv_mast.item_id not like '0%' and inv_mast.item_id not like
'0%' ) AND
( inv_mast.item_id like '_____-___-___' or inv_mast.item_id like
'_____-___' ) AND
( inv_mast.delete_flag = 'N' ) AND
(inv_loc.location_id='100001' )
)
declare @.B_color bit
set @.B_color =
(select case when @.loc_east > @.loc_west
then 0 --EAST
else 1 --WEST
end)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GONo, you cannot declare variables in a view. I think you want a stored
procedure or a table-valued function.
"ITDUDE27" <ITDUDE27@.discussions.microsoft.com> wrote in message
news:F8A6A242-7A31-4C7B-A629-A54C97124604@.microsoft.com...
> hello,
> I have a quick question, can you declare a varchar within a view?
> the code at the bottom generate error: Incorrect syntax near the keyword
> 'declare'.
> CODE:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> /*---
--
> 05/25/06 - RA : starting code to query data from shamrock db
> ----
--*/
> ALTER VIEW vw_customer_usage_bgcolor
> AS
> declare @.loc_east varchar (20)
> declare @.loc_west varchar (20)
> set @.loc_west = (select sum(inv_loc.qty_on_hand) from inv_loc INNER
> JOIN inv_mast ON ( inv_mast.inv_mast_uid = inv_loc.inv_mast_uid )
> WHERE ( inv_mast.item_id not like '0%' and inv_mast.item_id not like
> '0%' ) AND
> ( inv_mast.item_id like '_____-___-___' or inv_mast.item_id like
> '_____-___' ) AND
> ( inv_mast.delete_flag = 'N' ) AND
> (inv_loc.location_id='102230' )
> )
> set @.loc_east = (select sum(inv_loc.qty_on_hand) from inv_loc INNER
> JOIN inv_mast ON ( inv_mast.inv_mast_uid = inv_loc.inv_mast_uid )
> WHERE ( inv_mast.item_id not like '0%' and inv_mast.item_id not like
> '0%' ) AND
> ( inv_mast.item_id like '_____-___-___' or inv_mast.item_id like
> '_____-___' ) AND
> ( inv_mast.delete_flag = 'N' ) AND
> (inv_loc.location_id='100001' )
> )
> declare @.B_color bit
> set @.B_color =
> (select case when @.loc_east > @.loc_west
> then 0 --EAST
> else 1 --WEST
> end)
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO|||ITDUDE27 wrote:
> hello,
> I have a quick question, can you declare a varchar within a view?
> the code at the bottom generate error: Incorrect syntax near the keyword
> 'declare'.
> CODE:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> /*---
--
> 05/25/06 - RA : starting code to query data from shamrock db
> ----
--*/
> ALTER VIEW vw_customer_usage_bgcolor
> AS
> declare @.loc_east varchar (20)
> declare @.loc_west varchar (20)
> set @.loc_west = (select sum(inv_loc.qty_on_hand) from inv_loc INNER
> JOIN inv_mast ON ( inv_mast.inv_mast_uid = inv_loc.inv_mast_uid )
> WHERE ( inv_mast.item_id not like '0%' and inv_mast.item_id not like
> '0%' ) AND
> ( inv_mast.item_id like '_____-___-___' or inv_mast.item_id like
> '_____-___' ) AND
> ( inv_mast.delete_flag = 'N' ) AND
> (inv_loc.location_id='102230' )
> )
> set @.loc_east = (select sum(inv_loc.qty_on_hand) from inv_loc INNER
> JOIN inv_mast ON ( inv_mast.inv_mast_uid = inv_loc.inv_mast_uid )
> WHERE ( inv_mast.item_id not like '0%' and inv_mast.item_id not like
> '0%' ) AND
> ( inv_mast.item_id like '_____-___-___' or inv_mast.item_id like
> '_____-___' ) AND
> ( inv_mast.delete_flag = 'N' ) AND
> (inv_loc.location_id='100001' )
> )
> declare @.B_color bit
> set @.B_color =
> (select case when @.loc_east > @.loc_west
> then 0 --EAST
> else 1 --WEST
> end)
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
No, you can't declare variables inside a view... Try this instead:
SELECT
CASE WHEN loc_west > loc_east THEN 1 ELSE 0 END
FROM
(
SELECT
SUM(CASE WHEN inv_loc.location_id = '102230' THEN inv_loc.qty_on_hnd
ELSE 0 END) AS loc_west,
SUM(CASE WHEN inv_loc.location_id = '100001' THEN inv_loc.qty_on_hnd
ELSE 0 END) AS loc_east
FROM inv_loc
INNER JOIN inv_mast
ON inv_loc.inv_mast_uid = inv_mast.inv_mast_uid
WHERE inv_mast.item_id NOT LIKE 0%
AND inv_mast.item_id LIKE '_____-___-___'
AND inv_mast.delete_flag = 'N'
) sums_table|||Check out in BOL index the topic "create function"
Under that you have something called
"Multi-statement Table-valued Functions"
That can handle your requirement.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||>> I have a quick question, can you declare a varchar within a view? <<
If you had ever read the first five pages of a chapter on VIEWs in any
SQL, you would know that a VIEW is a virtual table constructed from a
SELECT statement with some options.
So that did not answer your question? If you couldnot be bothered to
read a definition, wouldn't an error message a "strong hint"?
I also see that you write with bits and delete flags, just like
assembly language. Just like we did in the 1960's before RDBMS. You
also put the silly "volkwagen" suffix on view names to violate
ISO-11179 rules. All of those things are signs of really bad DDL and
DML.
Do you notice anything interesting about this predicate? Like it is
redundant?
(Inv_Mast.item_id NOT LIKE '0%'
AND
Inv_Mast.item_id NOT LIKE '0%' )
What you have posted here implies a LOT of serious errors. Stop
programming, do a full data audit and get some help from an SQL
porgrammer.