conditions in where clause

Hi I am upsizing the access database to SQL 2005.
I currently converting Access Query's to SQL stored procs and functions but i have got stuck on one issue.

In access there is a update statement that has an IIF in the where clause, i have tried replicating this in T-SQL buy using case statement but it does not work.

in access the where looks some thing like this Where IIF(table1.Column1 = 0,Table2.column1,Table3.Column1)

Any one know how i can best replicate this behavior?

Dagz

In place of IIF, use CASE. Using your example:

Where IIF(table1.Column1 = 0,Table2.column1,Table3.Column1)

Code Snippet

WHERE CASE Table1.Column1
WHEN 0 THEN Table2.Column1
ELSE Table3.Column1
END

|||Here you go

Code Snippet

Case WHEN table1.Column1 = 0 THEN Table2.Column1
else Table3.Column1
END|||

when i try this i get the error message, where is of none boolean type?

Conditionals on derived columns

Hi,

Here's my current query, which throws an error that "AgeCalc" is an invalid column in the WHERE clause:

----------
SELECT
.
.
.,
AgeCalc =
CASE
WHEN dateadd(year, datediff (year, B.DOB, B.DateIn), B.DOB) > B.DateIn
THEN datediff (year, B.DOB, B.DateIn) - 1
ELSE datediff (year, B.DOB, B.DateIn)
END

FROM
ResidentData B

WHERE
(AgeCalc >= 18)
----------

How do I do conditionals on the "AgeCalc" derived column?

Thanks.How do I do conditionals on the "AgeCalc" derived column?

Thanks.

You have to write the expression over again:
WHERE
CASE
WHEN dateadd(year, datediff (year, B.DOB, B.DateIn), B.DOB) > B.DateIn
THEN datediff (year, B.DOB, B.DateIn) - 1
ELSE datediff (year, B.DOB, B.DateIn)
END >= 18

Alternatively, write a view that includes your derived column and then you can use your column name in an expression.

I don't recommend using CASE statements in WHERE clauses. It can result in sub-optimal query execution plans.

Regards,

hmscott|||Thanks for your help - I will test the solution and see what the performance is like.

The current situation does not allow me to consider creating views, so I'll have to stick to keeping the query similar to the way it already is.|||select *
from (
SELECT ...
, AgeCalc =
CASE WHEN dateadd(year
, datediff(year, B.DOB, B.DateIn)
, B.DOB) > B.DateIn
THEN datediff(year, B.DOB, B.DateIn) - 1
ELSE datediff(year, B.DOB, B.DateIn)
END
FROM ResidentData B
) as T
WHERE AgeCalc >= 18|||Thanks guys. Both solutions worked well. I will use the second one since it's about half a second faster.

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 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 Union!

Hi all,
I have a query that if it returns data i want to perform a union on it.
IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
If (? Above query returns rows)
UNION
SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
Is this kind of thing possible'
A basic example would be great!!
Cheers,
Adam.Adam Knight wrote:
> Hi all,
> I have a query that if it returns data i want to perform a union on
> it.
> IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
> If (? Above query returns rows)
> UNION
> SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
> Is this kind of thing possible'
> A basic example would be great!!
> Cheers,
> Adam.
IF EXISTS (Select * FROM myTable WHERE myColumn = 'a')
Select NEWID(), <explicitly specify columns> FROM myTable WHERE
myColumn = 'a'
UNION ALL -- Use a UNION ALL in most cases
SELECT NEWID(), <explicitly specify columns> FROM myTable 2 WHERE
myColumn = 'b'
ORDER BY 1
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Answered in microsoft.public.sqlserver.programming.
Help others to help you. Please do not multi-post!
David Portas
SQL Server MVP
--

Conditional UNION!

