Concatenating fields: order

Showing posts with label order. Show all posts

Tuesday, March 27, 2012

Configuration Order

I am trying to use the SQL Agent to specify the connection properties of the connection manager that supports all of the other SQL Configurations in my packages.

It would appear that configuration files specified in the SQL agent job are applied after the other configurations. I have the same package I want to use under different configurations on the same machine. This package is also portable across to other machines. so cannot have any location information embedded in the package.

Does anyone have a solution for this?

Philip Coupar wrote:

I am trying to use the SQL Agent to specify the connection properties of the connection manager that supports all of the other SQL Configurations in my packages.
It would appear that configuration files specified in the SQL agent job are applied after the other configurations. I have the same package I want to use under different configurations on the same machine. This package is also portable across to other machines. so cannot have any location information embedded in the package.
Does anyone have a solution for this?

Are indirect configs an option? I have never used sql configs but I believe you can store the location of the configs in an environment variable. In this situation all you need to do is update an environment variable.

-Jamie

|||

I would still need to be able to set a modifier to say which configuration location I wanted to use.

I have the same package that I can use to load the same file from different customers. Obviously I want the data to end up in different databases, possibly even onto different servers. So far I have a seperate SQL configuration database for each customer, however I can only tell the package to look at one configuration database, even if I use indirect configuration, per server. And their lies the only answer I have at the moment have one configuration per server, instead of managing multiple customers in different services on the same hardware.

It would have been nice to be able to set the connection string for the configuration database from outside the SSIS package and have it applied before the other configurations kicked in. The effect I get at the moment is even though I use SQL Agent to set the connection string of the connection maanger I am using to access the SQL configuration, it seems to be applied after the other configurations are set and subsequently the other SQL configurations do not know where to get their properties from.

I hope I am wrong about this, and I have just missed something simple.

|||

I understand your problem; but not sure about the solution. Have you tried the 'SET' option available in the DTEXEC utility to override the connection string that points to your configuration table? For this you would have to use a CmdExec step in your SQL Server agent (which BTW gives you better logging information)

|||I have tried DTEXEC and using the SET options in SQL Agent, however it would also appear that these are applied after the internally specified configurations.|||

Philip Coupar wrote:
I have tried DTEXEC and using the SET options in SQL Agent, however it would also appear that these are applied after the internally specified configurations.

At some point, are you using in your packages 'Parent Package variable' for setting any of the configurations? If so, that may be the problem. There is a bug with configurations using 'parent package variables'.

I am currently using XML file to set up the connection string of my SQL Server configuration table with no problem; I have done the same using Environment variables; if this is what you are doing the problem must be something else.

|||

I had started out using Parent Package configuration until I discovered that small issue.

I now have an XML configuration file, which is working fine. However I would like to have the same packages used in multiple configurations on the same machine even using indirect configuration I can only point my packages to one xml configuration file, and even if I try to override the xml configuration file by using configurations in SQL Agent that is only apllied after the original configurations have been applied and subsequently my internal SQL configurations are missed.

It would be nice if configurations applied in SQL Agent were applied first or you could specify the order in which ALL of the configurations both internal and external were applied.

It would also be nice if parent package configurations applied in the order they were presented and not last as they do at the moment, in fact I think they are applied after validation.

|||

Phil,

There are valid reasons for the order in whch configs are applied and I read a blog psot on it once except that I can't remember where. I think parent package configs are deliberately applied at a different time to all the others - but I can't remember why. Sorry.

Defining the order in which configurations are applied sounds like a good idea to me. Perhaps you could suggest it at Connect?

-Jamie

|||

Jamie Thomson wrote:

There are valid reasons for the order in whch configs are applied and I read a blog psot on it once except that I can't remember where. I think parent package configs are deliberately applied at a different time to all the others - but I can't remember why. Sorry.

Jamie, it would be interesting to read that explanation. Actually there is an active bug open at connect:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126146

No matter where you place it in the package configuration organizer; it would be set last... May be it is a difference in opinions

Configuration Order

I am trying to use the SQL Agent to specify the connection properties of the connection manager that supports all of the other SQL Configurations in my packages.

Does anyone have a solution for this?

Philip Coupar wrote:

I am trying to use the SQL Agent to specify the connection properties of the connection manager that supports all of the other SQL Configurations in my packages.
It would appear that configuration files specified in the SQL agent job are applied after the other configurations. I have the same package I want to use under different configurations on the same machine. This package is also portable across to other machines. so cannot have any location information embedded in the package.
Does anyone have a solution for this?

-Jamie

|||

I would still need to be able to set a modifier to say which configuration location I wanted to use.

I hope I am wrong about this, and I have just missed something simple.

|||

|||I have tried DTEXEC and using the SET options in SQL Agent, however it would also appear that these are applied after the internally specified configurations.|||

Philip Coupar wrote:
I have tried DTEXEC and using the SET options in SQL Agent, however it would also appear that these are applied after the internally specified configurations.

|||

I had started out using Parent Package configuration until I discovered that small issue.

It would be nice if configurations applied in SQL Agent were applied first or you could specify the order in which ALL of the configurations both internal and external were applied.

It would also be nice if parent package configurations applied in the order they were presented and not last as they do at the moment, in fact I think they are applied after validation.

|||

Phil,

Defining the order in which configurations are applied sounds like a good idea to me. Perhaps you could suggest it at Connect?

-Jamie

|||

Jamie Thomson wrote:

There are valid reasons for the order in whch configs are applied and I read a blog psot on it once except that I can't remember where. I think parent package configs are deliberately applied at a different time to all the others - but I can't remember why. Sorry.

Jamie, it would be interesting to read that explanation. Actually there is an active bug open at connect:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126146

No matter where you place it in the package configuration organizer; it would be set last... May be it is a difference in opinions

|||And another open bug for the same issue :

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126797

Scott.

Configuration in SQLCLR Assembly

Hi,

I have an SQLCLR assembly which is required to connect to a remote WCF service. In order to do this in a host such as IIS you would need to store all your WCF configuration data (endpoints, types etc) in the Web.config or App.config (in a windows forms app). This begs the question: Where do you store configuration data for SQLCLR assemblies?

The System.Configuration assembly is available in the SQLCLR, but this assumes a Web or App config files exists? Does SQL have its own config file that you can keep your settings in which is called when the assembly is running from within the SQL process?

If this is not possible, should I rather be storing configuration data in the database itself?

This particular example relates to WCF configuration but is relevant for assemblies using Enterprise Library which is also config driven.

Any help would be appreciated.

Chris

