Declare in a view

30 Jun 2006 3:43 PM

ITDUDE27

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

30 Jun 2006 3:50 PM

Aaron Bertrand [SQL Server MVP]

No, you cannot declare variables in a view. I think you want a stored
procedure or a table-valued function.

Show quote

"ITDUDE27" <ITDUD***@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

30 Jun 2006 4:04 PM

Tracy McKibben

ITDUDE27 wrote:
Show quote

> 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

30 Jun 2006 5:41 PM

Omnibuzz

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/

30 Jun 2006 5:58 PM

--CELKO--

>> 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.

>> the code at the bottom generate error: Incorrect syntax near the keyword 'declare'. <<

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.