Hi all,
I have a query that if it returns data i want to perform a union on it.
IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
If (? Above query returns rows)
UNION
SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
Is this kind of thing possible'
A basic example would be great!!
Cheers,
Adam."Adam Knight" <adam@.pertrain.com.au> wrote in message
news:OKNz$V9wFHA.3644@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I have a query that if it returns data i want to perform a union on it.
> IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
> If (? Above query returns rows)
> UNION
> SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
> Is this kind of thing possible'
>
Would this work?
Select *
FROM myTable
WHERE myColumn = 'a'
UNION
SELECT *
FROM myTable
WHERE myColumn = 'b'
and exists(
Select *
FROM myTable
WHERE myColumn = 'a')
Regards,
John|||Adam
Is there any reason to use UNION instead of UNION ALL? Do you want to
eliminate duplications?
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:OKNz$V9wFHA.3644@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I have a query that if it returns data i want to perform a union on it.
> IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
> If (? Above query returns rows)
> UNION
> SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
> Is this kind of thing possible'
> A basic example would be great!!
> Cheers,
> Adam.
>
>|||Yes!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:edBylb%23wFHA.3000@.TK2MSFTNGP12.phx.gbl...
> Adam
> Is there any reason to use UNION instead of UNION ALL? Do you want to
> eliminate duplications?
>
> "Adam Knight" <adam@.pertrain.com.au> wrote in message
> news:OKNz$V9wFHA.3644@.TK2MSFTNGP11.phx.gbl...
>|||Try:
SELECT DISTINCT *
FROM MyTable
WHERE mycolumn IN ('A','B')
AND EXISTS
(SELECT *
FROM MyTable
WHERE mycolumn = 'A') ;
ORDER BY NEWID() fails under UNION or DISTINCT unless you also add NEWID()
to the SELECT list (in which case duplicates would not be eliminated).
Apparently your table doesn't have a key. I suggest you fix that problem
first but I don't see how this query helps you do that.
If the above doesn't help, please post DDL, sample data and required results
as suggested here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--

Conditional Union!

Hi all,
I have a query that if it returns data i want to perform a union on it.
IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
If (? Above query returns rows)
UNION
SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
Is this kind of thing possible?
A basic example would be great!!
Cheers,
Adam.
Adam Knight wrote:
> Hi all,
> I have a query that if it returns data i want to perform a union on
> it.
> IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
> If (? Above query returns rows)
> UNION
> SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
> Is this kind of thing possible?
> A basic example would be great!!
> Cheers,
> Adam.
IF EXISTS (Select * FROM myTable WHERE myColumn = 'a')
Select NEWID(), <explicitly specify columns> FROM myTable WHERE
myColumn = 'a'
UNION ALL -- Use a UNION ALL in most cases
SELECT NEWID(), <explicitly specify columns> FROM myTable 2 WHERE
myColumn = 'b'
ORDER BY 1
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Answered in microsoft.public.sqlserver.programming.
Help others to help you. Please do not multi-post!
David Portas
SQL Server MVP

Conditional Union!

Hi all,
I have a query that if it returns data i want to perform a union on it.
IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
If (? Above query returns rows)
UNION
SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
Is this kind of thing possible'
A basic example would be great!!
Cheers,
Adam.Adam Knight wrote:
> Hi all,
> I have a query that if it returns data i want to perform a union on
> it.
> IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
> If (? Above query returns rows)
> UNION
> SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
> Is this kind of thing possible'
> A basic example would be great!!
> Cheers,
> Adam.
IF EXISTS (Select * FROM myTable WHERE myColumn = 'a')
Select NEWID(), <explicitly specify columns> FROM myTable WHERE
myColumn = 'a'
UNION ALL -- Use a UNION ALL in most cases
SELECT NEWID(), <explicitly specify columns> FROM myTable 2 WHERE
myColumn = 'b'
ORDER BY 1
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Answered in microsoft.public.sqlserver.programming.
Help others to help you. Please do not multi-post!
--
David Portas
SQL Server MVP
--

Conditional Table Joins

