Showing posts with label conditional. Show all posts
Showing posts with label conditional. Show all posts

Thursday, March 22, 2012

Conditonal SUM function, or similar conditional aggregates

Are there any conditional aggregate functions, such as SUM()?

An example would probably be the best way to describe what I'm
trying to do...

I have the following table, named Orders, with the following records:

ItemNo qty_ord paid
-- ---- --
T101B 1 199.00
T101B 1 199.00
T101B 1 199.00
T101B 1 199.00
T101B 1 199.00
T101B 1 199.00
T101B 1 199.00
T101B 1 0.00
T101B 1 0.00
T101B 1 0.00
T101B 1 0.00
Z200L 1 50.00
Z200L 2 100.00

I want to produce the following result set:

ItemNo qty_gross qty_net
-- ---- ---
T101B 11 7
Z200L 3 3

The "qty_gross" column in the result set is the sum of
total items ordered within the ItemNo grouping.
Easy enough. However, I also want a column "qty_net" that
is the sum of qty_ord but ONLY IF the amount in the
"paid" column is > 0.

I tried using the HAVING clause, but that produces a
catch 22 situation. If I say "HAVING paid > 0" then
the qty_gross column is wrong because it leaves out rows
that contain records with paid = 0 values. If I leave
out the HAVING clause, then the "qty_net" is wrong.

Any ideas?

select ItemNo, Sum(qty_ord) as qty_gross, Sum(qty_ord) as qty_net
from Orders
group by qty_ord, paid, ItemNo
having paid > 0 ???

Thanks,
RobbieOn 15 Feb 2005 06:17:24 -0800, RobbieGotNeeds@.netscape.net wrote:

>Are there any conditional aggregate functions, such as SUM()?
(snip)

Hi Robbie,

No. But you can use any expression in an aggregate function, including the
conditional CASE expression.

>An example would probably be the best way to describe what I'm
>trying to do...
(snip)
>I have the following table, named Orders, with the following records:
>ItemNo qty_ord paid
>-- ---- --
>T101B 1 199.00
>T101B 1 199.00
>T101B 1 199.00
>T101B 1 199.00
>T101B 1 199.00
>T101B 1 199.00
>T101B 1 199.00
>T101B 1 0.00
>T101B 1 0.00
>T101B 1 0.00
>T101B 1 0.00
>Z200L 1 50.00
>Z200L 2 100.00
>
>I want to produce the following result set:
>ItemNo qty_gross qty_net
>-- ---- ---
>T101B 11 7
>Z200L 3 3

SELECT ItemNo,
SUM(qty_ord) AS qty_gross,
SUM(CASE WHEN paid > 0 THEN qty_ord ELSE 0 END) AS qty_net
FROM Orders
GROUP BY ItemNo

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

conditonal counting

is there a way to have conditional counts or conditional running values?
such as counting the number of active customers verses the total number of
customers...
IIF(Fields!CustomerStatus.Value = "Active", Count(Fields!CustomerID.Value),
Nothing)
OR
IIF(Fields!CustomerStatus.Value = "Active",
RunningValue(Fields!CustomerID.Value, Count, Nothing), Nothing)
thanks,
--jimmyI haven't tried this but maybe
Sum(iif(Fields!CustomerStatus.Value="Active",1,0))
Otherwise create a computed column with the IIF,
then SUm( the computed column)
(You might also be able to use Excel's sumif function directly>)
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"jimmy" <jimmy@.discussions.microsoft.com> wrote in message
news:4E42351E-2196-42D8-AB8E-B44B34DDC1E3@.microsoft.com...
> is there a way to have conditional counts or conditional running values?
> such as counting the number of active customers verses the total number
of
> customers...
> IIF(Fields!CustomerStatus.Value = "Active",
Count(Fields!CustomerID.Value),
> Nothing)
> OR
> IIF(Fields!CustomerStatus.Value = "Active",
> RunningValue(Fields!CustomerID.Value, Count, Nothing), Nothing)
>
> thanks,
> --jimmy|||worked perfectly, thanks!
"Wayne Snyder" wrote:
> I haven't tried this but maybe
> Sum(iif(Fields!CustomerStatus.Value="Active",1,0))
> Otherwise create a computed column with the IIF,
> then SUm( the computed column)
> (You might also be able to use Excel's sumif function directly>)
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "jimmy" <jimmy@.discussions.microsoft.com> wrote in message
> news:4E42351E-2196-42D8-AB8E-B44B34DDC1E3@.microsoft.com...
> >
> > is there a way to have conditional counts or conditional running values?
> > such as counting the number of active customers verses the total number
> of
> > customers...
> >
> > IIF(Fields!CustomerStatus.Value = "Active",
> Count(Fields!CustomerID.Value),
> > Nothing)
> >
> > OR
> >
> > IIF(Fields!CustomerStatus.Value = "Active",
> > RunningValue(Fields!CustomerID.Value, Count, Nothing), Nothing)
> >
> >
> > thanks,
> > --jimmy
>
>

Tuesday, March 20, 2012

Conditionally Expand a Table

I have a table that is centered on the page. I have some conditional
hiding on the columns in the table. Is there a way to keep my table
centered when columns conditionally hide? I tried embedding the table
inside another table, but that didn't work. I also tried embedding
the table in a rectangle, but that didn't work either. I thought
someone might have a clever trick for doing this.
BillyOn Mar 12, 5:35 pm, billyburd...@.gmail.com wrote:
> I have a table that is centered on the page. I have some conditional
> hiding on the columns in the table. Is there a way to keep my table
> centered when columns conditionally hide? I tried embedding the table
> inside another table, but that didn't work. I also tried embedding
> the table in a rectangle, but that didn't work either. I thought
> someone might have a clever trick for doing this.
> Billy
As far as I know, there is not a way to handle this. If you haven't
already, you could try putting the table in a list control and set
'fit table in same page if possible' -or- you could try controlling
the resultset in the stored procedure or query used to source the
report. Sorry I could not be of more assistance.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||On Mar 12, 5:35 pm, billyburd...@.gmail.com wrote:
> I have a table that is centered on the page. I have some conditional
> hiding on the columns in the table. Is there a way to keep my table
> centered when columns conditionally hide? I tried embedding the table
> inside another table, but that didn't work. I also tried embedding
> the table in a rectangle, but that didn't work either. I thought
> someone might have a clever trick for doing this.
> Billy
The only other things I can think of are:
- Verify that 'Fit table on one page if possible' is selected in the
table properties.
- Put the table in a list control and verify that 'Fit table on one
page if possible' is selected for the list control properties.
- Try to control the table flow from the stored procedure or query
that is sourcing the report.
Sorry I could not be of more assistance.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||On Mar 12, 5:35 pm, billyburd...@.gmail.com wrote:
> I have a table that is centered on the page. I have some conditional
> hiding on the columns in the table. Is there a way to keep my table
> centered when columns conditionally hide? I tried embedding the table
> inside another table, but that didn't work. I also tried embedding
> the table in a rectangle, but that didn't work either. I thought
> someone might have a clever trick for doing this.
> Billy
The only other things I can think of are:
- Verify that 'Fit table on one page if possible' is selected in the
table properties.
- Put the table in a list control and verify that 'Fit table on one
page if possible' is selected for the list control properties.
- Try to control the table flow from the stored procedure or query
that is sourcing the report.
Sorry I could not be of more assistance.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||On Mar 12, 5:35 pm, billyburd...@.gmail.com wrote:
> I have a table that is centered on the page. I have some conditional
> hiding on the columns in the table. Is there a way to keep my table
> centered when columns conditionally hide? I tried embedding the table
> inside another table, but that didn't work. I also tried embedding
> the table in a rectangle, but that didn't work either. I thought
> someone might have a clever trick for doing this.
> Billy
The only other things I can think of are:
- Verify that 'Fit table on one page if possible' is selected in the
table properties.
- Put the table in a list control and verify that 'Fit table on one
page if possible' is selected for the list control properties.
- Try to control the table flow from the stored procedure or query
that is sourcing the report.
Sorry I could not be of more assistance.
Regards,
Enrique Martinez
Sr. SQL Server Developer

Conditional/Dynamic Where Clause

Hi all,
I want to construct a dynamic where clause depending on the
value of the parameter of the stored procedure.
Here' s a snippet of the Stored procedure that I want to write
Create Procedure mySP
@.FilterBy --declare parameter
As
Select * from Registration
where
--This is where i need help.
The values for @.FilterBy can be only either A or B or C or D.
If value of @.FilterBy is A then I would like the where clause to be :
Where Registration.A = 'Something'
If value of @.FilterBy is B then I would like the where clause to be :
Where Registration.B = 'Something'
If value of @.FilterBy is C then I would like the where clause to be :
Where Registration.C = 'Something'
If value of @.FilterBy is D then I would like the where clause to be :
Where Registration.D = 'Something'
Is this possible? If yes, how? I will greatly appreciate any help.
TIA,
Mounil.
Give this a try. It should be what you are looking for I think.
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
)
AS
SET NOCOUNT ON
DECLARE @.SqlDynvarchar(4000)
SELECT @.SqlDyn = 'SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.A = "Something"'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B = "Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C = "Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D = "Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"Mounilk" wrote:

> Hi all,
> I want to construct a dynamic where clause depending on the
> value of the parameter of the stored procedure.
> Here' s a snippet of the Stored procedure that I want to write
> Create Procedure mySP
> @.FilterBy --declare parameter
> As
> Select * from Registration
> where
> --This is where i need help.
>
> The values for @.FilterBy can be only either A or B or C or D.
> If value of @.FilterBy is A then I would like the where clause to be :
> Where Registration.A = 'Something'
> If value of @.FilterBy is B then I would like the where clause to be :
> Where Registration.B = 'Something'
> If value of @.FilterBy is C then I would like the where clause to be :
> Where Registration.C = 'Something'
> If value of @.FilterBy is D then I would like the where clause to be :
> Where Registration.D = 'Something'
> Is this possible? If yes, how? I will greatly appreciate any help.
> TIA,
> Mounil.
>
|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn =
'Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn?
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50)))'+ --Using
SP's Parameter in @.SqlDyn?
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B =
"Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C =
"Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D =
"Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.
|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn =
'Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn?
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50)))'+ --Using
SP's Parameter in @.SqlDyn?
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B =
"Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C =
"Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D =
"Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.

