Help! The IIF Statement in a query...

Part of the where clause in my SQL Statement is conditional. The query clause is as follows:

SELECT...

FROM...

WHERE PROJECT.COMPLETED<>-1 AND IIF(PROJECT.COST > RANGE.MINRANGE AND PROJECT.COST<RANGE.MAXRANGE , PROJECTRANGE.PROJECTRANGEID <>0 ,NULL)

I guess I didn't translate the if satement correctly because I always got errors when I tried to preview my report.

Need help analyzing the if statement for me. Thanks in advance.

What exactly are you trying to do?

Besides, the IIF you have is not syntactically correctn. IIF(<condition>, Expression if the condition is TRUE, Expression if the condition is FALSE). What you have is IIF( <condition>, <Condition>, <Value>) which is incorrect.

|||

Thanks for reply ndinakar. Here is what I am trying to do:

In the if statement, if ProjectCompleted is true(-1 means false), and if project.cost is greater than minimum range and less than max range, then the where clause should be like the following:

WHERE PROJECT.COMPLETED<>-1 ANDRANGE.PROJECTRANGEID <>0

If the Project.Cost is out of the range of minimum and max range (greater than max range or less than minimum range), then the if statement should not return anything, and the where clause will be like this:

WHERE PROJECT.COMPLETED<>-1

|||

I think I understand your question only partially. So what do you mean when you say return nothing if cost is out of the range? Do you still want to see those records or they should not be in the result set? You can probabbly put a filter on the record set accodringly.

|||Not sure if this will help but it looks to me like you are mixing your languages. IIF is for use in expressions in reporting services table cells etc. In SQL you have to use IF with BEGIN and END for your conditional statements. Have a look at this link which I found very usefulhttp://www.databasejournal.com/features/mssql/article.php/3361651sql

help writing s.proc in sql2005

hi all.

in my sql2005 i have a function that returns a value. func(x) returns j

how can use it in a select clause inside a s.proce?

select bb, func(xx) as jj , from ....

?

You should be able to include it right in your SELECT statement, since it returns a scalar.

You will, however, need to qualify the function with the schema; SELECT dbo.func(xx) or SELECT myschema.func(xx) etc.

Help with WHERE Clause in Stored Procedure

Hi,

I have an sp with the following WHERE clause

@.myqarep varchar(50)

SELECT tblCase.qarep FROM dbo.tblCase

WHERE dbo.tblCase.qarep = CASE @.myqarep WHEN '<All>' THEN
dbo.tblCase.qarep ELSE @.myqarep

@.myqarep is returned from a combo box (ms access)...the user either
picks a qarep from the combo box or they leave the default which is
'<All>'

they problem i'm having is that if the record's value for
dbo.tblCase.qarep is null...the record does not show up in the
results...but i need it to

any help is appreciated.

thanks
Paul... WHERE qarep = @.myqarep
OR @.myqarep = '<All>'

--
David Portas
SQL Server MVP
--|||thanks for the quick response...i'll give it a try!

David Portas wrote:
> ... WHERE qarep = @.myqarep
> OR @.myqarep = '<All>'
> --
> David Portas
> SQL Server MVP
> --

Help with WHERE Clause

hi guys help please..I have a stored procedure below that basically retrieve data from tables and under my WHERE clause I want to execute conditions depending on the value of "@.FilterBy" variable. If @.FilterBy is equal to "Pending" then execute a conditions under "IF @.FilterBy = 'Pending'" and if it equals to 'Delivered' then execute conditions under IF @.FilterBy = 'Delivered'. But unfortunately I can't figure out how to do that my stored procedure below just wont work becuase it has an error "Incorrect syntax near the keyword 'IF'"...Any help guys on how to solve this problem? Thanks in advance!

