Database

9-Aug-09 22:21

Hy!
Here is the situation:
I am interested in ordering rows in a returned querie (full outer join querie) and I need to do the ordering for 2 columns. Now, the thing is, simple ORDER BY can't help me. If you use

ORDER BY column1, column2

the result is sorting querie by column1 and then column2. This does not satisfy my situation.
I wolud like sorting to be in a way that sorting happens for these 2 columns at once. I would like the result to look like this if it is possible:

column1         column2
25.5.1984      null
null           30.5.1985
null           17.5.1993
2.2.2005       null

The columns are type short date/time.
Cheers!

Mycroft Holmes10-Aug-09 3:11

Mycroft Holmes

10-Aug-09 3:11

Try this

order by isnull(column1,column2)

Never underestimate the power of human stupidity
RAH

Luc Pattyn10-Aug-09 3:35

Luc Pattyn

10-Aug-09 3:35

Nice one!

Luc Pattyn [Forum Guidelines] [My Articles]

The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.

Branislav Vidovic11-Aug-09 0:23

11-Aug-09 0:23

Does not seem to help..

error:characters found after end of sql statement

Luc Pattyn13-Aug-09 3:28

Luc Pattyn

13-Aug-09 3:28

I just happened to need this today, in MySQL though. There the function exists and is called ifnull. Works great. Thanks.

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.

myinstincts9-Aug-09 19:03

myinstincts

9-Aug-09 19:03

i wrote a command like 'select max(admission_no) from user_details'in ms access.but it is giving the error as 'characters found after end of sql statement'.Can anybody help me with this?

Re: error:characters found after end of sql statement

Vimalsoft(Pty) Ltd9-Aug-09 22:05

Vimalsoft(Pty) Ltd

9-Aug-09 22:05

Please Give us more info. Where are you using this query ? from your code or this is in the Storeprocedure.

Give us more info so that we can help.

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

Retrieve data from column with xml data type, SQL Server 208

A.Petkovic9-Aug-09 1:19

A.Petkovic

9-Aug-09 1:19

Situation:
One of the columns in the table is with xml data type.

Every seconds table has ~30 row inserted. After couple of mounts there will be ~ 30row x 60s x 60min x 24h x 30day = ~78x10^6 rows for 1 month.

The goal is: very quick retrieve data from that table, where some queries will target xml column (specific data in xml element or attribute).

Did anybody have experience with xml column in this way? Did this way of returning data is slow, or can be quick with using xml indexes, or even using xml indexes this way of returning data is still slow?

___________________________

A.Petkovic
B.Sc.Eng., MCSD, MCT

Re: Retrieve data from column with xml data type, SQL Server 208

Vimalsoft(Pty) Ltd9-Aug-09 22:11

Vimalsoft(Pty) Ltd

9-Aug-09 22:11

if i understand you correctly , you have a field in your table that is of XML data type and has some xml in it and you want to retrieve a certain attribute(Column) in that XML. Then my frinend , that means form the begining you made a poor design decision when designing a database, and to loop through the data will be tedious for sql to retrieve the amaount of data you want. use another way.Give us more info , mybe we can help in the Logic that you can follow.

Re: Retrieve data from column with xml data type, SQL Server 208

A.Petkovic10-Aug-09 1:42

A.Petkovic

10-Aug-09 1:42

I will try to explain, in one db I have 3 tables, because I want to return data as quick as possible to web services, I design 1 table with all necessary columns.
First design is without xml column, but number of rows will be at least 5x larger from design with xml column.

One of the solution is that I return data, and in web services to deal with xml column.
Second solution is that I return data filtered by xml attributes, or elements.

One more quick response is goal.

___________________________

A.Petkovic
B.Sc.Eng., MCSD, MCT

UDF, SPROC in SQL Server Express 2008

Dirso8-Aug-09 10:42

Dirso

8-Aug-09 10:42

Hi,
I'd like to put all my database management in StoredProcedures or Functions, but I'm having a small problem:
1) I know I can't use INSERT, UPDATE or DELETE in UDFs, right?
2) So i created a SPROC that inserts a record to a simple table (id, name) and returns the ID in an output parameter. It's working fine.
3) I create a UDF that executes this SPROC and returns the ID... and here is my problem... looks like I can't execute my own SPROCs from a UDF. Frown | :(

4) So.. how do I achieve this goal? Inserting a record and returning the auto generated id back to my web browser (asp.net application).
Here is some of my code:

CREATE PROCEDURE finsTeste 
	@snome varchar(50), 
	@nid int output
AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO teste (nome) VALUES (@snome)
	SELECT @nid = SCOPE_IDENTITY()
END
GO
CREATE FUNCTION insTeste 
(
	@snome varchar(50)
)
RETURNS int
AS
BEGIN
	DECLARE @nid int
	EXECUTE finsTeste @snome, @nid output
	RETURN @nid;
END
GO

And then in the asp.net page I run a ExecuteScalar(StoredProcedure, "insTeste", params);

Thanks,
Dirso

Re: UDF, SPROC in SQL Server Express 2008

i.j.russell8-Aug-09 11:10

i.j.russell

8-Aug-09 11:10

Why do you need to create a UDF? You can get the value that you need directly from the SP.

Re: UDF, SPROC in SQL Server Express 2008

Dirso8-Aug-09 12:36

Dirso

8-Aug-09 12:36

I didn't know that... I thought I had to execute. Thank you very much! I just tried and it worked great!

Thanks,
Dirso

foreign key

Amit Patel19858-Aug-09 0:15

Amit Patel1985

8-Aug-09 0:15

hi
how to create foreign key in sql server 2005 using table designer

thanks

Re: foreign key

Eddy Vluggen8-Aug-09 5:08

Eddy Vluggen

8-Aug-09 5:08

I'm assuming that you're using "Microsoft SQL Server Management Studio";

* Rightclick on the table, choose "design"
* Rightclick on any column in the newly opened window, choose "Relationships"
* Click "Add"

Smile | :)

I are Troll Smile | :)

FULL JOIN in Access 2003

Branislav Vidovic8-Aug-09 0:10

8-Aug-09 0:10

Hello everyone!

I wonder is it possible to create SQL querie using FULL JOIN in Access 2003? When i try it i just get syntax error. So does anyone have an idea how to simulate full join in access?

Cheers!

Blue_Boy8-Aug-09 2:09

Blue_Boy

8-Aug-09 2:09

Branislav Vidovic wrote:
When i try it i just get syntax error

Which error message do you get? Post your code to see where is wrong.

For more about FULL JOIN.[^]

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

www.aktualiteti.com

Jerry Hammond8-Aug-09 3:40

Jerry Hammond

8-Aug-09 3:40

There is no reason to simulate the Outer Join.

Please post your query along with the error you receive.

"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering

riced9-Aug-09 1:15

riced

9-Aug-09 1:15

Possibly the union of a left join and a right join? Smile | :)

Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis

Branislav Vidovic9-Aug-09 20:41

9-Aug-09 20:41

Yes I actually tried that and made it. IT is actually union of left join, right join and inner join Big Grin | :-D

riced10-Aug-09 3:04

riced

10-Aug-09 3:04

Branislav Vidovic wrote:
and inner join

Of course - stupid of me to forget it. Smile | :)

Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis