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.
Showing posts with label generate. Show all posts
Showing posts with label generate. Show all posts
Sunday, March 11, 2012
Friday, March 9, 2012
Decision Statement
Hello all,
I need to generate a result based on 2 fields:
1. RelationshipID &
2. MaritalStatusID
The above fields are not embedded in the application as a
mandatory check box so some of the values maybe empty. I
need to get a list of all the members who have a
RelationshipID = 2 and if it is NULL look in
MaritalStatusID = 1 and if that is NULL, display the
result anyways. I do not need to show the values from
these fields as they are a part of my parameters (filters)
in the query. I am not a programmer and wanted to know how
would I write this in a IF THEN statement in SQL?
select Distinct pip.MemberID
from tblMemberInsurancePlan pip
Inner Join tblMember m on m.MemberID = pip.MemberID
Left Join ctblInsurancePlan ip on ip.InsurancePlanID =
pip.InsurancePlanID
Left Join ctblFamilyRelationship fr on fr.RelationshipID =
pip.RelationToSubscriber
Left Join ctblMaritalStatus ms on ms.MaritalStatusID =
m.MaritalStatusID
-- Filtering for Current insurance only
where (pip.InsurancePlanState in ('C') and
pip.InsurancePlanOrdinal = 1)
-- Filtering for Dependents only
and pip.MemberID <> pip.InsurancePlanSubscriberID
-- Filtering for 'CHILD' relationship to the subscriber
(and fr.RelationshipID = 2 or
-- Filter for Relationship = 2 or MaritalStatusID = 1
and ''''
I greatly appreciate any help in architecting the code in
this regard.
Thansk again.
Joshitry this...
select Distinct pip.MemberID
from tblMemberInsurancePlan pip
Inner Join tblMember m on m.MemberID = pip.MemberID
Left Join ctblInsurancePlan ip on ip.InsurancePlanID =
pip.InsurancePlanID
Left Join ctblFamilyRelationship fr on fr.RelationshipID =
pip.RelationToSubscriber
Left Join ctblMaritalStatus ms on ms.MaritalStatusID =
m.MaritalStatusID
-- Filtering for Current insurance only
where (pip.InsurancePlanState in ('C') and
pip.InsurancePlanOrdinal = 1)
-- Filtering for Dependents only
and pip.MemberID <> pip.InsurancePlanSubscriberID
-- Filtering for 'CHILD' relationship to the subscriber&&&-- Filter for
Relationship =2 or MaritalStatusID = 1--
or fr.RelationshipID = 2 or maritalstatusid = 1
"J. Joshi" wrote:
> Hello all,
> I need to generate a result based on 2 fields:
> 1. RelationshipID &
> 2. MaritalStatusID
> The above fields are not embedded in the application as a
> mandatory check box so some of the values maybe empty. I
> need to get a list of all the members who have a
> RelationshipID = 2 and if it is NULL look in
> MaritalStatusID = 1 and if that is NULL, display the
> result anyways. I do not need to show the values from
> these fields as they are a part of my parameters (filters)
> in the query. I am not a programmer and wanted to know how
> would I write this in a IF THEN statement in SQL?
> select Distinct pip.MemberID
> from tblMemberInsurancePlan pip
> Inner Join tblMember m on m.MemberID = pip.MemberID
> Left Join ctblInsurancePlan ip on ip.InsurancePlanID =
> pip.InsurancePlanID
> Left Join ctblFamilyRelationship fr on fr.RelationshipID =
> pip.RelationToSubscriber
> Left Join ctblMaritalStatus ms on ms.MaritalStatusID =
> m.MaritalStatusID
> -- Filtering for Current insurance only
> where (pip.InsurancePlanState in ('C') and
> pip.InsurancePlanOrdinal = 1)
> -- Filtering for Dependents only
> and pip.MemberID <> pip.InsurancePlanSubscriberID
> -- Filtering for 'CHILD' relationship to the subscriber
> (and fr.RelationshipID = 2 or
> -- Filter for Relationship = 2 or MaritalStatusID = 1
> and ''''
>
> I greatly appreciate any help in architecting the code in
> this regard.
> Thansk again.
> Joshi
>|||On Tue, 8 Feb 2005 10:41:46 -0800, J. Joshi wrote:
>I need to generate a result based on 2 fields:
>1. RelationshipID &
>2. MaritalStatusID
>The above fields are not embedded in the application as a
>mandatory check box so some of the values maybe empty. I
>need to get a list of all the members who have a
>RelationshipID = 2 and if it is NULL look in
>MaritalStatusID = 1 and if that is NULL, display the
>result anyways. I do not need to show the values from
>these fields as they are a part of my parameters (filters)
>in the query. I am not a programmer and wanted to know how
>would I write this in a IF THEN statement in SQL?
Hi Joshi,
You can do the selection in the WHERE clause of the query:
WHERE (other requirements)
AND ( RelationshipID = 2
OR (RelationshipID IS NULL AND ISNULL(MaritalStatusID, 1) = 1))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
I need to generate a result based on 2 fields:
1. RelationshipID &
2. MaritalStatusID
The above fields are not embedded in the application as a
mandatory check box so some of the values maybe empty. I
need to get a list of all the members who have a
RelationshipID = 2 and if it is NULL look in
MaritalStatusID = 1 and if that is NULL, display the
result anyways. I do not need to show the values from
these fields as they are a part of my parameters (filters)
in the query. I am not a programmer and wanted to know how
would I write this in a IF THEN statement in SQL?
select Distinct pip.MemberID
from tblMemberInsurancePlan pip
Inner Join tblMember m on m.MemberID = pip.MemberID
Left Join ctblInsurancePlan ip on ip.InsurancePlanID =
pip.InsurancePlanID
Left Join ctblFamilyRelationship fr on fr.RelationshipID =
pip.RelationToSubscriber
Left Join ctblMaritalStatus ms on ms.MaritalStatusID =
m.MaritalStatusID
-- Filtering for Current insurance only
where (pip.InsurancePlanState in ('C') and
pip.InsurancePlanOrdinal = 1)
-- Filtering for Dependents only
and pip.MemberID <> pip.InsurancePlanSubscriberID
-- Filtering for 'CHILD' relationship to the subscriber
(and fr.RelationshipID = 2 or
-- Filter for Relationship = 2 or MaritalStatusID = 1
and ''''
I greatly appreciate any help in architecting the code in
this regard.
Thansk again.
Joshitry this...
select Distinct pip.MemberID
from tblMemberInsurancePlan pip
Inner Join tblMember m on m.MemberID = pip.MemberID
Left Join ctblInsurancePlan ip on ip.InsurancePlanID =
pip.InsurancePlanID
Left Join ctblFamilyRelationship fr on fr.RelationshipID =
pip.RelationToSubscriber
Left Join ctblMaritalStatus ms on ms.MaritalStatusID =
m.MaritalStatusID
-- Filtering for Current insurance only
where (pip.InsurancePlanState in ('C') and
pip.InsurancePlanOrdinal = 1)
-- Filtering for Dependents only
and pip.MemberID <> pip.InsurancePlanSubscriberID
-- Filtering for 'CHILD' relationship to the subscriber&&&-- Filter for
Relationship =2 or MaritalStatusID = 1--
or fr.RelationshipID = 2 or maritalstatusid = 1
"J. Joshi" wrote:
> Hello all,
> I need to generate a result based on 2 fields:
> 1. RelationshipID &
> 2. MaritalStatusID
> The above fields are not embedded in the application as a
> mandatory check box so some of the values maybe empty. I
> need to get a list of all the members who have a
> RelationshipID = 2 and if it is NULL look in
> MaritalStatusID = 1 and if that is NULL, display the
> result anyways. I do not need to show the values from
> these fields as they are a part of my parameters (filters)
> in the query. I am not a programmer and wanted to know how
> would I write this in a IF THEN statement in SQL?
> select Distinct pip.MemberID
> from tblMemberInsurancePlan pip
> Inner Join tblMember m on m.MemberID = pip.MemberID
> Left Join ctblInsurancePlan ip on ip.InsurancePlanID =
> pip.InsurancePlanID
> Left Join ctblFamilyRelationship fr on fr.RelationshipID =
> pip.RelationToSubscriber
> Left Join ctblMaritalStatus ms on ms.MaritalStatusID =
> m.MaritalStatusID
> -- Filtering for Current insurance only
> where (pip.InsurancePlanState in ('C') and
> pip.InsurancePlanOrdinal = 1)
> -- Filtering for Dependents only
> and pip.MemberID <> pip.InsurancePlanSubscriberID
> -- Filtering for 'CHILD' relationship to the subscriber
> (and fr.RelationshipID = 2 or
> -- Filter for Relationship = 2 or MaritalStatusID = 1
> and ''''
>
> I greatly appreciate any help in architecting the code in
> this regard.
> Thansk again.
> Joshi
>|||On Tue, 8 Feb 2005 10:41:46 -0800, J. Joshi wrote:
>I need to generate a result based on 2 fields:
>1. RelationshipID &
>2. MaritalStatusID
>The above fields are not embedded in the application as a
>mandatory check box so some of the values maybe empty. I
>need to get a list of all the members who have a
>RelationshipID = 2 and if it is NULL look in
>MaritalStatusID = 1 and if that is NULL, display the
>result anyways. I do not need to show the values from
>these fields as they are a part of my parameters (filters)
>in the query. I am not a programmer and wanted to know how
>would I write this in a IF THEN statement in SQL?
Hi Joshi,
You can do the selection in the WHERE clause of the query:
WHERE (other requirements)
AND ( RelationshipID = 2
OR (RelationshipID IS NULL AND ISNULL(MaritalStatusID, 1) = 1))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Subscribe to:
Posts (Atom)