Conditional/Dynamic Where Clause

Hi all,
I want to construct a dynamic where clause depending on the
value of the parameter of the stored procedure.
Here' s a snippet of the Stored procedure that I want to write
Create Procedure mySP
@.FilterBy --declare parameter
As
Select * from Registration
where
--This is where i need help.
The values for @.FilterBy can be only either A or B or C or D.
If value of @.FilterBy is A then I would like the where clause to be :
Where Registration.A = 'Something'
If value of @.FilterBy is B then I would like the where clause to be :
Where Registration.B = 'Something'
If value of @.FilterBy is C then I would like the where clause to be :
Where Registration.C = 'Something'
If value of @.FilterBy is D then I would like the where clause to be :
Where Registration.D = 'Something'
Is this possible? If yes, how? I will greatly appreciate any help.
TIA,
Mounil.Give this a try. It should be what you are looking for I think.
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn = 'SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.A = "Something"'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B = "Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C = "Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D = "Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"Mounilk" wrote:
> Hi all,
> I want to construct a dynamic where clause depending on the
> value of the parameter of the stored procedure.
> Here' s a snippet of the Stored procedure that I want to write
> Create Procedure mySP
> @.FilterBy --declare parameter
> As
> Select * from Registration
> where
> --This is where i need help.
>
> The values for @.FilterBy can be only either A or B or C or D.
> If value of @.FilterBy is A then I would like the where clause to be :
> Where Registration.A = 'Something'
> If value of @.FilterBy is B then I would like the where clause to be :
> Where Registration.B = 'Something'
> If value of @.FilterBy is C then I would like the where clause to be :
> Where Registration.C = 'Something'
> If value of @.FilterBy is D then I would like the where clause to be :
> Where Registration.D = 'Something'
> Is this possible? If yes, how? I will greatly appreciate any help.
> TIA,
> Mounil.
>|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn ='Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn'
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50)))'+ --Using
SP's Parameter in @.SqlDyn'
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B ="Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C ="Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D ="Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn ='Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn'
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50)))'+ --Using
SP's Parameter in @.SqlDyn'
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B ="Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C ="Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D ="Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.

Conditional/Dynamic Where Clause

Hi all,
I want to construct a dynamic where clause depending on the
value of the parameter of the stored procedure.
Here' s a snippet of the Stored procedure that I want to write
Create Procedure mySP
@.FilterBy --declare parameter
As
Select * from Registration
where
--This is where i need help.
The values for @.FilterBy can be only either A or B or C or D.
If value of @.FilterBy is A then I would like the where clause to be :
Where Registration.A = 'Something'
If value of @.FilterBy is B then I would like the where clause to be :
Where Registration.B = 'Something'
If value of @.FilterBy is C then I would like the where clause to be :
Where Registration.C = 'Something'
If value of @.FilterBy is D then I would like the where clause to be :
Where Registration.D = 'Something'
Is this possible? If yes, how? I will greatly appreciate any help.
TIA,
Mounil.Give this a try. It should be what you are looking for I think.
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn = 'SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.A = "Something"'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B = "Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C = "Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D = "Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"Mounilk" wrote:

> Hi all,
> I want to construct a dynamic where clause depending on the
> value of the parameter of the stored procedure.
> Here' s a snippet of the Stored procedure that I want to write
> Create Procedure mySP
> @.FilterBy --declare parameter
> As
> Select * from Registration
> where
> --This is where i need help.
>
> The values for @.FilterBy can be only either A or B or C or D.
> If value of @.FilterBy is A then I would like the where clause to be :
> Where Registration.A = 'Something'
> If value of @.FilterBy is B then I would like the where clause to be :
> Where Registration.B = 'Something'
> If value of @.FilterBy is C then I would like the where clause to be :
> Where Registration.C = 'Something'
> If value of @.FilterBy is D then I would like the where clause to be :
> Where Registration.D = 'Something'
> Is this possible? If yes, how? I will greatly appreciate any help.
> TIA,
> Mounil.
>|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn =
'Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn'
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50))
)'+ --Using
SP's Parameter in @.SqlDyn'
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B =
"Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C =
"Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D =
"Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn =
'Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn'
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50))
)'+ --Using
SP's Parameter in @.SqlDyn'
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B =
"Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C =
"Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D =
"Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.sqlsql

Conditional XQuery: How to select a desirable node when it occurs multiple times

I would very much appreicate if someone could help me with the following

Return CountryCodes node based on the following rules:

(1) Ignore <AlternativeState> completely

(2) When <CurrentEvent>MarketSize</CurrentEvent> get CountryCodes from <MarketSize> node only

(3) When <CurrentEvent>MarketShare</CurrentEvent> get CountryCodes from <OtherEvents> node only

(4) When <CurrentEvent> doesn't exist then xml would have only one CountryCodes; get that node

I have come up with the following so far which is far from what is desirable