Hi all,
I'm having a little trouble with producing a query for MS SQL Server
2000 with a conditional join in.
The basis of the structure is that when an order is active, the
OrderID of any book in the Order is stored in the book record in the
Books table. If an order is cancelled, the OrderID is removed from the
record in the Books table, to enable the book to be put back on sale.
At that point, for each book cancelled, a record is appended to the
CancelledRefundedBooks table, with just the book reference number
(foreign key of the Books table), and the OrderID it belonged to. This
is to us to still see which books were in a cancelled order. I hope
that made sense! i've posted the table defs for these tables at the
bottom of this message.
My problem is when using the following stored procedure query to
search for orders based on different criteria, included a reference
number for a book in the order, the join with the books table is
causing cancelled orders not to be returned. For example, if i search
for active order 1, because one of the possible search criteria is
book reference, the Books table is joined to the query. Because order
1 is active, the book records in the Books table have orderID's
stored, and the order is returned. However, if i search for cancelled
order 2, because the books are lacking the OrderID field, the join is
causing the order record not to be returned. I have tried using an
INNER JOIN and a LEFT OUTER JOIN, and neither help. The ideal
situation would be if the join for the Books table could be
conditional on the OrderStatusID field in the Orders table, so that
the query would be joined to the Books table if the order was active,
and if the order was cancelled, the join would change to the
CancelledRefundedBooks table.
An additional complication is that if the join was to the
CancelledRefundedBooks table, in the event of a cancelled order, the
CancelledRefundedBooks table holds only the book reference numbers.
This means that table would then need a further join onto the Books
table to retrieve author, title etc. fields.
The stored proc as it stands is as follows:
ALTER PROCEDURE sp_searchorders
@.refnumber int = NULL,
@.surname nvarchar(100) = NULL,
@.orderid int = NULL,
@.postcode nvarchar(50) = NULL,
@.booktitle nvarchar(500) = NULL
WITH RECOMPILE
AS
SELECT DISTINCT
Orders.OrderID, Staff.Surname AS StaffSur,
Staff.FirstName AS StaffFir, Orders.CustomerID, Customers.Forenames,
Customers.Surname,
Customers.Telephone, Customers.Email,
Customers.Forenames, Customers.Surname, Customers.TownCity,
Websites.Name AS Website, Orders.OrdDate
FROM Orders INNER JOIN
Customers ON Orders.CustomerID = Customers.CustomerID INNER JOIN
Staff ON Orders.StaffID = Staff.StaffID INNER
JOIN
Websites ON Orders.WebsiteID = Websites.WebsiteID LEFT OUTER JOIN
Books ON Orders.OrderID = Books.OrderID
WHERE CASE @.refnumber
WHEN 0 THEN @.refnumber
ELSE Books.Ref
END
= @.refnumber
AND Customers.Surname LIKE COALESCE(@.surname, '%')
AND Books.Title LIKE COALESCE(@.booktitle, '%')
AND Customers.Postcode LIKE COALESCE(@.postcode, '%')
AND CASE @.orderid
WHEN 0 THEN @.orderid
ELSE Orders.OrderID
END
= @.orderid
My sincerest apologies for posting such a long narrative about the
problem, but I can't think of any other way of describing it! I have
included the CREATE statements for all the mentioned tables below, and
if i can provide any more information to help come up with a solution
plz ask. I am a relative newbie at SQL server, so please be gentle!!
Many thanks in advance
James Currer
CREATE TABLE [Books] (
[Ref] [int] NOT NULL ,
[Author] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
[Title] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[PlacePubDate] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[Description] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[Keywords] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
[Catalogues] [nvarchar] (80) COLLATE Latin1_General_CI_AS NULL ,
[Cost] [money] NULL ,
[DealerCode] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Notes] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[Price] [money] NULL ,
[OrderID] [int] NULL ,
[Weight] [numeric](6, 3) NULL ,
[ISBN] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Row] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Shelf] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[LocationID] [int] NULL ,
[StatusID] [int] NULL ,
[DealerID] [int] NULL ,
[BoxID] [int] NULL ,
[LastUpdatedBy] [int] NULL ,
CONSTRAINT [PK_Books] PRIMARY KEY CLUSTERED
(
[Ref]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [CancelledRefundedBooks] (
[BookRef] [int] NOT NULL ,
[RefundID] [int] NULL ,
[OrderID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [Orders] (
[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
[StaffID] [int] NOT NULL ,
[CustomerID] [int] NOT NULL ,
[OrdDate] [datetime] NULL ,
[ShipVia] [int] NULL ,
[WebsiteID] [int] NOT NULL ,
[PackingID] [int] NULL ,
[PaymentID] [int] NULL ,
[InvoiceNumber] [int] NULL ,
[InvoiceDate] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[OrderStatusID] [int] NOT NULL CONSTRAINT [DF_Orders_OrderStatusID]
DEFAULT (4),
[TotalPricePaid] [money] NOT NULL CONSTRAINT
[DF_Orders_TotalPricePaid] DEFAULT (0),
[CustomersOwnRef] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[ShippingPaid] [money] NOT NULL CONSTRAINT [DF_Orders_ShippingPaid]
DEFAULT (0.00),
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY]
) ON [PRIMARY]
GOJames,
I did not go beyond your second paragraph. I believe your db design is
flawed. If you keep an OrderID in your Book table for an active order, how
do you deal with the situation that there more than one orders that buy the
same book?
"James Currer" <phaser2001@.hotmail.com> wrote in message
news:57df26d.0307300626.667496bf@.posting.google.com...
> Hi all,
> I'm having a little trouble with producing a query for MS SQL Server
> 2000 with a conditional join in.
> The basis of the structure is that when an order is active, the
> OrderID of any book in the Order is stored in the book record in the
> Books table. If an order is cancelled, the OrderID is removed from the
> record in the Books table, to enable the book to be put back on sale.
> At that point, for each book cancelled, a record is appended to the
> CancelledRefundedBooks table, with just the book reference number
> (foreign key of the Books table), and the OrderID it belonged to. This
> is to us to still see which books were in a cancelled order. I hope
> that made sense! i've posted the table defs for these tables at the
> bottom of this message.
> My problem is when using the following stored procedure query to
> search for orders based on different criteria, included a reference
> number for a book in the order, the join with the books table is
> causing cancelled orders not to be returned. For example, if i search
> for active order 1, because one of the possible search criteria is
> book reference, the Books table is joined to the query. Because order
> 1 is active, the book records in the Books table have orderID's
> stored, and the order is returned. However, if i search for cancelled
> order 2, because the books are lacking the OrderID field, the join is
> causing the order record not to be returned. I have tried using an
> INNER JOIN and a LEFT OUTER JOIN, and neither help. The ideal
> situation would be if the join for the Books table could be
> conditional on the OrderStatusID field in the Orders table, so that
> the query would be joined to the Books table if the order was active,
> and if the order was cancelled, the join would change to the
> CancelledRefundedBooks table.
> An additional complication is that if the join was to the
> CancelledRefundedBooks table, in the event of a cancelled order, the
> CancelledRefundedBooks table holds only the book reference numbers.
> This means that table would then need a further join onto the Books
> table to retrieve author, title etc. fields.
> The stored proc as it stands is as follows:
> ALTER PROCEDURE sp_searchorders
> @.refnumber int = NULL,
> @.surname nvarchar(100) = NULL,
> @.orderid int = NULL,
> @.postcode nvarchar(50) = NULL,
> @.booktitle nvarchar(500) = NULL
> WITH RECOMPILE
> AS
> SELECT DISTINCT
> Orders.OrderID, Staff.Surname AS StaffSur,
> Staff.FirstName AS StaffFir, Orders.CustomerID, Customers.Forenames,
> Customers.Surname,
> Customers.Telephone, Customers.Email,
> Customers.Forenames, Customers.Surname, Customers.TownCity,
> Websites.Name AS Website, Orders.OrdDate
> FROM Orders INNER JOIN
> Customers ON Orders.CustomerID => Customers.CustomerID INNER JOIN
> Staff ON Orders.StaffID = Staff.StaffID INNER
> JOIN
> Websites ON Orders.WebsiteID => Websites.WebsiteID LEFT OUTER JOIN
> Books ON Orders.OrderID = Books.OrderID
> WHERE CASE @.refnumber
> WHEN 0 THEN @.refnumber
> ELSE Books.Ref
> END
> = @.refnumber
> AND Customers.Surname LIKE COALESCE(@.surname, '%')
> AND Books.Title LIKE COALESCE(@.booktitle, '%')
> AND Customers.Postcode LIKE COALESCE(@.postcode, '%')
> AND CASE @.orderid
> WHEN 0 THEN @.orderid
> ELSE Orders.OrderID
> END
> = @.orderid
> My sincerest apologies for posting such a long narrative about the
> problem, but I can't think of any other way of describing it! I have
> included the CREATE statements for all the mentioned tables below, and
> if i can provide any more information to help come up with a solution
> plz ask. I am a relative newbie at SQL server, so please be gentle!!
> Many thanks in advance
>
> James Currer
>
> CREATE TABLE [Books] (
> [Ref] [int] NOT NULL ,
> [Author] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
> [Title] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
> [PlacePubDate] [ntext] COLLATE Latin1_General_CI_AS NULL ,
> [Description] [ntext] COLLATE Latin1_General_CI_AS NULL ,
> [Keywords] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
> [Catalogues] [nvarchar] (80) COLLATE Latin1_General_CI_AS NULL ,
> [Cost] [money] NULL ,
> [DealerCode] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [Notes] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
> [Price] [money] NULL ,
> [OrderID] [int] NULL ,
> [Weight] [numeric](6, 3) NULL ,
> [ISBN] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [Row] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [Shelf] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [LocationID] [int] NULL ,
> [StatusID] [int] NULL ,
> [DealerID] [int] NULL ,
> [BoxID] [int] NULL ,
> [LastUpdatedBy] [int] NULL ,
> CONSTRAINT [PK_Books] PRIMARY KEY CLUSTERED
> (
> [Ref]
> ) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> CREATE TABLE [CancelledRefundedBooks] (
> [BookRef] [int] NOT NULL ,
> [RefundID] [int] NULL ,
> [OrderID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [Orders] (
> [OrderID] [int] IDENTITY (1, 1) NOT NULL ,
> [StaffID] [int] NOT NULL ,
> [CustomerID] [int] NOT NULL ,
> [OrdDate] [datetime] NULL ,
> [ShipVia] [int] NULL ,
> [WebsiteID] [int] NOT NULL ,
> [PackingID] [int] NULL ,
> [PaymentID] [int] NULL ,
> [InvoiceNumber] [int] NULL ,
> [InvoiceDate] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [OrderStatusID] [int] NOT NULL CONSTRAINT [DF_Orders_OrderStatusID]
> DEFAULT (4),
> [TotalPricePaid] [money] NOT NULL CONSTRAINT
> [DF_Orders_TotalPricePaid] DEFAULT (0),
> [CustomersOwnRef] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [ShippingPaid] [money] NOT NULL CONSTRAINT [DF_Orders_ShippingPaid]
> DEFAULT (0.00),
> CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
> (
> [OrderID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO

Conditional SQL Query?

Iin SQL Server 2000 I have two tables that I need to join table
A and table B. The result set is a little tricky though. Table A has a set
of columns that are duplicated in table B. The reason is if there is no
data in these columns in table A, then that means that the data "defaults"
to the same named columns in table B. There is a many-to-one relationship
from A to B. What I would like to do is build this join query such that I
would return X number of columns using alias column names. I would like the
query to be able to populate those alias columns with the column values from
table A if there is data in those columns, but if there is no data then
populate those alias columns with the column values from table B. So in
essence, I have something like this:
Table A
ID
B_ID
A_1
A_2
Table B
ID
B_1
B_2
I'd like to build a query that joins these tables on (A.B_ID = B.ID), and
return these alias columns:
COL_1: This retuns data from A_1 if data exists in this column, otherwise
returns data from B_1.
COL_2: This retuns data from A_2 if data exists in this column, otherwise
returns data from B_2.
Any help would be much appreciated.
Thanks!Try:
select
isnull (A_1, B_1)
, isnull (A2, B_2)
from
A
left join
B on B.ID = A.ID
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"epigram" <nospam@.spammy.com> wrote in message
news:1112011902. 9f7c78e9def104fa4579b49849e7cce5@.bubbane
ws...
Iin SQL Server 2000 I have two tables that I need to join table
A and table B. The result set is a little tricky though. Table A has a set
of columns that are duplicated in table B. The reason is if there is no
data in these columns in table A, then that means that the data "defaults"
to the same named columns in table B. There is a many-to-one relationship
from A to B. What I would like to do is build this join query such that I
would return X number of columns using alias column names. I would like the
query to be able to populate those alias columns with the column values from
table A if there is data in those columns, but if there is no data then
populate those alias columns with the column values from table B. So in
essence, I have something like this:
Table A
ID
B_ID
A_1
A_2
Table B
ID
B_1
B_2
I'd like to build a query that joins these tables on (A.B_ID = B.ID), and
return these alias columns:
COL_1: This retuns data from A_1 if data exists in this column, otherwise
returns data from B_1.
COL_2: This retuns data from A_2 if data exists in this column, otherwise
returns data from B_2.
Any help would be much appreciated.
Thanks!|||Hello, epigram!
You wrote on Mon, 28 Mar 2005 07:28:22 -0500:
e> I'd like to build a query that joins these tables on (A.B_ID = B.ID),
e> and return these alias columns:
e> COL_1: This retuns data from A_1 if data exists in this column,
e> otherwise returns data from B_1.
e> COL_2: This retuns data from A_2 if data exists in this column,
e> otherwise returns data from B_2.
SELECT B.ID,
COALESCE(A_1, B_1) as COL_1,
COALESCE(A_2, B_2) as COL_2,
FROM A JOIN B ON A.B_ID = B.ID
e> Any help would be much appreciated.
e> Thanks!
With best regards, Alexander Sinitsin. E-mail: al_sin[dog]ukr.net|||Did you read your last post?
http://support.microsoft.com/newsgr...n-us&sloc=en-us
AMB
"epigram" wrote:

> Iin SQL Server 2000 I have two tables that I need to join table
> A and table B. The result set is a little tricky though. Table A has a s
et
> of columns that are duplicated in table B. The reason is if there is no
> data in these columns in table A, then that means that the data "defaults"
> to the same named columns in table B. There is a many-to-one relationship
> from A to B. What I would like to do is build this join query such that I
> would return X number of columns using alias column names. I would like t
he
> query to be able to populate those alias columns with the column values fr
om
> table A if there is data in those columns, but if there is no data then
> populate those alias columns with the column values from table B. So in
> essence, I have something like this:
> Table A
> ID
> B_ID
> A_1
> A_2
> Table B
> ID
> B_1
> B_2
> I'd like to build a query that joins these tables on (A.B_ID = B.ID), and
> return these alias columns:
> COL_1: This retuns data from A_1 if data exists in this column, otherwise
> returns data from B_1.
> COL_2: This retuns data from A_2 if data exists in this column, otherwise
> returns data from B_2.
> Any help would be much appreciated.
> Thanks!
>
>|||I couldn't. For some reason, my newsreader program was telling me that the
responses to that original post were unavailabe.
Thanks.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:90602A4C-1EB2-4693-BC4C-79D35C8FA263@.microsoft.com...
> Did you read your last post?
> [url]http://support.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserv
er.programming&mid=91835d68-563b-4260-a3ae-83dfdd2e8b2e&sloc=en-us&sloc=en-us[/url
]
>
> AMB
>
> "epigram" wrote:
>

Conditional SQL Query

I'm learning SQL Server 2000. I have two tables that I need to join, table
A and table B. The result set is a little tricky though. Table A has a set
of columns that are duplicated in table B. The reason is if there is no
data in these columns in table A, then that means that the data "defaults"
to the same named columns in table B. There is a many-to-one relationship
from A to B. What I would like to do is build this join query such that I
would return X number of columns using alias column names. I would like the
query to be able to populate those alias columns with the column values from
table A if there is data in those columns, but if there is no data then
populate those alias columns with the column values from table B. So in
essence, I have something like this:
Table A
ID
B_ID
A_1
A_2
Table B
ID
B_1
B_2
I'd like to build a query that joins these tables on (A.B_ID = B.ID), and
return these alias columns:
COL_1: This retuns data from A_1 if data exists in this column, otherwise
returns data from B_1.
COL_2: This retuns data from A_2 if data exists in this column, otherwise
returns data from B_2.
Any help would be much appreciated.
Thanks!do your join and use the following in your select
select coalesce(A_1,B_1) as COL_1,coalesce(A_2,B_2) as COL_2
from ......

> COL_2: This retuns data from A_2 if data exists in this column, otherwise
> returns data from B_2.
"epigram" <nospam@.spammy.com> wrote in message
news:1111783245. cf22b6774ccf116a3d34ad39ea96b967@.bubbane
ws...
> I'm learning SQL Server 2000. I have two tables that I need to join,
> table A and table B. The result set is a little tricky though. Table A
> has a set of columns that are duplicated in table B. The reason is if
> there is no data in these columns in table A, then that means that the
> data "defaults" to the same named columns in table B. There is a
> many-to-one relationship from A to B. What I would like to do is build
> this join query such that I would return X number of columns using alias
> column names. I would like the query to be able to populate those alias
> columns with the column values from table A if there is data in those
> columns, but if there is no data then populate those alias columns with
> the column values from table B. So in essence, I have something like
> this:
> Table A
> ID
> B_ID
> A_1
> A_2
> Table B
> ID
> B_1
> B_2
> I'd like to build a query that joins these tables on (A.B_ID = B.ID), and
> return these alias columns:
> COL_1: This retuns data from A_1 if data exists in this column, otherwise
> returns data from B_1.
> COL_2: This retuns data from A_2 if data exists in this column, otherwise
> returns data from B_2.
> Any help would be much appreciated.
> Thanks!
>|||but using a LEFT OUTER JOIN.
AMB
"Denis" wrote:

> do your join and use the following in your select
> select coalesce(A_1,B_1) as COL_1,coalesce(A_2,B_2) as COL_2
> from ......
>
>
> "epigram" <nospam@.spammy.com> wrote in message
> news:1111783245. cf22b6774ccf116a3d34ad39ea96b967@.bubbane
ws...
>
>

Conditional SQL Insert Query

I have a simple ms access table with no primary key. I want to check if the value exists before it exists. I know there is way to do that directly using a insert clause without having a select statement but cannot seem to get it right.

Any help would be greatly appreciated.

Regards,

Vibhu Bansal.not sure if this is what you need but you can give it a try...

INSERT INTO <table> (field1, field2...) VALUES (value1, value2...)
WHERE (SELECT COUNT(*) FROM <table> WHERE <ColumnToCheck> = <ValueToCompare>) > 0;|||

I am looking for something similar but this gives an error in MsAccess saying semicolon expected. The semicolon is expected before "WHERE" clause!

Vibhu Bansal

|||can you post your code?|||

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

|||

Hi there,

From experience, you cannot insert WHERE clause into an INSERT statement. Period.

I discovered this in the early days when I was just learning SQL and tried using an INSERT statement instead of an UPDATE statement and got an error kicked back at me.

I think there is an IF statement for SQL but am not sure - anyone else know?

Thanks,

medicineworker

|||

Vibhu Bansal wrote:

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

Try this, use select instead of values()

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) select '05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO' where (select count(*) from tblTexas where ID='05320052')=0

|||

This does not work either...

select will require a table name or something...:)

|||Try doing a select command first i.e. select * from tblTexas where ID='05320052
then check @.@.ROWCOUNT for rows returned then the insert statement. Also Try selecting by table value rather than count. Like this:

select * from tblTexas where ID='05320052
if @.@.ROWCOUNT = 0
insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO')

Conditional SQL Insert Query

I have a simple ms access table with no primary key. I want to check if the value exists before it exists. I know there is way to do that directly using a insert clause without having a select statement but cannot seem to get it right.

Any help would be greatly appreciated.

Regards,

Vibhu Bansal.not sure if this is what you need but you can give it a try...

INSERT INTO <table> (field1, field2...) VALUES (value1, value2...)
WHERE (SELECT COUNT(*) FROM <table> WHERE <ColumnToCheck> = <ValueToCompare>) > 0;|||

I am looking for something similar but this gives an error in MsAccess saying semicolon expected. The semicolon is expected before "WHERE" clause!

Vibhu Bansal

|||can you post your code?|||

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

|||

Hi there,

From experience, you cannot insert WHERE clause into an INSERT statement. Period.

I discovered this in the early days when I was just learning SQL and tried using an INSERT statement instead of an UPDATE statement and got an error kicked back at me.

I think there is an IF statement for SQL but am not sure - anyone else know?

Thanks,

medicineworker

|||

Vibhu Bansal wrote:

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

Try this, use select instead of values()

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) select '05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO' where (select count(*) from tblTexas where ID='05320052')=0

|||

This does not work either...

select will require a table name or something...:)

|||Try doing a select command first i.e. select * from tblTexas where ID='05320052
then check @.@.ROWCOUNT for rows returned then the insert statement. Also Try selecting by table value rather than count. Like this:

select * from tblTexas where ID='05320052
if @.@.ROWCOUNT = 0
insert

into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair)

values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO')

Conditional SQL Insert Query

I have a simple ms access table with no primary key. I want to check if the value exists before it exists. I know there is way to do that directly using a insert clause without having a select statement but cannot seem to get it right.

Any help would be greatly appreciated.

Regards,

Vibhu Bansal.not sure if this is what you need but you can give it a try...

INSERT INTO <table> (field1, field2...) VALUES (value1, value2...)
WHERE (SELECT COUNT(*) FROM <table> WHERE <ColumnToCheck> = <ValueToCompare>) > 0;|||

I am looking for something similar but this gives an error in MsAccess saying semicolon expected. The semicolon is expected before "WHERE" clause!

Vibhu Bansal

|||can you post your code?|||

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

|||

Hi there,

From experience, you cannot insert WHERE clause into an INSERT statement. Period.

I discovered this in the early days when I was just learning SQL and tried using an INSERT statement instead of an UPDATE statement and got an error kicked back at me.

I think there is an IF statement for SQL but am not sure - anyone else know?

Thanks,

medicineworker

|||

Vibhu Bansal wrote:

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

Try this, use select instead of values()

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) select '05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO' where (select count(*) from tblTexas where ID='05320052')=0

|||

This does not work either...

select will require a table name or something...:)

|||Try doing a select command first i.e. select * from tblTexas where ID='05320052
then check @.@.ROWCOUNT for rows returned then the insert statement. Also Try selecting by table value rather than count. Like this:

select * from tblTexas where ID='05320052
if @.@.ROWCOUNT = 0
insert

into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair)

values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO')

Conditional Split query

Hi,

I have the following table in MsAccess

EmployeesA

empId integer,

empName varchar(60),

empAge integer,

empStatus char(1) - can be N,D or S - New, Deleted or Shifted

and the following in Sql2005

EmployeesB

Id smallint,

Name varchar(60),

Age int,

Status char(1) - Bydefault 'N'

I have written a Foreach File package that populates the sql server tables (EmployeesB) from Access(EmployeesA). However i want to check for a condition now.

If empStatus = N in EmployeesA, then insert a new record in EmployeesB

If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D'

If empStatus = S in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status as 'S' in EmployeesB and insert a new row.

How do I do it for each table each row in EmployeesA using a foreach file loop?

Thanks,

ron

If you are using a data flow inside your For Each, you can use the techniques shown in this thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1) to determine whether the row should be inserted or updated. The thread is dicussing specifically whether the row already exists or not, so you may need to add a conditional split to your data flow.