You can use a config file with SQLCLR. Create a config file sqlservr.exe.config and place it in the same directory as the SQL Server executable (sqlservr.exe). Whatever is in that file will be parsed in when an appdomain is loaded.

However, config files are not supported as such, and there is no guarantee it will work in the future. In addition, there is no way to differentiate between config ettings for different databases.

So, use the best storage facility there is - the database.

Niels
|||

Hi Niels,

Thank you for your response. I will test out the sqlservr.exe.config today. I agree that as the application will be running in SQL that the best storage spce is the DB, this however does not solve the problem where you are using tools like EntLib or Technologies like WCF where they rely specifically on configuration files. I am not aware of anyway to make WCF or EntLib collect configuration data from a sql database rather than the Application configuration file.

Chris

Sunday, March 25, 2012

config creating havoc

I have created two package configurations

Each identical apart from the name (dev/prod) [They appear in that order in the 'configurations..' dialog]

I then manually edited the prod config (in notepad) to the correct values (connection string etc)

I then opened the config file in the config edit tool (some time later) to note that my changes had somehow been overwritten. I changed them back to the correct values and saved the prod config.

I then tried building the package.

It returned with numerous validation errors despite 'delay validation' being set to true on each of the tasks that were causing problems (they create temp tables which it cannot validate against)

I then tried running the package.

It errored.

It was trying to connect to production!!

How on earth is this possible?
According to the configuration dialog, the 'dev' configuration is the first one loaded.
I haven't told it anywhere to use 'prod'.
I haven't even (within ssis) managed to even change the values (connection strings) to production.

What is going on here?It would appear that subsequent entries in the 'package configurations organizer' overwrite initials ones.

e.g.
config1 value1=abc
config2 value1=def

the package would have value1=def

so, what exactly is the point of being able to load multiple configurations if it is just going to pick up the last one regardless?|||

The point of superceding configured properties, so far as I use it, to go from generalization to specialization.

A given configuration might be shared by 50 packages. However, for one or two of those packages, the configured value is not appropriate, must be specifialized, and hence, an "subclassed" configuration is added, which overrides the base, or precediing configuration.

|||ok, I can see how that could work but...

If I have a package and want to store the connection string for dev/test/prod in the same package, how can I then test this in VS.net?

Other than manually adding and deleting them each time?

also am getting the following error when trying to build:
Creating deployment utility...
Error : System.ApplicationException: Could not copy file "xxxx.dtsConfig" to the deployment utility output directory "yyyyyyy". > System.IO.IOException: The file 'xxxxxx' already exists.

I cannot see any kind of 'overwrite' flag in the project - deploy properties (like you get in reporting services)|||do not add config files into the misc folder in the project - this was causing the 'already exists' error

It obviously decides to deploy all the misc files. How stupid of me to include config files in this folder. They are only completely 100% relevant to the project after all.|||

You don't need to build anything, though. For that matter, deploying is a bit of a stretch as well. The .dtsx and .dtsconfig files are all you need. You can simply copy them to the server via a mapped drive, or use the import (or for that matter, Save Copy As... in BIDS) in Management Studio when connected to Integration Services if you prefer to store packages in MSDB.