SELECT UsageID, Countries.Code.query('

for $CountryCode in .

return data($CountryCode)

') AS CountryCodes

FROM UsageAnalysis

CROSS APPLY xmlState.nodes('//*[not(self::AlternativeState)]/*/CountryCodes') AS Countries(Code)

GO

Please keep in mind xml comes from a table column.

The following are three possible simplified cases

Case 1

<State>

<StatsState>

<CurrentState>

<MarketSize>

<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes>

</MarketSize>

<CurrentEvent>MarketSize</CurrentEvent>

</CurrentState>

</StatsState>

</State>

Case 2

<State>

<DefinitionState>

<CountryCodes>BR</CountryCodes>

</DefinitionState>

</State>

Case 3

<State>

<StatsState>

<CurrentState>

<OtherEvents>

<CountryCodes>FR</CountryCodes>

<AlternativeState>

<OtherEvents>

<CountryCodes>FR</CountryCodes>

</OtherEvents>

<MarketSize>

<CountryCodes>FR,FP,FG</CountryCodes>

</MarketSize>

<CurrentEvent>MarketShare</CurrentEvent>

</AlternativeState>

</OtherEvents>

<CurrentEvent>MarketShare</CurrentEvent>

<MarketSize>

<CountryCodes>,FR</CountryCodes>

</MarketSize>

</CurrentState>

</StatsState>

</State>

Hope this solve your problem:

Code Snippet

declare @.x xml

set @.x =

'<State>

<StatsState>

<CurrentState>

<MarketSize>

<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes>

</MarketSize>

<CurrentEvent>MarketSize</CurrentEvent>

</CurrentState>

</StatsState>

</State>

<State>

<DefinitionState>

<CountryCodes>BR</CountryCodes>

</DefinitionState>

</State>

<State>

<StatsState>

<CurrentState>

<OtherEvents>

<CountryCodes>FR</CountryCodes>

<AlternativeState>

<OtherEvents>

<CountryCodes>FR</CountryCodes>

</OtherEvents>

<MarketSize>

<CountryCodes>FR,FP,FG</CountryCodes>

</MarketSize>

<CurrentEvent>MarketShare</CurrentEvent>

</AlternativeState>

</OtherEvents>

<CurrentEvent>MarketShare</CurrentEvent>

<MarketSize>

<CountryCodes>,FR</CountryCodes>

</MarketSize>

</CurrentState>

</StatsState>

</State>'

select @.x.query('

for $s in /State

return

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketSize")

then $s/StatsState/CurrentState/MarketSize/CountryCodes

else (

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketShare")

then $s/StatsState/CurrentState/OtherEvents/CountryCodes

else $s//CountryCodes

)

')

|||

Should this also be returned?

<CountryCodes>,FR</CountryCodes>

Please excuse me because I am rather new to the XML sector. I am confused by the question and the answer. I coded this up:

declare @.x xml
set @.x =
'<State>
<StatsState>
<CurrentState>
<MarketSize>
<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes>
</MarketSize>
<CurrentEvent>MarketSize</CurrentEvent>
</CurrentState>
</StatsState>
</State>
<State>
<DefinitionState>
<CountryCodes>BR</CountryCodes>
</DefinitionState>
</State>
<State>
<StatsState>
<CurrentState>
<OtherEvents>
<CountryCodes>FR</CountryCodes>
<AlternativeState>
<OtherEvents>
<CountryCodes>FR</CountryCodes>
</OtherEvents>
<MarketSize>
<CountryCodes>FR,FP,FG</CountryCodes>
</MarketSize>
<CurrentEvent>MarketShare</CurrentEvent>
</AlternativeState>
</OtherEvents>
<CurrentEvent>MarketShare</CurrentEvent>
<MarketSize>
<CountryCodes>,FR</CountryCodes>
</MarketSize>
</CurrentState>
</StatsState>
</State>'


select coalesce (
nullif(t.c.query('./StatsState/CurrentState/MarketSize/CountryCodes').value('.','varchar(20)'), ''),
nullif(t.c.query('./StatsState/CurrentState/OtherEvents/CountryCodes').value('.','varchar(20)'),''),
t.c.query('./DefinitionState/CountryCodes').value('.','varchar(20)')

)
as CountryCodes
from @.x.nodes('State') t(c)

and received this result:

/*
CountryCodes
--
KT,LC,VG,SU,TT,UY,VE
BR
,FR
*/

Do the correct results need to include the markup such that the results should look more like this:

/*
CountryCodes
--
<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes><CountryCodes>BR</CountryCodes><CountryCodes>,FR</CountryCodes>
*/

(Trying to learn what is going on -- and I'm a bit confused.)

I appreciate the help.

|||

Jinghao, thanks very much. Your provided snippet does exactly what I have been trying to achieve. The only change I decided to introduce is to use data() so that I could get the scalar values for country codes as follows:

select @.x.query('

for $s in /State

return

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketSize")

then data($s/StatsState/CurrentState/MarketSize/CountryCodes)

else (

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketShare")

then data($s/StatsState/CurrentState/OtherEvents/CountryCodes)

else data($s//CountryCodes)

)

')

/*

Result set from your query:

<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes>

<CountryCodes>BR</CountryCodes>

<CountryCodes>FR</CountryCodes>

Results after introducing data()

KT,LC,VG,SU,TT,UY,VE BR FR

*/

Now I could use a function call to return a list of country codes.

Thanks again for your help.

|||

Kent,

I must say that it took me a while to fully understand the solution you suggested by clever use of COALESCE. It did exactly what I was trying to achieve. i.e get a list of selected country codes.

/*

KT,LC,VG,SU,TT,UY,VE

BR

FR

*/

I just wanted to have a list of countries, without having any markups. i.e. just the scalar values of <countryCodes>

Your response has shown me another use of COALESCE function and I very much appreciate your help

conditional widths

I have a report with columns which the user may optionally turn on. when
these columns are not displayed (hidden=true), the report fits nicely in
legal sized paper, and when the columns are displayed, it is typically
because the report will be exported to Excel.
here's my quandary: if I assign those optional columns a particular width,
the report exceeds the paper size (even though the columns are hidden),
causing extra pages to print (blank). the solution is to make those columns
0 width (or as close as Reporting Services will allow)... however, when
exporting to Excel, the columns have to be manually expanded. grr...
the answer would be conditional width sizes i.e. if a report parameter
specifies display of the columns then use one width, otherwise make the width
0... but the width property doesn't seem to accept an expression.
has anyone out there faced this same problem? any ideas of how I can
address it?
thx - ekkisI am having a similar problem, I want conditional widths so that I can use
the same report for different years. I have information broken down by weeks
and want the parameter to be the year, based on that parameter I need the
month name above the week names to change in size. Haven't figures it out
yet...

Conditional Where wildcard problem

Hi,

I have a problem using the LIKE operator in a stored procedure. I have simplified the script so that it runs in query analyser and still have the same problem. The script is:

DECLARE @.FirstName varchar (50)

SELECT @.FirstName = 'B%'

SELECT * FROM PhoneList
WHERE PhoneList.FirstName LIKE CASE @.FirstName WHEN '' THEN PhoneList.FirstName ELSE @.FirstName END

This code produces no rows in the result. However if I change the second line to:
SELECT @.FirstName = 'Ben'
Then I get all of the rows with 'Ben' as the first name. If I change it to:
SELECT @.FirstName = 'Be%'
Then I get all of the rows with three character first names beginning with 'Be'. If I change it to:
SELECT @.FirstName = 'B%%'
Then I get all of the three character first names beginning with 'B'.

I need the conditional where so that if an empty string is passed it returns every row, which works fine as it is.

The % wildcard appears to be operating the same way as the _ wildcard. Has anyone seen this before?

This is SQL Server 2k SP3 on Win2003 server.

thanks
BenHi,

maybe you could try this:

DECLARE @.FirstName varchar (50)

SELECT @.FirstName = 'B%'

SELECT * FROM PhoneList
WHERE PhoneList.FirstName LIKE @.FirstName + '%'

If @.FIrstName is an empty string the statement should return all data.

;)

Conditional Where using a Parameter

How do I construct a select with a conditional where:

If DefaultWH is not blank I want to add a "AND part".

SELECT DISTINCT Name
FROM Warehouse
WHERE (Cono = @.Company)

CASE WHEN

@.DefaultWH' <> ' ' THEN

AND (@.DefaultWH = whseid)

END

Here is something that I have done. Rather than use a blank, set it to null and use an isnull.

Example:

If @.DefaultWh = ''
Begin
Set @.DefaultWh = NULL
End

Select Distinct Name
from Warehouse
Where (Cono = @.Company)
and isnull(@.DefaultWH, WhseID) = WhseID

Hope that helps!

BobP

|||

Try :

SELECT DISTINCT Name
FROM Warehouse
WHERE ((Cono = @.Company) and (@.DefaultWH='')) or ((Cono = @.Company) and (@.DefaultWH=whseid))

Hope it work's...

|||

Neith of the examples worked. I need to have the query run in 1 of 2 formats

Parameter 1 = select company

Parameter 2 = select DefaultWH

Parameter 3 =

SELECT DISTINCT Name
FROM Warehouse
WHERE (Cono = @.Company) < === from Parameter 1

or

SELECT DISTINCT Name
FROM Warehouse
WHERE (Cono = @.Company) AND (@.DefaultWH = whseid) <===From Parameter 1 and 2

I need to be able to drop or include the "AND part" based on the value of Parameter 2

Parameter 1 runs and I pick a company#

Parameter 2 runs and it returns DefafultWH as a blank (access to all warehouses) or a value (limited to that warehouse) based on the Company# enter for Parameter 1.

If Parameter 2 is blank then run Parameter 3 -- Select without the AND

If Parameter 2 is not blank then run Parameter 3 -- Select with the AND part

It just cant be that hard to to. I just have not been that this that long to figure it out.

|||

My example would work in both scenarios.

This is what it does:

The ISNULL syntax returns the value of @.DefaultWH if it is NOT null, and the value of WhseID if it is. So, If parameter2 is blank, change it to null, and the AND clause will look like this:

Parameter1 = 'ABC Company'

Parameter2 = blank (Unselected)

Select Distinct Name
from Warehouse
Where Cono = 'ABC Company'
and WhseID = WhseID

So this effectively removes the AND. The only caveat to this: If WhseID can be NULL, then you would need to add one more statement.

Select Distinct Name
From Warehouse
Where Cono = @.Company
And (WhseID = isnull(@.DefaultWH,WhseID) or WhseID is null)

You have to add the "WhseID is null" clause because null does not "=" null, it only IS null.

Feel free to email me at bobp1339
at
yahoo

BobP

|||

Where does the code go

If I insert on the generic query screeen is says that @.DefaultWH is not declared or defined

If I insert the following into the generic code screen a

=Code.GetSQL()

and then insert the code in the Report code section I get errors there to.

Conditional WHERE statement?

Hi all,

I have one for all the blackbelters out there: is there a way i can
make a stored procedure where i can control the where statement with
variables? I have to do some complex transformations to get compose a
fact table for MSAS and there a a lot of similarities between the
queries and a few differences because of different account methods
etc. (booking in starting date, booking stuff on order entry dates
etc) I want to put a combination of different rules in different
members of dimensions.

An example of what i mean:

CREATE STORED PROCEDURE dbo.FILLFACT (@.PAR1, @.PAR2)
AS
INSERT INTO FactTable (blah blah)

SELECT
IF @.PAR1 = 'OrderDate'
SourceView.Orderdate
ELSE
SourceView.StartDate
,
etc etc...

FROM
SourceView

WHERE
IF @.PAR2 = 'WholeTable'
1=1
IF @.PAR2 = 'Incomplete'
EndDate IS NULL OR EXIST (SELECT * FROM Exceptions WHERE
..., etc)

This way i could fill my fact table with

EXEC dbo.FillFact 'beginDate','Wholetable'
EXEC dbo.FillFact 'begindate', 'Rulebook1'
EXEC dbo.FillFact 'BeginDate', 'Exceptions'
etcetera.

This is not an actual SQL script i use, just an example of what i'm
talking about. Or maybe i could pass the where statement entirley as a
variable? But i can't use SET @.PAR1 = 'EndDate IS NULL' and then use
WHERE @.PAR1 can I?

I hope i'm making sense. Does anyone know if this is possible? Right
now i have a procedure that is composed of a dozen of sql scripts that
are mostly the same, but i have to copy it for every combination of
situations and then, of course, new stuff has to be added on 12
different places. Again and again.

Any thoughts?

TIA,

Gert-Jan van der Kamp[posted and mailed, please reply in news]

G.J. v.d. Kamp (gjvdkamp@.hotmail.com) writes:
> I have one for all the blackbelters out there: is there a way i can
> make a stored procedure where i can control the where statement with
> variables? I have to do some complex transformations to get compose a
> fact table for MSAS and there a a lot of similarities between the
> queries and a few differences because of different account methods
> etc. (booking in starting date, booking stuff on order entry dates
> etc) I want to put a combination of different rules in different
> members of dimensions.

I believe that my article on dynamic search condition should give
you some ideas to work from. Look at
http://www.sommarskog.se/dyn-search.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||That's exectly what i mean, thanx!

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns95F2F1FFAE1DEYazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> G.J. v.d. Kamp (gjvdkamp@.hotmail.com) writes:
> > I have one for all the blackbelters out there: is there a way i can
> > make a stored procedure where i can control the where statement with
> > variables? I have to do some complex transformations to get compose a
> > fact table for MSAS and there a a lot of similarities between the
> > queries and a few differences because of different account methods
> > etc. (booking in starting date, booking stuff on order entry dates
> > etc) I want to put a combination of different rules in different
> > members of dimensions.
> I believe that my article on dynamic search condition should give
> you some ideas to work from. Look at
> http://www.sommarskog.se/dyn-search.html.sqlsql

conditional where statement

I have a stored procedure that performs a search function with params:

@.username nvarchar(50)
@.country nvarchar(50)
and like 10 more.

A user may provide values for these params optionally.
So when the @.username var is left blank, there should be no filtering on the username field (every field should be selected regardless of the username)
Currently my statement is:

select username,country from myUsers where
username=@.username andcountry=@.country

With this statement when a user provides no value for username the username field selects on ''m which returns ofcourse nothing...

What can I do to solve this?

Thanks!

SELECTFROM YourTableWHERE (@.usernameISNULL OR UserName = @.username )AND (@.countryISNULL OR Country = @.country )
|||

Thanks, but in this case the username field would not be ignored.
If someone has filled in a username, say "peter", but the webvisitor would not want to search on any username, the statement would be:

SELECT
FROM YourTable
WHERE (@.usernameISNULL OR UserName = '' )

In this case the user with name "peter" would not be found. If a webvisitor does NOT provide a username, I want to return all rows regardless of the value in the username field...

Im just hoping I've explained myself clearly now :)

Thanks!

|||

use WHERE (UserName = COALESCE(@.UserName, UserName)) AND (Country = COALESCE(@.Country, Country)) AND etc.

COALESCE (or ISNULL if you prefer) will return the first value in the parameter list that is not null, so if you pass a NULL value for, say, @.UserName, that part of the WHERE clause will resolve to "WHERE UserName = UserName", which of course, is always true.

|||

Peter Smith:

If someone has filled in a username, say "peter", but the webvisitor would not want to search on any username

Can you explain what you mean by that? If there is a value in @.username, it will be searched against, else ignored. If there is a value provided and it does not exist in the table, obviously nothing will be returned. Incase the query doesnt work as expected, please provide some sample data, and sample scenarios and their expected outputs.

|||

Dinakar Nethi provided an excellent query for your issue. The key to implement is:

You need to set your input parameter to default NULL first.

@.UserName NVarchar(50) = NULL,

@.Country NVarchar(50) = NULL

|||

mmm, I see (now). I tested your query and it works :)
Thanks!

conditional WHERE sections.

Hi I have a query where I am reading in a bunch of veriables and only want to
run parts of the where clause if the variables are valid, (will be input in a
stored procedure)
I tried
WHERE
if @.var1 IS NULL
BEGIN
{
tablename.fieldname >= @.var1
}
END
if @.var2 IS NULL
BEGIN
{
AND tablename.fieldname >= @.var2
}
END
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation occures
thanks.
Paul G
Software engineer.
Paul
Create a dynamic SQL statement and build the where clause. Then execute the
SQL statement:
declare @.sql varchar(8000)
set @.sql = 'select * from table where '
If @.var1 is null
set @.sql = @.sql + 'condition 1'
else
set @.sql = @.sql + 'condition 2'
exec @.sql
"Paul" wrote:

> Hi I have a query where I am reading in a bunch of veriables and only want to
> run parts of the where clause if the variables are valid, (will be input in a
> stored procedure)
> I tried
> WHERE
> if @.var1 IS NULL
> BEGIN
> {
> tablename.fieldname >= @.var1
> }
> END
> if @.var2 IS NULL
> BEGIN
> {
> AND tablename.fieldname >= @.var2
> }
> END
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violation occures
> thanks.
>
> --
> Paul G
> Software engineer.
|||There are several approaches for handling such requirements. Some of the
popular ones are detailed at: http://www.sommarskog.se/dyn-search.html
Anith
|||Hi thanks for the information. Figured there may be several ways to the
solution.
"Anith Sen" wrote:

> There are several approaches for handling such requirements. Some of the
> popular ones are detailed at: http://www.sommarskog.se/dyn-search.html
> --
> Anith
>
>
|||Ok looks like the dynamic SQL statement should work for what I am trying to
do. Thanks.
"Bruce" wrote:
[vbcol=seagreen]
> Paul
> Create a dynamic SQL statement and build the where clause. Then execute the
> SQL statement:
> declare @.sql varchar(8000)
> set @.sql = 'select * from table where '
> If @.var1 is null
> set @.sql = @.sql + 'condition 1'
> else
> set @.sql = @.sql + 'condition 2'
> exec @.sql
>
> "Paul" wrote:

Conditional where clause, depending on parameter

I am looking for a way to create a query that, depending on the value of a
parameter, builds the correct where-clause. This should be usable in a store
d
procedure.
Example:
parameter @.ShowArchived
select x, y, z from table_zyx WHERE ...
if @.ShowArchived > 0 --> WHERE archive=1
else --> WHERE archive=0 OR archive is null
All help is more than welcome!Hmm perhaps something like this:
WHERE isnull(archive,0) = case when @.ShowArchived > 0 then 1 else 0 end
it isn't optimal but you can change it if it works for you.
MC
"Vicky" <Vicky@.discussions.microsoft.com> wrote in message
news:F249666A-1E70-4C13-B48E-ED50B064771C@.microsoft.com...
>I am looking for a way to create a query that, depending on the value of a
> parameter, builds the correct where-clause. This should be usable in a
> stored
> procedure.
> Example:
> parameter @.ShowArchived
> select x, y, z from table_zyx WHERE ...
> if @.ShowArchived > 0 --> WHERE archive=1
> else --> WHERE archive=0 OR archive is null
> All help is more than welcome!|||http://www.sommarskog.se/dyn-search.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Vicky" <Vicky@.discussions.microsoft.com> wrote in message
news:F249666A-1E70-4C13-B48E-ED50B064771C@.microsoft.com...
>I am looking for a way to create a query that, depending on the value of a
> parameter, builds the correct where-clause. This should be usable in a
> stored
> procedure.
> Example:
> parameter @.ShowArchived
> select x, y, z from table_zyx WHERE ...
> if @.ShowArchived > 0 --> WHERE archive=1
> else --> WHERE archive=0 OR archive is null
> All help is more than welcome!|||you can use dynamic sql.
potentially a "simpler" to understand solution, and sometimes faster to
run is to have different select statements separted by if statements
stuffed into a stored procedure.

Conditional where clause, depending on parameter

I am looking for a way to create a query that, depending on the value of a
parameter, builds the correct where-clause. This should be usable in a stored
procedure.
Example:
parameter @.ShowArchived
select x, y, z from table_zyx WHERE ...
if @.ShowArchived > 0 --> WHERE archive=1
else --> WHERE archive=0 OR archive is null
All help is more than welcome!Hmm perhaps something like this:
WHERE isnull(archive,0) = case when @.ShowArchived > 0 then 1 else 0 end
it isn't optimal but you can change it if it works for you.
MC
"Vicky" <Vicky@.discussions.microsoft.com> wrote in message
news:F249666A-1E70-4C13-B48E-ED50B064771C@.microsoft.com...
>I am looking for a way to create a query that, depending on the value of a
> parameter, builds the correct where-clause. This should be usable in a
> stored
> procedure.
> Example:
> parameter @.ShowArchived
> select x, y, z from table_zyx WHERE ...
> if @.ShowArchived > 0 --> WHERE archive=1
> else --> WHERE archive=0 OR archive is null
> All help is more than welcome!|||http://www.sommarskog.se/dyn-search.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Vicky" <Vicky@.discussions.microsoft.com> wrote in message
news:F249666A-1E70-4C13-B48E-ED50B064771C@.microsoft.com...
>I am looking for a way to create a query that, depending on the value of a
> parameter, builds the correct where-clause. This should be usable in a
> stored
> procedure.
> Example:
> parameter @.ShowArchived
> select x, y, z from table_zyx WHERE ...
> if @.ShowArchived > 0 --> WHERE archive=1
> else --> WHERE archive=0 OR archive is null
> All help is more than welcome!|||you can use dynamic sql.
potentially a "simpler" to understand solution, and sometimes faster to
run is to have different select statements separted by if statements
stuffed into a stored procedure.

Conditional Where Clause w/ Case Statement Possible?

Greetings,

After many hours search many forums and many failed experiments, I figure it's time to turn to the experts.

I need to execute a query that changes the returned data based upon a parameter's value. In my example below, the lob field contains both text values and nulls.

SELECT uniqueID, lob, xdate

FROM mytable

WHERE

CASE WHEN @.myparam = 'ALL'

THEN

xdate >= '2007-09-01'

ELSE

xdate >= '2007-09-01' or

lob = @.myparm

END

I've experimented with various forms of the LIKE function, checking for null/not null and keep coming up blank.

I thought about using an IF statement and creating different versions of the entire statement, however, in real-life I need to do this with four fields using four parameters (one for each field). The permutations are a little too much.

Any ideas?

Rob

Your query can be written this way, I think:

SELECT uniqueID, lob, xdate

FROM mytable

WHERE (xdate >= '20070901')

AND (

@.myparm = 'ALL'

OR

lob = @.myparm

)

In general, you can probably write:

WHERE

(@.p = 'Option1' AND (<option 1 condition>))

OR

(@.p = 'Option 2' AND (<option 2 condition>))

OR

...

Be careful where parentheses go, so AND and OR don't associate differently than you want.

In SQL, CASE .. END is an expression, by the way, so it can't be used as you hoped in your post.

Steve Kass

Drew University

http://www.stevekass.com

|||

This is really bad idea to write the logical expression to validate the variable’s value on the where clause, it might force the engine to use the index scan,

If .. Else is not harm to use in your query.. Don’t try to reduce the no of lines, check the performance..

Code Snippet

If @.myparam = 'ALL'

SELECT

uniqueID, lob, xdate

FROM

mytable

WHERE

xdate >= '2007-09-01'

ELSE

SELECT

uniqueID, lob, xdate

FROM

mytable

WHERE

xdate >= '2007-09-01' or lob = @.myparm

|||

Steve Kass wrote:

Your query can be written this way, I think:

SELECT uniqueID, lob, xdate

FROM mytable

WHERE (xdate >= '20070901')

AND (

@.myparm = 'ALL'

OR

lob = @.myparm

)

In SQL, CASE .. END is an expression, by the way, so it can't be used as you hoped in your post.

Steve - when @.myparm ='ALL', I want all of the records returned. The field (lob) does not actually contain the value 'ALL'.

I tried using a LIKE statement but found that nulls are not returned. For example, if @.myparm was populated with "%" and used in this query:

SELECT uniqueID, lob, xdate

FROM mytable

WHERE (xdate >= '20070901') and

lob LIKE @.myparm

then all records with a value in lob are returned but those will a null are not. I need to inlcude the records with a null.

Rob

|||

Manivannan.D.Sekaran wrote:

This is really bad idea to write the logical expression to validate the variable’s value on the where clause, it might force the engine to use the index scan,

If .. Else is not harm to use in your query.. Don’t try to reduce the no of lines, check the performance..

The problem with using the IF statement is the number of permutation when I have four variables.

IF @.myparm1 = 'ALL' and @.myparm2 <> 'ALL' and @.myparm3 <> 'ALL' and @.myparm4 <> 'ALL'

(query 1)

IF @.myparm1 <> 'ALL' and @.myparm2 = 'ALL' and @.myparm3 <> 'ALL' and @.myparm4 <> 'ALL'

(query 2)

IF @.myparm1 <> 'ALL' and @.myparm2 <> 'ALL' and @.myparm3 = 'ALL' and @.myparm4 <> 'ALL'

(query 3)

...

I think I'd wind up with 16 IF statements. While it should work, it would be very difficult to support.

Rob

|||

In your case the best solution is using the dynamic sql... Buliding the Where clause expression on the fly..

There are few limitations there while using the dynamic sql.. If you really need a best performance there is no compromise..

example,

Code Snippet

Declare @.SQL as Varchar(8000);

Declare @.Name as Varchar(8000);

Declare @.Type as Varchar(8000);

Declare @.Where as varchar(8000);

Set @.Name = null;

Set @.Type ='U'

Set @.SQL = 'Select * from Sysobjects Where 1=1'

Set @.Where = ''

IF @.Name is not NULL

Set @.Where = ' And Name=''' + @.Name + ''''

IF @.Type is NOT NULL

Set @.Where = @.Where + ' And Type=''' + @.Type + ''''

Exec (@.SQL + @.Where)

Code Snippet

Declare @.SQL as NVarchar(4000);

Declare @.Name as Varchar(8000);

Declare @.Type as Varchar(8000);

Declare @.Where as varchar(8000);

Set @.Name = NULL;

Set @.Type ='P'

Set @.SQL = 'Select * from Sysobjects Where 1=1'

Set @.Where = ''

IF @.Name is not NULL

Set @.Where = ' And Name=@.Name'

IF @.Type is NOT NULL

Set @.Where = @.Where + ' And Type=@.Type'

Set @.SQL = @.SQL + @.Where

Exec sp_executesql @.SQL, N'@.name varchar(100), @.type varchar(100)', @.name, @.type

|||Sorry, Rob. I misread your original intent, so let me try again. I think you want

1. If 'ALL' is passed: every row with xdate >= '20070901'
2. If 'ALL' is not passed: every row with xdate >= '20070901', as well as rows with lob = @.myparm, regardless of date.

First, a solution that assumes @.myparm is never NULL:

select uniqueID, lob, xdate
from mytable
where
(
@.myparm = 'ALL'
and
(xdate >= '20070901')
) or (
@.myparm <> 'ALL'
and
(@.myparm = lob or xdate >= '20070901')
)

Because you use NULL as a value for [lob], and you use @.myparm = NULL to select those rows, you can't rely on @.myparm = lob, which is not true when @.myparm and lob are both NULL. Unfortunately, you have to handle this separately, because T-SQL has no IS NOT DISTINCT FROM operator that means "are both equal or are both null". Whether you treat it as a third case or in the second case is up to you:

where
(
@.myparm = 'ALL'
and
(xdate >= '20070901')
) or (
@.myparm <> 'ALL'
and
(@.myparm = lob or xdate >= '20070901')
) or (
@.myparm IS NULL
and
(lob IS NULL or xdate >= '20070901')
)

So this is like what a CASE statement would be. The structure is

where
you are in case 1 and the where clause for that case holds
or
you are in case 2 and the where clause for that case holds
...

The problem with NULL is because a CASE expression would allow an OTHERWISE clause to handle both @.myparm <> 'ALL' and @.myparm IS NULL at once.

Note that I wrote the date without hyphens. Unfortunately, if xdate is [datetime] or [smalldatetime], SQL Server installations that use European and many other non-US localizations will interpret the date as you wrote it to mean January 9, 2007. I doubt you ever want that, and you can avoid a surprise by using the format I provided, or the other "safe" format '2007-09-01T00:00:00' (the T is required).

SK

|||

Steve Kass wrote:


select uniqueID, lob, xdate
from mytable
where
(
@.myparm = 'ALL'
and
(xdate >= '20070901')
) or (
@.myparm <> 'ALL'
and
(@.myparm = lob or xdate >= '20070901')
)

SK

Steve,

Thank you very much. This did the trick. It's amazing what a few AND & OR statements can do with the correct paranthesis.

I'm actually using these queres in SSRS as a data source. It took a couple of edits to get SSRS to take the paranthesis correctly as it want to "fix" them for you.

Rob

Conditional Where Clause w/ Case Statement Possible?

Greetings,

After many hours search many forums and many failed experiments, I figure it's time to turn to the experts.

I need to execute a query that changes the returned data based upon a parameter's value. In my example below, the lob field contains both text values and nulls.

SELECT uniqueID, lob, xdate

FROM mytable

WHERE

CASE WHEN @.myparam = 'ALL'

THEN

xdate >= '2007-09-01'

ELSE

xdate >= '2007-09-01' or

lob = @.myparm

END

I've experimented with various forms of the LIKE function, checking for null/not null and keep coming up blank.

I thought about using an IF statement and creating different versions of the entire statement, however, in real-life I need to do this with four fields using four parameters (one for each field). The permutations are a little too much.

Any ideas?

Rob

Your query can be written this way, I think:

SELECT uniqueID, lob, xdate

FROM mytable

WHERE (xdate >= '20070901')

AND (

@.myparm = 'ALL'

OR

lob = @.myparm

)

In general, you can probably write:

WHERE

(@.p = 'Option1' AND (<option 1 condition>))

OR

(@.p = 'Option 2' AND (<option 2 condition>))

OR

...

Be careful where parentheses go, so AND and OR don't associate differently than you want.

In SQL, CASE .. END is an expression, by the way, so it can't be used as you hoped in your post.

Steve Kass

Drew University

http://www.stevekass.com

|||

This is really bad idea to write the logical expression to validate the variable’s value on the where clause, it might force the engine to use the index scan,

If .. Else is not harm to use in your query.. Don’t try to reduce the no of lines, check the performance..

Code Snippet

If @.myparam = 'ALL'

SELECT

uniqueID, lob, xdate

FROM

mytable

WHERE

xdate >= '2007-09-01'

ELSE

SELECT

uniqueID, lob, xdate

FROM

mytable

WHERE

xdate >= '2007-09-01' or lob = @.myparm

|||

Steve Kass wrote:

Your query can be written this way, I think:

SELECT uniqueID, lob, xdate

FROM mytable

WHERE (xdate >= '20070901')

AND (

@.myparm = 'ALL'

OR

lob = @.myparm

)

In SQL, CASE .. END is an expression, by the way, so it can't be used as you hoped in your post.

Steve - when @.myparm ='ALL', I want all of the records returned. The field (lob) does not actually contain the value 'ALL'.

I tried using a LIKE statement but found that nulls are not returned. For example, if @.myparm was populated with "%" and used in this query:

SELECT uniqueID, lob, xdate

FROM mytable

WHERE (xdate >= '20070901') and

lob LIKE @.myparm

then all records with a value in lob are returned but those will a null are not. I need to inlcude the records with a null.

Rob

|||

Manivannan.D.Sekaran wrote:

This is really bad idea to write the logical expression to validate the variable’s value on the where clause, it might force the engine to use the index scan,

If .. Else is not harm to use in your query.. Don’t try to reduce the no of lines, check the performance..

The problem with using the IF statement is the number of permutation when I have four variables.

IF @.myparm1 = 'ALL' and @.myparm2 <> 'ALL' and @.myparm3 <> 'ALL' and @.myparm4 <> 'ALL'

(query 1)

IF @.myparm1 <> 'ALL' and @.myparm2 = 'ALL' and @.myparm3 <> 'ALL' and @.myparm4 <> 'ALL'

(query 2)

IF @.myparm1 <> 'ALL' and @.myparm2 <> 'ALL' and @.myparm3 = 'ALL' and @.myparm4 <> 'ALL'

(query 3)

...

I think I'd wind up with 16 IF statements. While it should work, it would be very difficult to support.

Rob

|||

In your case the best solution is using the dynamic sql... Buliding the Where clause expression on the fly..

There are few limitations there while using the dynamic sql.. If you really need a best performance there is no compromise..

example,

Code Snippet

Declare @.SQL as Varchar(8000);

Declare @.Name as Varchar(8000);

Declare @.Type as Varchar(8000);

Declare @.Where as varchar(8000);

Set @.Name = null;

Set @.Type ='U'

Set @.SQL = 'Select * from Sysobjects Where 1=1'

Set @.Where = ''

IF @.Name is not NULL

Set @.Where = ' And Name=''' + @.Name + ''''

IF @.Type is NOT NULL

Set @.Where = @.Where + ' And Type=''' + @.Type + ''''

Exec (@.SQL + @.Where)

Code Snippet

Declare @.SQL as NVarchar(4000);

Declare @.Name as Varchar(8000);

Declare @.Type as Varchar(8000);

Declare @.Where as varchar(8000);

Set @.Name = NULL;

Set @.Type ='P'

Set @.SQL = 'Select * from Sysobjects Where 1=1'

Set @.Where = ''

IF @.Name is not NULL

Set @.Where = ' And Name=@.Name'

IF @.Type is NOT NULL

Set @.Where = @.Where + ' And Type=@.Type'

Set @.SQL = @.SQL + @.Where

Exec sp_executesql @.SQL, N'@.name varchar(100), @.type varchar(100)', @.name, @.type

|||Sorry, Rob. I misread your original intent, so let me try again. I think you want

1. If 'ALL' is passed: every row with xdate >= '20070901'
2. If 'ALL' is not passed: every row with xdate >= '20070901', as well as rows with lob = @.myparm, regardless of date.

First, a solution that assumes @.myparm is never NULL:

select uniqueID, lob, xdate
from mytable
where
(
@.myparm = 'ALL'
and
(xdate >= '20070901')
) or (
@.myparm <> 'ALL'
and
(@.myparm = lob or xdate >= '20070901')
)

Because you use NULL as a value for [lob], and you use @.myparm = NULL to select those rows, you can't rely on @.myparm = lob, which is not true when @.myparm and lob are both NULL. Unfortunately, you have to handle this separately, because T-SQL has no IS NOT DISTINCT FROM operator that means "are both equal or are both null". Whether you treat it as a third case or in the second case is up to you:

where
(
@.myparm = 'ALL'
and
(xdate >= '20070901')
) or (
@.myparm <> 'ALL'
and
(@.myparm = lob or xdate >= '20070901')
) or (
@.myparm IS NULL
and
(lob IS NULL or xdate >= '20070901')
)

So this is like what a CASE statement would be. The structure is

where
you are in case 1 and the where clause for that case holds
or
you are in case 2 and the where clause for that case holds
...

The problem with NULL is because a CASE expression would allow an OTHERWISE clause to handle both @.myparm <> 'ALL' and @.myparm IS NULL at once.

Note that I wrote the date without hyphens. Unfortunately, if xdate is [datetime] or [smalldatetime], SQL Server installations that use European and many other non-US localizations will interpret the date as you wrote it to mean January 9, 2007. I doubt you ever want that, and you can avoid a surprise by using the format I provided, or the other "safe" format '2007-09-01T00:00:00' (the T is required).

SK

|||

Steve Kass wrote:


select uniqueID, lob, xdate
from mytable
where
(
@.myparm = 'ALL'
and
(xdate >= '20070901')
) or (
@.myparm <> 'ALL'
and
(@.myparm = lob or xdate >= '20070901')
)

SK

Steve,

Thank you very much. This did the trick. It's amazing what a few AND & OR statements can do with the correct paranthesis.

I'm actually using these queres in SSRS as a data source. It took a couple of edits to get SSRS to take the paranthesis correctly as it want to "fix" them for you.

Rob

|||in t-sql you can have conditional where clauses
sample:

select * from mytable
where
mycol = case when @.i = 1 then 1 else 2 end

Conditional Where clause possible?

Is it possible to use a conditional statements in a where clause?

IE: I have 3 paramaters that may or may not be filled.

I would like to do something along the lines of...

Select * From (tables)

WHERE

If @.param1 has value

Begin

'run this where statement

if @.Param2 has value

'add this to the where clause

if @.param3 has value

'add this to the where cluase

Dynamic Search Conditions in T-SQL

http://www.sommarskog.se/dyn-search.html

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB

|||

thanks but I can't get to those websites...

Our company websense filters that out as "personal"

|||

Sometimes you can get away with something like:

Select * From (tables)

WHERE

(Field1 = @.param1 OR @.param1 IS NULL) AND

(Field 2 = @.param2 OR @.param2 IS NULL) AND ...

|||

Then tell your IT department that they are actually work related and why and ask them to allow access to them.

Simple as that.

|||

Mainiac007,

You can't do conditional code in T-SQL (unlike PL/SQL). You can, however, do this:

Select*From(tables)

where

col1 =coalesce(@.param1, col1)

and col2 =coalesce(@.param2, col2)

and...

Ron

Conditional WHERE clause

Hi,

[SQL 2005 Express]

I would like a DropDownList to be populated differently depending on the selected value in a FormView.

If the FormView's selected value (CompanyID) is 2, then the DropDownList should show all Advisers from the relevant Company. Otherwise, the DropDownList should show all Advisers from the relevant Company where the TypeID field is 3.

Here is the SQL for case 1:

SELECT
AdviserID,
AdviserName
FROM
Advisers
WHERE
(CompanyID = @.CompanyID).

Here's the SQL for case 2:

SELECT
AdviserID,
AdviserName
FROM
Advisers
WHERE
(CompanyID = @.CompanyID) AND
(TypeID = 3).

Here's my best (failed) attempt to get what I want:

SELECT
AdviserID,
AdviserName
FROM
Advisers
WHERE
IF @.CompanyID = 2 THEN
BEGIN
(CompanyID = @.CompanyID)
END
ELSE
BEGIN
(CompanyID = @.CompanyID) AND
(TypeID = 3)
END

I've also tried:

SELECT
AdviserID,
AdviserName
FROM
Advisers
WHERE
CASE @.CompanyID
WHEN 2 THEN (CompanyID = @.CompanyID)
ELSE (CompanyID = @.CompanyID) AND
(TypeID = 3)
END

and

SELECT
AdviserID,
AdviserName
FROM
Advisers
WHERE
CASE WHEN (@.CompanyID = 2)
THEN (CompanyID = @.CompanyID)
ELSE (CompanyID = @.CompanyID) AND (TypeID = 3)
END

I'd be very grateul to know (a) what the correct syntax for this is and (b) if it can be achieved using a parametised query, rather than a stored procedure.

Thanks very much.

Regards

Gary

Gary,

I think you are trying to construct a select based on the values selected in some control in your web form. (correct me if i am wrong) while going through your issue, i think the following solution should work -

Generate a dynamic where clause -

declare @.SQLSelect varchar(4000),

@.SQLWhere varchar(2000)

set @.SQLSelect = 'SELECT
AdviserID,
AdviserName
FROM
Advisers '

If @.CompanyID = 2

Begin

@.SQLWhere = ' Where CompanyID = ' + @.CompanyID

End

Else

Begin

@.SQLWhere = ' Where CompanyID = ' + @.CompanyID + ' and TypeID = 3'
End

set @.SQLSelect = @.SQLSelect + @.SQLWhere

Execute @.SQLSelect

I think this will help you to think further, if didn't solve your problem.

Ash

|||

Thanks, Ash.

I suspect that I'm too much of a novice to know what to do with what you've posted.

I've tried executing it as a Query and as a Stored Procedure - with no success. I ended up simplifying it to the same WHERE clause, with no better results:

Here is the simplified query:

DECLARE @.SQLSelect varchar(4000), @.SQLWhere varchar(2000)
SET @.SQLSelect = 'SELECT AdviserID, AdviserName FROM Advisers '
IF @.AdviserCompanyID = 2
BEGIN
@.SQLWhere = ' Where AdviserCompanyID = ' + @.AdviserCompanyID
END
ELSE
BEGIN
@.SQLWhere = ' Where AdviserCompanyID = ' + @.AdviserCompanyID
END

SET @.SQLSelect = @.SQLSelect + @.SQLWhere
EXECUTE @.SQLSelect

This generated: "Must Declare the scalar variable @.AdviserCompany"

So, I changed the first line to:

DECLARE @.SQLSelect varchar(4000), @.SQLWhere varchar(2000), @.AdviserCompanyID int

This generated:"Incorrect syntax near '@.SQLWhere'"

No joy after much fiddling. I then tried to create a Stored Procedure - with similar success levels.

What am I not getting?

Thanks very much for your help.

regards

Gary

|||

Gary:

You are not the only one. It took me hours to come to this solution.

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:mytestConnectionString %>"SelectCommand="SELECT [AdviserID], [AdviserName] FROM [Adviser]

WHERE CompanyID = CASE @.CompanyID WHEN 2 THEN 2 ELSE @.CompanyID END AND

TypeID = CASE @.CompanyID WHEN 2 THEN TypeID else 3 END"

><SelectParameters><asp:ControlParameterControlID="FormView1$companyIDtxtbox"Name="CompanyID"Type="Int32"/></SelectParameters></asp:SqlDataSource>|||

Excellent, Limno - thanks very much! Works a charm...

(Still keen to find out if I was doing something stupid to prevent the Previous idea from working...Anyone?)

Regards

Gary

|||

Hi Guys,

I would like to apply Limnon's solution to the WHERE clause in the following select statement:

SELECT
FIInvestments.InvestmentID,
Accounts.AccountName + ' - ' + CAST(Accounts.AccountNumber AS varchar(20)) + CONVERT
varchar, FIInvestments.InvestmentDate, 3) + ': ' + ' ($' + LEFT (CAST(FIInvestments.Amount AS varchar),
LEN(CAST(FIInvestments.Amount AS varchar)) - 3) + ' for ' + CAST(FIInvestments.Term AS varchar(3)) + '
months)' AS Investment
FROM
FIInvestments INNER JOIN
Accounts ON FIInvestments.AccountID = Accounts.AccountID
WHERE (FIInvestments.FundID = @.FundID) AND (NOT EXISTS
(SELECT PaymentID, CommPaymentID, Date, InvestmentID, Amount, Notes
FROM FICommPayments
WHERE (InvestmentID = FIInvestments.InvestmentID)))

(In other words: I want all the Investments with the selected FundID, unless commission has already been paid on them, as evidenced by payment records in the FICommPayments table. This is so I can allocate commissionpayments to them).

However, there is one Fund/FundID (FundID = 141) which pays commission in dribs and drabs, so if that Fund is selected, I wantall the investments within that Fund - not just the ones that haven't had commission paid against them yet.

So, here are my two WHERE statements:

WHERE (FIIinvestments.FundID = @.FundID)

and

WHERE (FIInvestments.FundID = @.FundID) AND (NOT EXISTS
(SELECT PaymentID, CommPaymentID, Date, InvestmentID, Amount, Notes
FROM FICommPayments
WHERE (InvestmentID = FIInvestments.InvestmentID)))

And here's my best attempt at a conditional WHERE statement so far:

WHERE CASE @.FundID WHEN 141 THEN (FIInvestments.FundID = @.FundID) ELSE (FIInvestments.FundID = @.FundID) AND (NOT EXISTS (SELECT PaymentID, CommPaymentID, Date, InvestmentID, Amount, Notes FROM FICommPayments WHERE (InvestmentID = FIInvestments.InvestmentID))) END

The error messages this generates are:

Incorrect syntax near '='.
Incorrect syntax near ')'.

I also tried, with little hope:

WHERE (FIInvestments.FundID = @.FundID) CASE @.FundID WHEN NOT 141 THEN (NOT EXISTS
(SELECT PaymentID, CommPaymentID, Date, InvestmentID, Amount, Notes
FROM FICommPayments
WHERE (InvestmentID = FIInvestments.InvestmentID)))END

This generated:

Incorrect syntax near the keyword 'NOT'.
Incorrect syntax near ')'

Perhaps I should revert to Ash's solution - but I got stuck on that one, too!

Thanks for the help.

Regards

Gary

|||

Hello:

I don't know why your approaches did not work. You may post that question again if you want an answer. Instead, you can split your condition in two separate parts, then use UNION OR UNION ALL (with possible duplicates) to combine the results.

I used a simplified version of your tables to test the following script, it works as to my understanding. But you may need to tweak it for your real case.


SELECT FIInvestments.InvestmentID, FIInvestments.FUNDID
FROM FIInvestments WHERE FundID<>141 AND FundID=@.FundID AND FIInvestments.FundID NOT IN (SELECT FICommPayments.FUNDID
FROM FICommPayments INNER JOIN FIInvestments ON FICommPayments.InvestmentID = FIInvestments.InvestmentID)
UNION ALL
SELECT FIInvestments.InvestmentID, FIInvestments.FUNDID
FROM FIInvestments
WHERE FundID=141 ANDFundID=@.FundID

|||

Excellent, Limon!

I started off thinking that your suggestion wasn't exactly what I'm after in this case (because I need one list for case 141 and the other for every other case, so the UNION ALL didn't seem like what I was after until it dawned on me what you're doing - very sneaky!).

I've been looking for UNION / UNION ALL for other reasons, so I get a double hit out of this one. Thanks very much - you're making my day on a number of fronts at the moment (including on the other thread)!

If this keeps up much longer, I'll have to put you on a retainer. In fact, I think that the guru's amongst you should work out an easy way of allowing novices like me to secure a commercial agreement/service with you guys in addition to this freebie one. I know that it exposes the community to abuse, but I'm sure there must be a way of doing it... I'll keep thinking and ewxperiencing and come up with something over the next month or two.

Regards

Gary