|||

Hi,

thanks for the reply. I had already seen that link. I cannot do a look up as Employees B will already contain millions of rows.

I just want to know this step by step if you could explain. I am so new to this SSIS.

How will I specify conditions :

If empStatus = N in EmployeesA, then insert a new record in EmployeesB

If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D'

If empStatus = S in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status as 'S' in EmployeesB and insert a new row.

Which control to use. Where to specify etc.

thanks,

|||

Ok,

I have figured out most of it through a friend. Just tell me this:

What i am doing is :

For status D, I do a Lookup and if found, I have to use an OLE DB Command tranform to do the update.

What query do I fire in the look up over here. If that row exists, after that what to do in the OLEDB command. ?How to pass the current row?

thanks.

|||Do you mean how to update the row that was matched in the lookup? Why can you not use the same fields you used in the lookup for the match and put them into the WHERE clause of your update? Sometimes it is cleaner to return a key column or two from the lookup, and them as basis for the WHERE clause.|||

Can you state an example. What should be in the Lookup and what in the OledbCommand based on my table.

Thanks

|||

An example, do you mean for this problem-

If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D'

I would be tempted to skip the lookup. Use a Conditional Split to get a feed of all EmployeesA rows where empStatus = "D", then use a command to do the update. Set your connection, and end the SQL statement -