USE [CFREEDB]
GO
/****** Object: StoredProcedure [dbo].[usp_DELIVERY_GET] Script Date: 09/01/2007 12:03:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_DELIVERY_GET]
@.FilterBy varchar(20),
@.CustomerID int,
@.FromDate datetime,
@.ToDate datetime
AS
BEGIN

SELECT DISTINCT Delivery.CustomerID, Customer.Customer_LastName, Customer.Customer_MiddleName, Customer.Customer_FirstName,
Customer.Customer_Company, Customer.Customer_Address, Customer.Customer_ContactNo, Customer.Customer_Discount, Customer_Balance

FROM CFREE_Delivery Delivery
INNER JOIN CFREE_Customer Customer
ON Delivery.CustomerID = Customer.CustomerID
WHERE
IF @.FilterBy = 'Pending'
BEGIN
Delivery.IsDeleted <> 1 AND
Delivery.IsDelivered IS NULL AND
Delivery.IsRemitted IS NULL AND
Delivery_Date BETWEEN @.FromDate AND @.ToDate
END
IF @.FilterBy = 'Delivered'
BEGIN
Delivery.IsDeleted <> 1 AND
Delivery.IsDelivered IS NOT NULL AND
Delivery.IsRemitted IS NOT NULL AND
Delivery_Date BETWEEN @.FromDate AND @.ToDate
END

ORDER BY Customer.Customer_LastName, Customer.Customer_FirstName, Customer.Customer_MiddleName

ENDWHERE Delivery.IsDeleted <> 1
AND Delivery_Date BETWEEN @.FromDate AND @.ToDate
AND (
( @.FilterBy = 'Pending'
AND Delivery.IsDelivered IS NULL
AND Delivery.IsRemitted IS NULL
)
OR ( @.FilterBy = 'Delivered'
AND Delivery.IsDelivered IS NOT NULL
AND Delivery.IsRemitted IS NOT NULL
)
)

Help with WHERE CLAUSE

I'm having a heck of time with this where clause. I have a table that contains client addresses, a client can have more than one address. So some of the addresses may be seasonal. I need to return only the current address based on a flag MailTo (bit) and a date range, just the month and day, the start and end are datetime datatypes.

Here is what i have tried:

I would really would like it to work on a range of month and day based on the startdate and enddate fields and the MailTo flag.
The table looks like this;

tblClientAddresses:
Address_ID,Client_ID,Address,Address2,City,State,Zip,Country,AddressType,StartDate,
EndDate,MailTo

WHERE (A.MailTo=1) AND (A.EndDate Is Null OR DatePart(mm,A.Enddate) >= DatePart(mm,GETDATE()) AND DatePart(dd,A.Enddate) >= DatePart(dd,GETDATE()))

Thank you for any help!I have tried this:

WHERE (A.MailTo=1) AND (Month(Day(GETDATE())) BETWEEN Month(Day(A.Startdate)) AND(Month(Day(A.Enddate))) OR (A.Startdate Is Null) AND (A.EndDate Is Null))

But it still not returning the proper row.

Does anyone have an idea if what i show above is even doable?

Thanks|||Try this:

WHERE (A.MailTo=1)
AND Convert(varchar(5),GetDate(),10) BETWEEN
Convert(varchar(5),A.Startdate,10) AND Convert(varchar(5),A.Enddate,10)

The datetime format #10 is mm-dd-yy. If you limit the cast to varchar(5) you only get mm-dd.

JK|||That has fixed the date issue, but now the MailTo flag is being ignored. Should I adjust the paranthises somehow? I have moved the flag to the end and added paranthises but no luck.

Thank you very much|||Hmm. It is odd that the MailTo flag is being ignored.

Could you post your full query command with all the trimmings?
JK

Help With Where Clause

I am creating a food database application where I allow the user to search
for food names. For this example, lets supposed that my database has the
following food names on the table.
Salt with water
Chips and salsa
Fried Chicken
Guacamole
If the user types 'sa' on the search textbox I would like to retrieve the
following records:
Salt with water
Chips and salsa
This is because Salt starts with 'sa' and salsa also starts with 'sa'. If
the user typed 'sa ch' (this is the string 'sa' then a space and then the
string 'ch'). I would like to retrieve the records
Salt with water
Chips and salsa
Fried Chicken
I get the same records as before because I included the string 'sa' and I
get the record 'Fried Chicken' because Chicken starts with 'ch'. The problem
is that the user can have as many words as he or she pleases. It even gets
more complicated because I would like to use a store procedure on Microsoft
SQL server.
Could someone help me creating this store procedure, PLEASE! Thank you.Rene
See if this helps
CREATE TABLE #Test
(
col VARCHAR(50)
)
INSERT INTO #Test VALUES ('Salt with water')
INSERT INTO #Test VALUES ('Chips and salsa')
INSERT INTO #Test VALUES ('Fried Chicken')
INSERT INTO #Test VALUES ('Guacamole')
SELECT * FROM #Test WHERE col LIKE '%sa%'
"Rene" <nospam@.nospam.com> wrote in message
news:uI8eG$kaFHA.3120@.TK2MSFTNGP12.phx.gbl...
> I am creating a food database application where I allow the user to search
> for food names. For this example, lets supposed that my database has the
> following food names on the table.
> Salt with water
> Chips and salsa
> Fried Chicken
> Guacamole
> If the user types 'sa' on the search textbox I would like to retrieve the
> following records:
> Salt with water
> Chips and salsa
> This is because Salt starts with 'sa' and salsa also starts with 'sa'. If
> the user typed 'sa ch' (this is the string 'sa' then a space and then the
> string 'ch'). I would like to retrieve the records
> Salt with water
> Chips and salsa
> Fried Chicken
> I get the same records as before because I included the string 'sa' and I
> get the record 'Fried Chicken' because Chicken starts with 'ch'. The
problem
> is that the user can have as many words as he or she pleases. It even gets
> more complicated because I would like to use a store procedure on
Microsoft
> SQL server.
> Could someone help me creating this store procedure, PLEASE! Thank you.
>|||> SELECT * FROM #Test WHERE col LIKE '%sa%'
This won't work. Aquery like this will also return food name such as 'Carne
Aa' because 'Aa' has 'sa' inside its text, I am only interested on
food that has any of its words *start* with the string 'sa'.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23OXdQmlaFHA.3364@.TK2MSFTNGP09.phx.gbl...
> Rene
> See if this helps
> CREATE TABLE #Test
> (
> col VARCHAR(50)
> )
> INSERT INTO #Test VALUES ('Salt with water')
> INSERT INTO #Test VALUES ('Chips and salsa')
> INSERT INTO #Test VALUES ('Fried Chicken')
> INSERT INTO #Test VALUES ('Guacamole')
> SELECT * FROM #Test WHERE col LIKE '%sa%'
>
> "Rene" <nospam@.nospam.com> wrote in message
> news:uI8eG$kaFHA.3120@.TK2MSFTNGP12.phx.gbl...
> problem
> Microsoft
>|||WHERE col LIKE '% sa%' or col like LIKE '%,sa%' or col like LIKE '%.sa%' or
left(col, 2) = 'sa'
You know where I'm going with this, right.
I have seen a script (stored procedure or function) that extracts words from
a string and converts it to a table, one word per column
This may help you with this problem. Maybe someone here could point you to
the script. I can't seem to be able to find it right now.
"Rene" <nospam@.nospam.com> wrote in message
news:ONj7qTqaFHA.2736@.TK2MSFTNGP12.phx.gbl...
> This won't work. Aquery like this will also return food name such as
> 'Carne Aa' because 'Aa' has 'sa' inside its text, I am only
> interested on food that has any of its words *start* with the string 'sa'.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23OXdQmlaFHA.3364@.TK2MSFTNGP09.phx.gbl...
>

Help with Where clause

Hi,
I want to return records based on a linetype and classtype
Here is what I have so far
Where (LineType = '1' OR LineType = '7')
or (LineType = '5'AND ClassType NOT LIKE '[_]%')
I am trying to return all LineType 1 and 7 and only LineType 5 where the
classtype does not start with an underscore but the above syntax doesn't
return any records where the ClassType starts with an underscore regardless
of whether they are LineType 1, 7 or 5
How can I achieve my goal?
Thanks
On Thu, 17 Mar 2005 10:09:21 -0000, Newbie wrote:

>Hi,
>I want to return records based on a linetype and classtype
>Here is what I have so far
>Where (LineType = '1' OR LineType = '7')
>or (LineType = '5'AND ClassType NOT LIKE '[_]%')
>I am trying to return all LineType 1 and 7 and only LineType 5 where the
>classtype does not start with an underscore but the above syntax doesn't
>return any records where the ClassType starts with an underscore regardless
>of whether they are LineType 1, 7 or 5
>How can I achieve my goal?
>Thanks
>
Hi Newbie,
I'm sorry, but I could not reproduce the behaviour you're reporting. Run
the following script in Query Analyzer and check the results:
create table test(LineType char(1) not null, ClassType char(3) not null)
go
insert test select '1', '_as' union all select '1', 'dfg'
union all select '5', '_as' union all select '5', 'dfg'
union all select '7', '_as' union all select '7', 'dfg'
union all select '8', '_as' union all select '8', 'dfg'
go
select * from test
Where (LineType = '1' OR LineType = '7')
or (LineType = '5'AND ClassType NOT LIKE '[_]%')
go
drop table test
go
Results on my test database:
LineType ClassType
-- --
1 _as
1 dfg
5 dfg
7 _as
7 dfg
If you have different results, then please paste the output of
SELECT @.@.VERSION
in a reply to this message.
If you have the same results, then it must be something else. Please
post a simple script (as I did above) that allows me to reproduce the
behaviour. Include the expected output as well. (www.aspfaq.com/5006)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks _ I tried your script and it worked fine - the problem was my working
of the calculator - managed to make the same mistake more than once!
Thanks again
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:d5ui31d2np1k5khvonabvmbcdmpr1lollf@.4ax.com...
> On Thu, 17 Mar 2005 10:09:21 -0000, Newbie wrote:
>
> Hi Newbie,
> I'm sorry, but I could not reproduce the behaviour you're reporting. Run
> the following script in Query Analyzer and check the results:
> create table test(LineType char(1) not null, ClassType char(3) not null)
> go
> insert test select '1', '_as' union all select '1', 'dfg'
> union all select '5', '_as' union all select '5', 'dfg'
> union all select '7', '_as' union all select '7', 'dfg'
> union all select '8', '_as' union all select '8', 'dfg'
> go
> select * from test
> Where (LineType = '1' OR LineType = '7')
> or (LineType = '5'AND ClassType NOT LIKE '[_]%')
> go
> drop table test
> go
> Results on my test database:
> LineType ClassType
> -- --
> 1 _as
> 1 dfg
> 5 dfg
> 7 _as
> 7 dfg
> If you have different results, then please paste the output of
> SELECT @.@.VERSION
> in a reply to this message.
> If you have the same results, then it must be something else. Please
> post a simple script (as I did above) that allows me to reproduce the
> behaviour. Include the expected output as well. (www.aspfaq.com/5006)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Help with TSQL where clause syntax

Hey everyone I want a where clause based off a variable input and I'm having some trouble with the syntax. My current code(incorrect) is below but it shows what I am aiming for. Does anyone have any suggestions?

Code Snippet

(CASE
WHEN @.inIndustry = 'ALL' THEN
WHERE EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE
WHERE OpportunityID = op.OpportunituID AND monthyear LIKE '%' + @.Year1)
ELSE
WHERE (acIF.Industry <> 'Defense' OR acIF.Industry IS NULL)
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE
WHERE OpportunityID = op.OpportunituID AND monthyear LIKE '%' + @.Year1) END)

Maybe this:

Code Snippet

(CASE

WHEN @.inIndustry ='ALL'AND

EXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

ELSE

CASEWHEN(acIF.Industry <>'Defense'OR acIF.Industry ISNULL)

ANDEXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

ELSE 0

END--inner case

END)-- outer case

|||Hey. Thanks for the help but I dont see how that would work. I'm trying to make the WHERE clause dynamic in a way. It's all based off whatever the var @.inIndustry is equal to. So if @.inIndustry is equal to 'ALL' then is uses a certain where clause. I dont want to base the equivalence off of the entire statement there.|||

Code Snippet

WHERE(CASE

WHEN @.inIndustry ='ALL'AND

EXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

WHEN(acIF.Industry <>'Defense'OR acIF.Industry ISNULL)

ANDEXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

ELSE 0

END)

= 1

|||Ahh I see how it works now. I plugged it in and it works like a charm. Thanks for the help!
|||

No problem; my pleasure.

Much appreciated if you can mark the solution as the answer

|||Actually it didnt work exactly as it should... This is the code i have at the moment.

Code Snippet

WHERE
(CASE
--MASTER
WHEN @.inIndustry = 'ALL'
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE WHERE OpportunityID = op.OpportunityID AND monthyear LIKE '%' + @.Year1)

THEN 1

--ENERGY AND UTILITIES
WHEN @.inIndustry = 'EU' AND acIf.Industry = 'Energy and Utilities'
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE WHERE OpportunityID = op.OpportunityID AND monthyear LIKE '%' + @.Year1)
THEN 1

-- NONGOV - E&U + OTHER
WHEN (acIF.Industry <> 'Defense' OR acIF.Industry IS NULL)
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE WHERE OpportunityID = op.OpportunityID AND monthyear LIKE '%' + @.Year1)
THEN 1

ELSE 0
END) = 1

Now comparing original code and the code i wrote aboveI know that it performs the the last case statement no matter what i input for @.inIndustry. Is there any way to fix this?
|||

Nest CASE statements

CASE WHEN @.inIndustry = 'ALL' ..

ELSE

CASE WHEN ...

ELSE

END

END

|||I ended up adding checks to the last statement to make sure @.inIndustry wasn't equal to EU, ALL etc.. and it works fine now.

Thanks for all the help!