CONDITIONAL 'WHERE'

THIS IS MY QUERY:
SELECT *
FROM users a
WHERE a.usertype = 'contact' AND ISNULL (a.FirstNm,'') LIKE 'MMColParam5%'
and ISNULL (a.LastNm,'') LIKE 'MMColParam6%'
AND STATUS IN (MMCOLPARAM7)
ORDER BY d.Attorney
THE LINE WHERE IT SAYS "AND STATUS IN (MMCOLPARM7), I WANT TO MAKE THAT
CONDITIONAL AND ONLY RUN IT IF MMCOLPARAM7 IS NOT NULL, IF IT IS NULL TO
SKIP THAT "AND" AND JUST EXECUTE THE REST OF THE QUERY.
HOW CAN THIS BE DONE ?
THANKS FOR THE HELP GUYS.
AHi
Try something like:
SELECT *
FROM users a
WHERE a.usertype = 'contact' AND ISNULL (a.FirstNm,'') LIKE 'MMColParam5%'
AND ISNULL (a.LastNm,'') LIKE 'MMColParam6%'
AND ( STATUS IN (MMCOLPARAM7)
OR MMCOLPARAM7 IS NULL )
John
"Aleks" wrote:

> THIS IS MY QUERY:
>
> SELECT *
> FROM users a
> WHERE a.usertype = 'contact' AND ISNULL (a.FirstNm,'') LIKE 'MMColParam5
%'
> and ISNULL (a.LastNm,'') LIKE 'MMColParam6%'
> AND STATUS IN (MMCOLPARAM7)
> ORDER BY d.Attorney
> --
> THE LINE WHERE IT SAYS "AND STATUS IN (MMCOLPARM7), I WANT TO MAKE THAT
> CONDITIONAL AND ONLY RUN IT IF MMCOLPARAM7 IS NOT NULL, IF IT IS NULL TO
> SKIP THAT "AND" AND JUST EXECUTE THE REST OF THE QUERY.
> HOW CAN THIS BE DONE ?
> THANKS FOR THE HELP GUYS.
> A
>
>|||With regards to

> AND STATUS IN (MMCOLPARAM7)
See if this works for status filtering
AND STATUS =coalesce(MMCOLPARAM7,STATUS)
HTH..
--
http://zulfiqar.typepad.com
BSEE, MCP
"Aleks" wrote:

> THIS IS MY QUERY:
>
> SELECT *
> FROM users a
> WHERE a.usertype = 'contact' AND ISNULL (a.FirstNm,'') LIKE 'MMColParam5
%'
> and ISNULL (a.LastNm,'') LIKE 'MMColParam6%'
> AND STATUS IN (MMCOLPARAM7)
> ORDER BY d.Attorney
> --
> THE LINE WHERE IT SAYS "AND STATUS IN (MMCOLPARM7), I WANT TO MAKE THAT
> CONDITIONAL AND ONLY RUN IT IF MMCOLPARAM7 IS NOT NULL, IF IT IS NULL TO
> SKIP THAT "AND" AND JUST EXECUTE THE REST OF THE QUERY.
> HOW CAN THIS BE DONE ?
> THANKS FOR THE HELP GUYS.
> A
>
>

Conditional IF in a derived column transform

HI, I was wondering if there is a possibility to use a confitional if like this:

IF(ISNULL(mycolumn value, "new value if null", mycolumnvalue)

into a derived column transform to infer a value to a null column value. I do know I can do it using a script component by it would be simpler to do by using an expression.

Thank you,

Ccote

Yes, you can do this. Look here in BOL for conditional operator: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/d38e6890-7338-4ce0-a837-2dbb41823a37.htm

-Jamie

|||

Thank you Jamie. I always forget to look in BOL.

Thank you again

Ccote

Conditional expressions - isnull(A) OR isnull(B)

I am trying to reproduce an expression in my access front-end database
in an SQL view (using Visual Studio 2005 view definition). The
expression is:
SELECT dbo_T200PEOPLE.PersonNo, dbo_T200PEOPLE.FirstName,
dbo_T200PEOPLE.LastName, IIf(IsNull([Password]) Or
IsNull([PasswordHint]),"No","Yes") AS Secured, dbo_T200PEOPLE.Password,
dbo_T200PEOPLE.PasswordHint
FROM dbo_T200PEOPLE;
Can anyone tell me how to reproduce the "IIf(IsNull([Password]) Or
IsNull([PasswordHint]),"No","Yes") AS Secured" part? All help
gratefully received!Take a look at CASE expression in the BOL
"neilr" <neilryder@.yahoo.com> wrote in message
news:1148376579.910713.297590@.i40g2000cwc.googlegroups.com...
>I am trying to reproduce an expression in my access front-end database
> in an SQL view (using Visual Studio 2005 view definition). The
> expression is:
> SELECT dbo_T200PEOPLE.PersonNo, dbo_T200PEOPLE.FirstName,
> dbo_T200PEOPLE.LastName, IIf(IsNull([Password]) Or
> IsNull([PasswordHint]),"No","Yes") AS Secured, dbo_T200PEOPLE.Password,
> dbo_T200PEOPLE.PasswordHint
> FROM dbo_T200PEOPLE;
> Can anyone tell me how to reproduce the "IIf(IsNull([Password]) Or
> IsNull([PasswordHint]),"No","Yes") AS Secured" part? All help
> gratefully received!
>|||OK that did it thanks. For anyone else interested, it now looks like
this:
CASE
WHEN PEP.Password IS NULL OR
PEP.PasswordHint IS NULL OR
PEP.Salutation IS NULL OR
PEP.FirstName IS NULL OR
PEP.JobTitle IS NULL
THEN 'No'
ELSE 'Yes'
END
AS DataComplete

Conditional expressions - isnull(A) OR isnull(B)

I am trying to reproduce an expression in my access front-end database
in an SQL view (using Visual Studio 2005 view definition). The
expression is:
SELECT dbo_T200PEOPLE.PersonNo, dbo_T200PEOPLE.FirstName,
dbo_T200PEOPLE.LastName, IIf(IsNull([Password]) Or
IsNull([PasswordHint]),"No","Yes") AS Secured, dbo_T200PEOPLE.Password,
dbo_T200PEOPLE.PasswordHint
FROM dbo_T200PEOPLE;
Can anyone tell me how to reproduce the "IIf(IsNull([Password]) Or
IsNull([PasswordHint]),"No","Yes") AS Secured" part? All help
gratefully received!Take a look at CASE expression in the BOL
"neilr" <neilryder@.yahoo.com> wrote in message
news:1148376579.910713.297590@.i40g2000cwc.googlegroups.com...
>I am trying to reproduce an expression in my access front-end database
> in an SQL view (using Visual Studio 2005 view definition). The
> expression is:
> SELECT dbo_T200PEOPLE.PersonNo, dbo_T200PEOPLE.FirstName,
> dbo_T200PEOPLE.LastName, IIf(IsNull([Password]) Or
> IsNull([PasswordHint]),"No","Yes") AS Secured, dbo_T200PEOPLE.Password
,
> dbo_T200PEOPLE.PasswordHint
> FROM dbo_T200PEOPLE;
> Can anyone tell me how to reproduce the "IIf(IsNull([Password]) Or
> IsNull([PasswordHint]),"No","Yes") AS Secured" part? All help
> gratefully received!
>|||OK that did it thanks. For anyone else interested, it now looks like
this:
CASE
WHEN PEP.Password IS NULL OR
PEP.PasswordHint IS NULL OR
PEP.Salutation IS NULL OR
PEP.FirstName IS NULL OR
PEP.JobTitle IS NULL
THEN 'No'
ELSE 'Yes'
END
AS DataComplete