UPDATE EmployeesB

SET empStatus = 'D'

WHERE empname = ?

AND age = ?

Map the two input columns empname and age to the two parameters, to complete the OLD-DB Command setup.

This avoids the costs of a lookup, which may be faster overall. If there is no match, then no update happens, which is the same overall outcome as if the lookup had failed to find anything and the command was not run.

It may be faster to use a Lookup to help filter out the non-matches, it really depends on row counts and ratios of lookup hits to misses. Test both if you are worried about performance, but it is often faster to attempt and "fail" than to prevent the "fail" in the first place in SSIS.

|||

Darren,

you know what..that worked like a charm i removed the look up and did as you said..I will try the rest and if everything works, i will close this thread. thankuuuuuu.

If you could have a look at this thread too, I will be much obliged.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2178311&SiteID=1

thanks.

Conditional sorting in order by clause

Hi,
I have a query as

select name, age, address from employee order by name

Now i want to do sorting as ASC or DESC in order by clause dynamically.

I tried something like this :-

declare @.Order int
set @.Order = 1

select name, age, address from employee
order by name
CASE
WHEN @.Order = 0 THEN ASC
WHEN @.Order = 1 THEN DESC
END

But its giving me error, Is it correct or is there any other way to do conditional sorting?

order by is usually the last statement in a query and u cant do it this way...simple way is use if-else ...

if(@.order=1)

select ...order by name desc

else

select ...order bu name asc

u may try to use dynamic sql and achive it too, but its not adviseable...

|||

Use the following query it is a conditional sorting...

Declare @.Order int
Set @.Order = 1

Selecct name, age, address from employee
Order By
CASE WHEN @.Order = 0 THEN Name End ASC,
CASE WHEN @.Order = 1 THEN Name End DESC