As to your question as to dev, test, and prod, simply create three config files, name them slightly differently than each other. Then point your package to an environment-neutral named config file. To test, simply copy one of your dev, test, or prod config files (probably starting with dev) to the name that you have the package pointing to. When you want to switch environments, repeat the process using a different config file this time (perhaps test's file)

Monday, March 19, 2012

Conditional Union!

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!

Sunday, March 11, 2012

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

Conditional query on ASPNET page

Dear Friends,

I am working on search customer information page.

I have 5 search options,

Name,

Email,

Order Number,

Product Name,

Order Date

I am using check boxes, I need to allow admin to enter above information and click on search,

How will I make my query and sends to DB server to pull up records which satisfies where clause:

For example,

Select * from orders where Email = #Email#

This is simple, but I can not hardcode all queries, I don’t know in advance what different search option ADMIN may choose.

Any suggestion for logic or query make up,

Thanks,

Fahim.

So here are a couple suggestions that might help you with your task:

1. Look into calling system stored procedure sp_columns (http://msdn2.microsoft.com/en-us/library/ms176077.aspx) on the table to get the column metadata information.

Using this information you can then dynamically generate a query based on the check boxes selected by the user.

2, If you schema is simple and you already know the column information, then all you need to do is have a string that concatenates differect WHERE clauses based on the options selected and then submit the query when the user hits the search button.

HTH,

Thursday, March 8, 2012

Conditional outer join help

I need to do an outer join in order to retrieve info in cases where some
table data does not exist, but doing this causes my query to return rows I
don't want for cases where data does exist in all tables. I have simplified
the problem as follows:
Say my data looks like this:
Table A Table B Table C
ID Code ID Code ID Code
1 11 11 A 11 X
1 12 12 A 12 Y
1 13 13 B 13 Z
2 21 21 C 21 X
My query needs to return data from other tables, which then join to Table A
on ID. I'm only interested in data from Table B and C where B.code = A and
C.code = Y. So effectively what I want returned from this part of the query
is
A.ID A.Code B.ID B.Code C.ID C.Code
1 12 12 A 12 Y
null null null null null null (where the null row is
from A.ID = 2)
The problem is, to get the null row returned from A.ID = 2, I have to do the
following:
select *
from A
left outer join B
on A.code = B.ID
and B.code = 'A'
left outer join C
on B.ID = C.ID
and C.code = 'Y'
But this means that I also get two extra rows returned for when A.ID = 1.
To get the correct rows returned for A.ID = 1, I need to do the following:
select *
from A
left outer join B
on A.code = B.ID
left outer join C
on B.ID = C.ID
where B.code = 'A'
and C.code = 'Y'
How can I do both?
Thanks in advance.Easy: replace the second outer join by an inner join between B and C. (I'm
not sure if you will have to put it into a subquery.)
Another possibility would be to put your first solution into a subquery
itself but this is a much less elegant solution.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
"janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
news:9E9A95BD-779A-4BCF-81A9-0FBE604FD45C@.microsoft.com...
>I need to do an outer join in order to retrieve info in cases where some
> table data does not exist, but doing this causes my query to return rows I
> don't want for cases where data does exist in all tables. I have
> simplified
> the problem as follows:
> Say my data looks like this:
> Table A Table B Table C
> ID Code ID Code ID Code
> 1 11 11 A 11 X
> 1 12 12 A 12 Y
> 1 13 13 B 13 Z
> 2 21 21 C 21 X
> My query needs to return data from other tables, which then join to Table
> A
> on ID. I'm only interested in data from Table B and C where B.code = A
> and
> C.code = Y. So effectively what I want returned from this part of the
> query
> is
> A.ID A.Code B.ID B.Code C.ID C.Code
> 1 12 12 A 12 Y
> null null null null null null (where the null row
> is
> from A.ID = 2)
> The problem is, to get the null row returned from A.ID = 2, I have to do
> the
> following:
> select *
> from A
> left outer join B
> on A.code = B.ID
> and B.code = 'A'
> left outer join C
> on B.ID = C.ID
> and C.code = 'Y'
> But this means that I also get two extra rows returned for when A.ID = 1.
> To get the correct rows returned for A.ID = 1, I need to do the following:
> select *
> from A
> left outer join B
> on A.code = B.ID
> left outer join C
> on B.ID = C.ID
> where B.code = 'A'
> and C.code = 'Y'
> How can I do both?
> Thanks in advance.|||Thanks Sylvain but inner joining between B and C eliminates the row of all
nulls that I need returned for A.ID = 2. I'm not sure how putting this in a
subquery would help? Can you be more specific?
Result after outer joining A and B on A.code = B.ID and B.code = 'A' is:
A.ID A.Code B.ID B.Code
1 11 11 A
1 12 12 A
null null null null (A.ID was 1)
null null null null (A.ID was 2)
So you can see that inner joining this to C on B.ID = C.ID is not going to
return the null rows. What am I missing in your explanation?
"Sylvain Lafontaine" wrote:

> Easy: replace the second outer join by an inner join between B and C. (I'
m
> not sure if you will have to put it into a subquery.)
> Another possibility would be to put your first solution into a subquery
> itself but this is a much less elegant solution.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
>
> "janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
> news:9E9A95BD-779A-4BCF-81A9-0FBE604FD45C@.microsoft.com...
>
>|||Sorry, but your repetition of the same names (ID and CODE) for differents
values may have mixed my little head. I've just took a little time to write
a test database with your data and here a query that I have made by
transforming your second query into a subquery and use it with an outer join
to the distinct values from A:
Select R.*, S.* from
(Select Distinct Id from A) as R Left outer join
(select A.Id as AId, A.Code as ACode, B.Id as BId, B.Code as BCode, C.Id as
CId, C.Code as CCode
from A left outer join B on A.code = B.ID
left outer join C on B.ID = C.ID
where (B.Code is Null and C.Code is Null)
or (B.code = 'A' and C.code = 'Y')
) as S
On R.Id = S.AId
and here are the result:
1 1 12 12 A 12 Y
2 NULL NULL NULL NULL NULL NULL
The first column is a new column that I have added and it simply gives the
list of distinct values for the ID of A. With the exception of this column,
this is exactly the result that you have asked for in your first post. I
have also added alias because of the multiple repetition of ID and CODE with
different meanings in the three tables.
Of course, we see that the two LEFT OUTER JOIN in the subqueries S are
useless and can be probably replaced with INNER JOIN to give the same
results but I'm not sure if this is the case for you because I don't know
enough about your real data for the rest of the tables.
There are probably other possibilities, too but now, it's getting to late
for me.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
"janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
news:3FBA7F59-9770-414D-8791-9B44631BCD33@.microsoft.com...
> Thanks Sylvain but inner joining between B and C eliminates the row of all
> nulls that I need returned for A.ID = 2. I'm not sure how putting this in
> a
> subquery would help? Can you be more specific?
> Result after outer joining A and B on A.code = B.ID and B.code = 'A' is:
> A.ID A.Code B.ID B.Code
> 1 11 11 A
> 1 12 12 A
> null null null null (A.ID was 1)
> null null null null (A.ID was 2)
> So you can see that inner joining this to C on B.ID = C.ID is not going to
> return the null rows. What am I missing in your explanation?
> "Sylvain Lafontaine" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Right now you have a magical "id" that does not tell us what it
identifies and a magical "code' that modeled as both strings and
numerics in the same schema. Then you have code and id are equi-joined
together. Here is my guess at what you might have meant to say:
CREATE TABLE Foobar
(foo_grp INTEGER NOT NULL,
member_id INTEGER NOT NULL PRIMARY KEY);
CREATE TABLE Foo
(member_id INTEGER NOT NULL PRIMARY KEY
REFERENCES Foobar(member_id),
foo_score CHAR(1) NOT NULL);
CREATE TABLE Bar
(member_id INTEGER NOT NULL PRIMARY KEY
REFERENCES Foobar(member_id),
bar_score CHAR(1) NOT NULL);
Table A on ID. I'm only interested in data from Table B and C where
B.code = A and C.code = Y. <<
SELECT foo_grp, member_id, 'A' AS bar_score, 'Y' AS foo_score
FROM Foobar
WHERE EXISTS
(SELECT *
FROM Foo AS F1, Bar AS B1
WHERE F1.member_id = Foobar.member_id
AND B1.member_id = Foobar.member_id
AND bar_score = 'A'
AND foo_score = 'Y');|||Hi Sylvain,
I really appreciate your help on this. Sorry that my attempt at
simplification has made things confusing. The real tables have hideous long
names and the referential integrity between them is full of holes.
I have tried to apply your solution but I don't understand how your inner
query generates the null column values. As you say, the outer joins in the
subqueries are effectively the same as inner joins. Therefore, there will b
e
no null column values in the S table. I tried adding the restrictions to th
e
inner joins (i.e. 'from A left outer join B on A.code = B.id and B.code = 'A
'
left outer join C on B.ID = C.ID and C.code = 'Y') but, of course, this mean
s
I will always get an extra row of nulls for the A.ID = 1 row. i.e. my resul
t
set would be (using your leading row of distinct values from A):
1 1 12 12 A 12 Y
1 NULL NULL NULL NULL NULL NULL
2 NULL NULL NULL NULL NULL NULL
Jane
"Sylvain Lafontaine" wrote:

> Sorry, but your repetition of the same names (ID and CODE) for differents
> values may have mixed my little head. I've just took a little time to wri
te
> a test database with your data and here a query that I have made by
> transforming your second query into a subquery and use it with an outer jo
in
> to the distinct values from A:
> Select R.*, S.* from
> (Select Distinct Id from A) as R Left outer join
> (select A.Id as AId, A.Code as ACode, B.Id as BId, B.Code as BCode, C.Id a
s
> CId, C.Code as CCode
> from A left outer join B on A.code = B.ID
> left outer join C on B.ID = C.ID
> where (B.Code is Null and C.Code is Null)
> or (B.code = 'A' and C.code = 'Y')
> ) as S
> On R.Id = S.AId
> and here are the result:
> 1 1 12 12 A 12 Y
> 2 NULL NULL NULL NULL NULL NULL
> The first column is a new column that I have added and it simply gives the
> list of distinct values for the ID of A. With the exception of this colum
n,
> this is exactly the result that you have asked for in your first post. I
> have also added alias because of the multiple repetition of ID and CODE wi
th
> different meanings in the three tables.
> Of course, we see that the two LEFT OUTER JOIN in the subqueries S are
> useless and can be probably replaced with INNER JOIN to give the same
> results but I'm not sure if this is the case for you because I don't know
> enough about your real data for the rest of the tables.
> There are probably other possibilities, too but now, it's getting to late
> for me.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
>
> "janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
> news:3FBA7F59-9770-414D-8791-9B44631BCD33@.microsoft.com...
>
>|||Hi Jane,
The null column values are not generated by the inner query but by the
Left Outer Join of the outer query.
Excerpt for the aliases, the big inner query is the same as your second
query in your first post and this query generate only one line. The other
inner query (the small one: (Select Distinct Id from A) ) generate only
two lines with two values: 1 and 2 and combined as a Left Outer Join to the
other inner query can give only two big lines: the first one with values
other than null and the other one with all null values.
Maybe you could post here the query that you have tried and that gives
three lines instead of two.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
"janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
news:71FC1170-6EA1-4F51-BB8F-A231E69513A0@.microsoft.com...
> Hi Sylvain,
> I really appreciate your help on this. Sorry that my attempt at
> simplification has made things confusing. The real tables have hideous
> long
> names and the referential integrity between them is full of holes.
> I have tried to apply your solution but I don't understand how your inner
> query generates the null column values. As you say, the outer joins in
> the
> subqueries are effectively the same as inner joins. Therefore, there will
> be
> no null column values in the S table. I tried adding the restrictions to
> the
> inner joins (i.e. 'from A left outer join B on A.code = B.id and B.code =
> 'A'
> left outer join C on B.ID = C.ID and C.code = 'Y') but, of course, this
> means
> I will always get an extra row of nulls for the A.ID = 1 row. i.e. my
> result
> set would be (using your leading row of distinct values from A):
> 1 1 12 12 A 12 Y
> 1 NULL NULL NULL NULL NULL NULL
> 2 NULL NULL NULL NULL NULL NULL
> Jane
> "Sylvain Lafontaine" wrote:
>|||Hi,
Okay I see what you mean. I have used a version of your solution and it
works although I have to repeat a large query. I feel like there should be
a
better way but I don't want to post the actual query. It's too large and th
e
relationships between the tables are too hard to see.
Thanks for your help.
jane
"Sylvain Lafontaine" wrote:

> Hi Jane,
> The null column values are not generated by the inner query but by the
> Left Outer Join of the outer query.
> Excerpt for the aliases, the big inner query is the same as your secon
d
> query in your first post and this query generate only one line. The other
> inner query (the small one: ? (Select Distinct Id from A) ? ) generate o
nly
> two lines with two values: 1 and 2 and combined as a Left Outer Join to th
e
> other inner query can give only two big lines: the first one with values
> other than null and the other one with all null values.
> Maybe you could post here the query that you have tried and that gives
> three lines instead of two.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
>
> "janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
> news:71FC1170-6EA1-4F51-BB8F-A231E69513A0@.microsoft.com...
>
>|||You can use a temporary table or a table variable to store the result of
this large query and avoid repeating it.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
"janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
news:CD3584C7-3B1A-4010-95DD-F1722B2800CC@.microsoft.com...
> Hi,
> Okay I see what you mean. I have used a version of your solution and it
> works although I have to repeat a large query. I feel like there should
> be a
> better way but I don't want to post the actual query. It's too large and
> the
> relationships between the tables are too hard to see.
> Thanks for your help.
> jane
>
> "Sylvain Lafontaine" wrote:
>|||And finally, if you query is really complex, then the use of other options
like Exists() could be your best solution.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:%23UcmOnIYFHA.796@.TK2MSFTNGP09.phx.gbl...
> You can use a temporary table or a table variable to store the result of
> this large query and avoid repeating it.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
>
> "janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
> news:CD3584C7-3B1A-4010-95DD-F1722B2800CC@.microsoft.com...
>

Conditional Order by?

Is there a way to do a conditional order by so that a user can give a parameter to a stored proc and it give back results sorted the way they want?

I want it so that the user can do 1 of 4 things,
* sort by "title" ascending,
* sort by "title" descending,
* sort by "synopsis" ascending,
* sort by "synopsis" descending

Can it be done? This is what I have but I get a syntax error:

select * from Blah

Order By
Case
when @.orderId = 1 then title asc
when @.orderId = 2 then title desc
when @.orderId = 3 then synopsis asc
when @.orderId = 4 then synopsis desc
end

Any help is greatly appreciated!

You need to split the query as ASC Query & Desc Query.

You can choose your order by columns (if it is single column) dynamically but Sorting Order you can't.

Use your query as follow as

if @.OrderId in (1,3)

select * from Blah
Order By
Case
when @.orderId = 1 then title
when @.orderId = 3 then synopsis
end asc

else

select * from Blah
Order By
Case
when @.orderId = 2 then title
when @.orderId = 4 then synopsis
end Desc

You can use dynamic SQL

Declare @.SQL as NVarchar(1000)

Select @.SQL = N'select * from Blah

Order By ' +
Case
when @.orderId = 1 then 'title asc'
when @.orderId = 2 then 'title desc'
when @.orderId = 3 then 'synopsis asc'
when @.orderId = 4 then 'synopsis desc'
end

Exec (@.SQL)

|||Why don't you dynamically add the final order by clause to the select query string ?
I guess it is more clear to understand and much more flexible for any future changes.|||Thanks both for the replies, I didnt know it would be so detailed, I have a rather large select query that I am applying this to and I dont really want to create a string then execute it, it just looks messy to me. But I guess if I have no option I guess ill have to.

Thanks again|||In some cases I have done this:

SELECT ....,
SortOrder = Case when @.orderId = 1 then title
when @.orderId = 2 then REVERSE(TITLE)
when @.orderId = 3 then synopsis
when @.orderId = 4 then REVERSE(synopsis) END
ORDER BY SortOrder

The only issue with this is all the vars in the CASE must be the same type, or cast to a certain type.

|||

Easiest is to do below:

order by

case @.orderId when 1 then title end

, case @.orderId when 2 then title end desc

, case @.orderId when 3 then synopsis end

, case @.orderId when 4 then synopsis end desc

Note that you will get the best performance (assuming you have indexes on the column(s) and the plan can use it) if you use dynamic SQL to form the ORDER BY with required columns or use different SELECT statements. But in most cases, I have found that the above construct is easier to use, safe from SQL injection (dynamic SQL is prone to it if you are not careful) and with few conversions which single CASE expression requires.

|||Thanks all for the help with this topic, I think Umachandar's answer will fit my solution best.

Conditional Order By Stored Procedure

I need to create a conditional if or case statement in SQL Server 2000
for a stored procedure. Basically if the value passed in is 1,2 or 3 then
it will order by either NEWID(), a text field or a datetime feild.
Not done much dynamic sql so any help would be appreciated.
Fuzzy
The approach I typically take is this:

SELECT
someColumns
FROM
someTable
WHERE
CASE WHEN @.sortValue = 1 THEN NEWID() END,
CASE WHEN @.sortValue = 2 THEN someTextColumn END,
CASE WHEN @.sortValue = 3 THEN someDateTimeColumn END,
defaultSortColumn -- just in case the @.sortValue is not 1, 2, or 3, Iknow the results will be sorted by *something*

|||My full stored procedure is listed below but i assume i have to specify the order
by clause somewhere it keeps returning a incorrect syntax near CASE error message
The sproc
CREATE PROCEDURE [dbo].[sp_call_accomSearch]
(
@.accomType As Int,
@.sgleroom As Int,
@.dbleroom As Int,
@.twinroom As Int,
@.tripleroom As Int,
@.Garage As Int,
@.Phone As Int,
@.Altitude As Int,
@.CarPark As Int,
@.Tv As Int,
@.TownCentre As Int,
@.SwimPool As Int,
@.Radio As Int,
@.NearSlopes As Int,
@.DgsAdmit As Int,
@.Safe As Int,
@.CrossCtry As Int,
@.SuitDisable As Int,
@.Balcony As Int,
@.OnTLake As Int,
@.Solarium As Int,
@.Suite As Int,
@.QutZone As Int,
@.BeautyCb As Int,
@.Minibar As Int,
@.Tennis As Int,
@.WhirlPl As Int,
@.Elevator As Int,
@.Sauna As Int,
@.PriceRgLow As Int,
@.PriceRgHigh As Int,
@.DateFromTotal As DateTime,
@.DateToTotal As DateTime,
@.selfcatering As Int,
@.halfboard As Int,
@.fullboard As Int,
@.roomphone As Int,
@.Suitdisableroom As Int,
@.CountryID As Int,
@.OrderBy As Int
)
AS
SELECT
tblaccommodation.accommodationID,
tblaccommodation.[name],
tblaccommodation.address1,
tblaccommodation.address2,
tblaccommodation.town,
tblaccommodation.postcode,
tblaccommodation.country,
tblaccommodation.email,
tblaccommodation.contact,
tblaccommodation.editorial,
(SELECT [name] FROM tblresort WHERE resortID = resortname) As ResortName,
(SELECT SUM(sgleroom) As sgleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) As SgleRoomTotal,

(SELECT SUM(dbleroom) As dbleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) As dbleRoomTotal,
(SELECT SUM(twinroom) As twinroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) As twinRoomTotal,
(SELECT SUM(tripleroom) As tripleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) As tripleRoomTotal
FROM
tblaccommodation
WHERE

tblaccommodation.accomType = case
when @.accomType = 1 then @.accomType
else tblaccommodation.accomType
end AND

(SELECT SUM(sgleroom) As sgleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) >= @.Sgleroom AND

(SELECT SUM(dbleroom) As dbleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) >= @.dbleroom AND
(SELECT SUM(twinroom) As twinroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) >= @.twinroom AND
(SELECT SUM(tripleroom) As tripleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) >= @.tripleroom AND

tblaccommodation.Garage = case
when @.Garage <> 0 then @.Garage
else tblaccommodation.Garage
end AND
tblaccommodation.Phone = case
when @.Phone <> 0 then @.Phone
else tblaccommodation.Phone
end AND
tblaccommodation.Altitude = case
when @.Altitude <> 0 then @.Altitude
else tblaccommodation.Altitude
end AND
tblaccommodation.Carpark = case
when @.Carpark <> 0 then @.Carpark
else tblaccommodation.Carpark
end AND

tblaccommodation.TownCentre = case
when @.TownCentre <> 0 then @.TownCentre
else tblaccommodation.TownCentre
end AND
tblaccommodation.SwimPool = case
when @.SwimPool <> 0 then @.SwimPool
else tblaccommodation.SwimPool
end AND
tblaccommodation.NearSlopes = case
when @.NearSlopes <> 0 then @.NearSlopes
else tblaccommodation.NearSlopes
end AND
tblaccommodation.DgsAdmit = case
when @.DgsAdmit <> 0 then @.DgsAdmit
else tblaccommodation.DgsAdmit
end AND
tblaccommodation.Safe = case
when @.Safe <> 0 then @.Safe
else tblaccommodation.Safe
end AND
tblaccommodation.CrossCtry = case
when @.CrossCtry <> 0 then @.CrossCtry
else tblaccommodation.CrossCtry
end AND
tblaccommodation.SuitDisable = case
when @.SuitDisable <> 0 then @.SuitDisable
else tblaccommodation.SuitDisable
end AND
tblaccommodation.OnTLake = case
when @.OnTLake <> 0 then @.OnTLake
else tblaccommodation.OnTLake
end AND
tblaccommodation.Solarium = case
when @.Solarium <> 0 then @.Solarium
else tblaccommodation.Solarium
end AND
tblaccommodation.QutZone = case
when @.QutZone <> 0 then @.QutZone
else tblaccommodation.QutZone
end AND
tblaccommodation.BeautyCb = case
when @.BeautyCb <> 0 then @.BeautyCb
else tblaccommodation.BeautyCb
end AND
tblaccommodation.Tennis = case
when @.Tennis <> 0 then @.Tennis
else tblaccommodation.Tennis
end AND
tblaccommodation.Whirlpl = case
when @.Whirlpl <> 0 then @.Whirlpl
else tblaccommodation.Whirlpl
end AND
tblaccommodation.Elevator = case
when @.Elevator <> 0 then @.Elevator
else tblaccommodation.Elevator
end AND
tblaccommodation.Sauna = case
when @.Sauna <> 0 then @.Sauna
else tblaccommodation.Sauna
end AND

tblaccommodation.PriceRgLow >= @.PriceRgLow AND
tblaccommodation.PriceRgLow <= @.PriceRgHigh AND
tblaccommodation.PriceRgHigh <= @.PriceRgHigh AND

tblaccommodation.FromDT >= @.DateFromTotal AND
tblaccommodation.ToDT <= @.DateToTotal AND
tblaccommodation.selfcatering = case
when @.selfcatering<> 0 then @.selfcatering
else tblaccommodation.selfcatering
end AND
tblaccommodation.halfboard = case
when @.halfboard<> 0 then @.halfboard
else tblaccommodation.halfboard
end AND
tblaccommodation.fullboard = case
when @.fullboard<> 0 then @.fullboard
else tblaccommodation.fullboard
end AND
tblaccommodation.Country = @.CountryID AND
tblaccommodation.displayAcc = '1'
CASE WHEN @.OrderBy = 1 THEN NEWID() END,
CASE WHEN @.OrderBy = 2 THEN name END,
CASE WHEN @.OrderBy = 3 THEN FromDT END,
GO
|||First, I made a mistake in my example code. I mistakenly used aWHERE clause instead of an ORDER BY clause. Sorry to be confusing:-(
You are missing the ORDER BY, and you had an unneeded comma at the end. Try:

tblaccommodation.displayAcc = '1'
ORDER BY
CASE WHEN @.OrderBy = 1 THEN NEWID() END,
CASE WHEN @.OrderBy = 2 THEN name END,
CASE WHEN @.OrderBy = 3 THEN FromDTEND

|||LOL ... i am confused most of the time anyway usually why i am on here so much
I am still getting an sql error still when i try to check the syntax of the query in question any
thoughts what is going wrong here not found anything on google groups like it yet. I have
listed the error below.
Fuzzy

Microsoft SQL-DMO (ODBC SQLState: 42000)
Error 1008: The SELECT item identified by the ORDER BY number 1 contains a variable as
part of the expression identfying a column position. Variables are only allowed when ordering
by an expression referencing a column name
|||Hmmm, it's not liking the NEWID(). I suggest this as aworkaround, but it will force NEWID() to be generated for each row inthe resultset, which *might* be a performance hit if you have a lot ofrows:
ORDER BY
CASE WHEN @.OrderBy = 2 THEN name END,
CASE WHEN @.OrderBy = 3 THEN FromDTEND,
NEWID()
|||

You Can also do:

SELECT [all of your fields]
FROM (
SELECT [all of your fields], newID() as ID
FROM ...
) X
ORDER BY
CASE WHEN @.orderBy = 1 THEN ID END,
CASE WHEN @.orderBy = 2 THEN name END,
CASE WHEN @.orderBy = 3 THEN FromDT END
While either version will work for you, I think this one might be a little cleaner so if someone else needs to update it, they can see exactly how it is supposed to use the new ID (if orderBy is 1).
Just my 1.50
Nick

|||

nick-w wrote:
You Can also do:
SELECT [all of your fields]
FROM (
SELECT [all of your fields], newID() as ID
FROM ...
) X
ORDER BY
CASE WHEN @.orderBy = 1 THEN ID END,
CASE WHEN @.orderBy = 2 THEN name END,
CASE WHEN @.orderBy = 3 THEN FromDT END
Whileeither version will work for you, I think this one might be a littlecleaner so if someone else needs to update it, they can see exactly howit is supposed to use the new ID (if orderBy is 1).

I had thought about that, but had become concerned that NEWID()would be calculated for every row in every table, not just for theresultset. That sent me on a fruitless hunt to find a referencefor the processing sequence of all of the different portions of aSELECT statement so that I could confirm or deny that concern. When I couldn't find a reference (does anyone have one?) , I decidedon the approach I recommended. But, with that being said, I muchprefer the approach you've recommended as it doesn't make assumptionsand as you said it is cleaner. :-)

Conditional 'Order By' statement

I have a table that stores three columns of data, namely
1. Order_ID
2. Exam_Start_Date
3. Order_Received_Date
I want to order the records as follows:
If the Exam_Start_Date is within the next 10 days or past, then order by the
exam_start_date. Otherwise order by the order_Received_Date. Therefore the
result set should display records where the exam_start_date is within next 10
days first, then display all other records.
I have tried the following SQL but it doesnt appear to work (i.e. Order of
the records is not exam_start_date (if within next 10 days), otherwise
Order_Received_Date.
select order_id, order_received_date, exam_start_date
from orders
ORDER BY CASE WHEN Exam_Start_date < '20050118' THEN Exam_Start_Date ELSE
Order_Received_Date END
Any suggestions greatly appreciated!
Wes.
On Sat, 7 Jan 2006 18:51:02 -0800, Wez wrote:

>I have a table that stores three columns of data, namely
>1. Order_ID
>2. Exam_Start_Date
>3. Order_Received_Date
>I want to order the records as follows:
>If the Exam_Start_Date is within the next 10 days or past, then order by the
>exam_start_date. Otherwise order by the order_Received_Date. Therefore the
>result set should display records where the exam_start_date is within next 10
>days first, then display all other records.
>I have tried the following SQL but it doesnt appear to work (i.e. Order of
>the records is not exam_start_date (if within next 10 days), otherwise
>Order_Received_Date.
>select order_id, order_received_date, exam_start_date
>from orders
>ORDER BY CASE WHEN Exam_Start_date < '20050118' THEN Exam_Start_Date ELSE
>Order_Received_Date END
>Any suggestions greatly appreciated!
Hi Wes,
Try if this works better:
ORDER BY CASE WHEN Exam_Start_date < '20050118'
THEN Exam_Start_Date
ELSE '20050118'
END DESC,
Order_Received_Date DESC
Or, a more generic version:
ORDER BY CASE WHEN Exam_Start_date < DATEADD(day,-10, CURRENT_TIMESTAMP)
THEN Exam_Start_Date
ELSE DATEADD(day,-10, CURRENT_TIMESTAMP)
END DESC,
Order_Received_Date DESC
Hugo Kornelis, SQL Server MVP

Conditional Join

I want to join 2 tables conditionally. One order needs to join with one
instruction. The three potential join fields are: Country, Exchange, and
Type. These fields are required in the Orders table but only Country is
required in Instructions. The data entry requirements of the application are
such that if an instruction has a Type it must have an Exchange.
The logic of the join is that:
1-if all three fields match, Country, Exchange, and Type then join those
records.
2-if two fields match, Country, and Exchange then join those records.
3-if one field matches, Country, then join those records.
My expected results given the sample data is as follows.
SELECT OrderID,InstructionID FROM Orders
JOIN ...
--Expected Results
OrderID,InstructionID
1,1
2,5
3,5
4,7
5,8
6,9
7,13
CREATE TABLE Orders
(
OrderID int NOT NULL,
Country char (3)NOT NULL,
Exchange char (3)NOT NULL,
Type char (3)NOT NULL,
)
CREATE TABLE Instructions
(
InstructionID int NOT NULL,
Country char (3) NOT NULL,
Exchange char (3) NULL,
Type char (3) NULL,
Instructions varchar (15)NOT NULL
)
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (1,'USA','NYS','Buy')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (2,'CAN','TSE','Buy')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (3,'CAN','TSE','Sel')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (4,'ESP','BAR','Buy')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (5,'ESP','MAD','Buy')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (6,'IRQ','BAG','Buy')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (7,'DUE','HAM','Buy')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(1,'USA','NYS','Buy','Instruction 1')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(2,'USA','NYS','Sel','Instruction 2')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(3,'USA','NYS',NULL,'Instruction 3')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(4,'USA',NULL,NULL,'Instruction 4')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(5,'CAN','TSE',NULL,'Instruction 5')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(6,'CAN','ALB',NULL,'Instruction 6')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(7,'ESP',NULL,NULL,'Instruction 7')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(8,'ESP','MAD',NULL,'Instruction 8')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(9,'IRQ','BAG','Buy','Instruction 9')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(10,'IRQ','BAG','Sel','Instruction 10 ')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(11,'DUE',NULL,NULL,'Instruction 11')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(12,'DUE','HAM',NULL,'Instruction 12')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(13,'DUE','HAM','Buy','Instruction 13')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(14,'DUE','HAM','Sel','Instruction 14')Terri (terri@.cybernets.com) writes:
> I want to join 2 tables conditionally. One order needs to join with one
> instruction. The three potential join fields are: Country, Exchange, and
> Type. These fields are required in the Orders table but only Country is
> required in Instructions. The data entry requirements of the application
> are such that if an instruction has a Type it must have an Exchange.
> The logic of the join is that:
> 1-if all three fields match, Country, Exchange, and Type then join those
> records.
> 2-if two fields match, Country, and Exchange then join those records.
> 3-if one field matches, Country, then join those records.
> My expected results given the sample data is as follows.
Thanks a lot for table and test data. This may not be the smartest
query, but it's easy to understand:
SELECT O.OrderID, I.InstructionID
FROM Orders O
JOIN Instructions I ON O.Country = I.Country
WHERE NOT EXISTS (SELECT *
FROM Instructions I2
WHERE O.Country = I2.Country
AND O.Exchange = I2.Exchange )
UNION ALL
SELECT O.OrderID, I.InstructionID
FROM Orders O
JOIN Instructions I ON O.Country = I.Country
AND O.Exchange = I.Exchange
WHERE NOT EXISTS (SELECT *
FROM Instructions I2
WHERE O.Country = I2.Country
AND O.Exchange = I2.Exchange
AND O.Type = I2.Type)
UNION ALL
SELECT O.OrderID, I.InstructionID
FROM Orders O
JOIN Instructions I ON O.Country = I.Country
AND O.Exchange = I.Exchange
AND O.Type = I.Type
ORDER BY OrderID
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> I want to join 2 tables conditionally. <<
That makes NO sense as you have to join or not join a table.
Okay.
Gee, too bad that SQL and RDBMS has columns and not fields; rows are
not records. They are nothing alike in concept or execution. Also,
"type" is too vague to be a valid column name - "type" of
what? This is basics, damn it!!
xchange.
The logic of the join is that:
1-if all three fields [sic] match, Country, Exchange, and Type [sic]
then join those records [sic].
2-if two fields [sic] match, Country, and Exchange then join those
records [sic]. <
3-if one field [sic] matches, Country, then join those records [sic].
<<
Okay, try this:
SELECT ..
FROM Foo, Bar, etc.
WHERE CASE WHEN Foo.country_code= Bar.country_code
THEN 1 ELSE 0 END
+ CASE WHEN Foo.exchange_code = Bar.exchange_code
THEN 1 ELSE 0 END
+ CASE WHEN Foo.vague_type =Bar.vague_type
THEN 1 ELSE 0 END > 1 ;|||Terri
CREATE VIEW myView
AS
SELECT
OrderID, InstructionID,
I.Country ,I.Exchange ,I.Type FROM Orders O
JOIN Instructions I ON O.Country=COALESCE(I.Country,O.Country)
AND O.Exchange=COALESCE(I.Exchange,O.Exchange) AND
O.Type=COALESCE(I.Type,O.Type)
WHERE I.Exchange IS NOT NULL
--Final Select
SELECT OrderID,
InstructionID,
Country, Exchange, Type
FROM myView WHERE Type IS NOT NULL
UNION
SELECT OrderID,
InstructionID,
Country, Exchange, Type
FROM myView WHERE (SELECT COUNT(*) AS dp
FROM myView V WHERE OrderID=myView.OrderID ) =1
"Terri" <terri@.cybernets.com> wrote in message
news:dr3mjc$r6g$1@.reader2.nmix.net...
>I want to join 2 tables conditionally. One order needs to join with one
> instruction. The three potential join fields are: Country, Exchange, and
> Type. These fields are required in the Orders table but only Country is
> required in Instructions. The data entry requirements of the application
> are
> such that if an instruction has a Type it must have an Exchange.
> The logic of the join is that:
> 1-if all three fields match, Country, Exchange, and Type then join those
> records.
> 2-if two fields match, Country, and Exchange then join those records.
> 3-if one field matches, Country, then join those records.
> My expected results given the sample data is as follows.
> SELECT OrderID,InstructionID FROM Orders
> JOIN ...
>
> --Expected Results
> OrderID,InstructionID
> 1,1
> 2,5
> 3,5
> 4,7
> 5,8
> 6,9
> 7,13
> CREATE TABLE Orders
> (
> OrderID int NOT NULL,
> Country char (3)NOT NULL,
> Exchange char (3)NOT NULL,
> Type char (3)NOT NULL,
> )
> CREATE TABLE Instructions
> (
> InstructionID int NOT NULL,
> Country char (3) NOT NULL,
> Exchange char (3) NULL,
> Type char (3) NULL,
> Instructions varchar (15)NOT NULL
> )
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (1,'USA','NYS','Buy')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (2,'CAN','TSE','Buy')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (3,'CAN','TSE','Sel')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (4,'ESP','BAR','Buy')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (5,'ESP','MAD','Buy')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (6,'IRQ','BAG','Buy')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (7,'DUE','HAM','Buy')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (1,'USA','NYS','Buy','Instruction 1')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (2,'USA','NYS','Sel','Instruction 2')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (3,'USA','NYS',NULL,'Instruction 3')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (4,'USA',NULL,NULL,'Instruction 4')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (5,'CAN','TSE',NULL,'Instruction 5')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (6,'CAN','ALB',NULL,'Instruction 6')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (7,'ESP',NULL,NULL,'Instruction 7')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (8,'ESP','MAD',NULL,'Instruction 8')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (9,'IRQ','BAG','Buy','Instruction 9')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (10,'IRQ','BAG','Sel','Instruction 10 ')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (11,'DUE',NULL,NULL,'Instruction 11')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (12,'DUE','HAM',NULL,'Instruction 12')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (13,'DUE','HAM','Buy','Instruction 13')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (14,'DUE','HAM','Sel','Instruction 14')
>
>

Friday, February 17, 2012

concurrent backups

If I have say 15 databases on one server and in order to
back them up when there is little activity, but to get
them backed up before the tape backups begin, will there
be a problem if some of them are begun at the same time,
say i start 5 of them at 10PM and 5 at 11PM, etc?
Please and Thank YouIf you backup to different tapes or different disk files, the concurrent
backups would be fine.
You cannot concurrently backup to the same tape device.
--
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rachel" <anonymous@.discussions.microsoft.com> wrote in message
news:270001c49cef$8baeebf0$a401280a@.phx.gbl...
> If I have say 15 databases on one server and in order to
> back them up when there is little activity, but to get
> them backed up before the tape backups begin, will there
> be a problem if some of them are begun at the same time,
> say i start 5 of them at 10PM and 5 at 11PM, etc?
> Please and Thank You|||Hi,
No problems, you can execute the backups for different databases in parellel.
Thanks
hari
MCDBA
"Rachel" wrote:
> If I have say 15 databases on one server and in order to
> back them up when there is little activity, but to get
> them backed up before the tape backups begin, will there
> be a problem if some of them are begun at the same time,
> say i start 5 of them at 10PM and 5 at 11PM, etc?
> Please and Thank You
>|||No, most backup software leaves open files alone and if a file is being
backed up to then its open. Even if the software does backup open files
then it will not cause a problem.
Adrian
Rachel wrote:
> If I have say 15 databases on one server and in order to
> back them up when there is little activity, but to get
> them backed up before the tape backups begin, will there
> be a problem if some of them are begun at the same time,
> say i start 5 of them at 10PM and 5 at 11PM, etc?
> Please and Thank You|||Technically this is not a problem if you are backing them all up to disk and
not directly to tape. But depending on the size of the db's, the disk
array's they are on and the disk array's they are being backed up to, the
number of processors, other jobs etc you may not have optimal results. You
may find they will run faster overall if you schedule them one after
another. You can also affect the users and other activities happening if
you attempt to backup too much at one time. Hardware plays a big factor in
this though.
Andrew J. Kelly SQL MVP
"Rachel" <anonymous@.discussions.microsoft.com> wrote in message
news:270001c49cef$8baeebf0$a401280a@.phx.gbl...
> If I have say 15 databases on one server and in order to
> back them up when there is little activity, but to get
> them backed up before the tape backups begin, will there
> be a problem if some of them are begun at the same time,
> say i start 5 of them at 10PM and 5 at 11PM, etc?
> Please and Thank You

concurrent backups

If I have say 15 databases on one server and in order to
back them up when there is little activity, but to get
them backed up before the tape backups begin, will there
be a problem if some of them are begun at the same time,
say i start 5 of them at 10PM and 5 at 11PM, etc?
Please and Thank You
If you backup to different tapes or different disk files, the concurrent
backups would be fine.
You cannot concurrently backup to the same tape device.
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rachel" <anonymous@.discussions.microsoft.com> wrote in message
news:270001c49cef$8baeebf0$a401280a@.phx.gbl...
> If I have say 15 databases on one server and in order to
> back them up when there is little activity, but to get
> them backed up before the tape backups begin, will there
> be a problem if some of them are begun at the same time,
> say i start 5 of them at 10PM and 5 at 11PM, etc?
> Please and Thank You
|||Hi,
No problems, you can execute the backups for different databases in parellel.
Thanks
hari
MCDBA
"Rachel" wrote:

> If I have say 15 databases on one server and in order to
> back them up when there is little activity, but to get
> them backed up before the tape backups begin, will there
> be a problem if some of them are begun at the same time,
> say i start 5 of them at 10PM and 5 at 11PM, etc?
> Please and Thank You
>
|||No, most backup software leaves open files alone and if a file is being
backed up to then its open. Even if the software does backup open files
then it will not cause a problem.
Adrian
Rachel wrote:

> If I have say 15 databases on one server and in order to
> back them up when there is little activity, but to get
> them backed up before the tape backups begin, will there
> be a problem if some of them are begun at the same time,
> say i start 5 of them at 10PM and 5 at 11PM, etc?
> Please and Thank You
|||Technically this is not a problem if you are backing them all up to disk and
not directly to tape. But depending on the size of the db's, the disk
array's they are on and the disk array's they are being backed up to, the
number of processors, other jobs etc you may not have optimal results. You
may find they will run faster overall if you schedule them one after
another. You can also affect the users and other activities happening if
you attempt to backup too much at one time. Hardware plays a big factor in
this though.
Andrew J. Kelly SQL MVP
"Rachel" <anonymous@.discussions.microsoft.com> wrote in message
news:270001c49cef$8baeebf0$a401280a@.phx.gbl...
> If I have say 15 databases on one server and in order to
> back them up when there is little activity, but to get
> them backed up before the tape backups begin, will there
> be a problem if some of them are begun at the same time,
> say i start 5 of them at 10PM and 5 at 11PM, etc?
> Please and Thank You

Tuesday, March 27, 2012

Configuration Order

Configuration Order

Configuration in SQLCLR Assembly

Sunday, March 25, 2012

config creating havoc

Monday, March 19, 2012

Conditional Union!

Conditional UNION!

Conditional Union!

Conditional Union!

Sunday, March 11, 2012

Conditional sorting in order by clause

Conditional query on ASPNET page

Thursday, March 8, 2012

Conditional outer join help

Conditional Order by?

Conditional Order By Stored Procedure

Conditional 'Order By' statement

Conditional Join

Friday, February 17, 2012

concurrent backups

concurrent backups

Concatenating fields

Blog Archive

About Me