Select TOP n in DataSet - Database Discussion Boards

Re: Querying multiple subclassed tables

andyharman1-Feb-07 6:54

andyharman

1-Feb-07 6:54

You would normally use the following:

select A.*, A1.*, A2.*, A3.*
from A
left outer join A1 on A1.id = A.id
left outer join A2 on A2.id = A.id
left outer join A3 on A3.id = A.id
where A.id = 'foo'

This will only return one row - the outer joins will either return proper field values (for the appropriate sub-class table) or nulls (for the inappropriate ones).

Regards
Andy

Re: Querying multiple subclassed tables

Keith Andersch1-Feb-07 9:36

Keith Andersch

1-Feb-07 9:36

Thanks, Andy. That seems to do the trick. I'm curious though, as you add subclassed tables, would performance degrade significantly? I'm using SQL Server 2000. I'm not too worried about performance right now but it's something that would be good to know.

Thanks,
Keith

Re: Querying multiple subclassed tables

andyharman1-Feb-07 22:27

andyharman

1-Feb-07 22:27

As long as each of the tables involved have a unique index or PK constraint (preferably clustered) specified on the key column the query should perform well. I would say that the problem comes with the maintenance effort required to add subclassed tables to all of your queries. I try not to overuse this design for that reason, rather than for performance.

An alternative common design that may perform better would be to merge all of the sub-classed tables into the ancestor table.

If you are worried about performance and encapsulation then you might consider using a hybrid of the two designs - so that the most commonly-used information is merged into the ancestor table, with the sub-classed tables only required in exceptional cases.

Like many design decisions - there is no right or wrong answer Smile | :)

Andy

Re: Querying multiple subclassed tables

Keith Andersch2-Feb-07 9:31

Keith Andersch

2-Feb-07 9:31

I totally agree about the maintenance issue that arises from having so many subclassed tables in such a query. I began to explore a different path and ended up not going with huge query that you suggested. I'm trying my "bestest" to do an n-tier approach and that huge query didn't fit right with what I had. Though who knows if I'm doing it close to right. lol

Thanks again.

Keith

openxml question [modified]

El'Cachubrey1-Feb-07 3:49

El'Cachubrey

1-Feb-07 3:49

hi all
I have an table
CREATE TABLE [MultyItemsTab] (
[MultyId] [int] IDENTITY (0, 1) NOT NULL ,
[SingleId] [int] NOT NULL ,
CONSTRAINT [PK_MultyItemsTab] PRIMARY KEY
(
[MultyId],
[SingleId]
) ON [PRIMARY] ,
...
)
GO

And i want to save into one xml-based data

<br />
...<br />
set @txt = '<ROOT> <Style SingleId="0"/><Style SingleId="1"/> </ROOT>'<br />
<br />
EXEC sp_xml_preparedocument @idoc OUTPUT, @txt<br />
<br />
insert into MultyItemsTab select * from openxml(@idoc,'/ROOT/Style',0) with (SingleId int)<br />
<br />

...
And as a result in a table added 2 rows;

-----------------
MultyId-------------SingleId
0-------------------0
1-------------------1

But actually i want what MultyId indent not increase when add rows to this table at once.

I mean what desirable result is:

-----------------
MultyId-------------SingleId
0-------------------0
0-------------------1

How i can do this????

THANKS

-- modified at 10:16 Thursday 1st February, 2007

Calling an SP with Param...

Astricks1-Feb-07 3:45

Astricks

1-Feb-07 3:45

Calling by the other way:

Is it possible to call a stored procedure WITH PARAM from .net WITHOUT using "AddParams" ? I mean we should be able to call(exec) the SP just like we do in the query analyser. I'd construct the string like "sp_testproc '111','RR',23'". and execute it as nonQuery. Is there any way? please help.

Re: Calling an SP with Param...

Pete O'Hanlon1-Feb-07 4:22

Pete O'Hanlon

1-Feb-07 4:22

Theoretically you could do this by setting the CommandType to Text and then issue the command directly. I would strongly advise you against doing this however. This is not a good design, and it is certainly not a robust or secure design. By passing in parameters as part of a string, you open yourself right up for Sql Injection attacks.

I cannot see any reason, apart from convenience, why you would do this. Use command parameters - they are much better for your needs.

the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer

Deja View - the feeling that you've seen this post before.

Select TOP n in DataSet

Vasudevan Deepak Kumar1-Feb-07 2:23

Vasudevan Deepak Kumar

1-Feb-07 2:23

Any trick of using Select TOP in dataset to datatable. I know that we can have a select TOP in the query but that is currently infeasible in my case since datasource does not support that. Also this is for .NET framework 1.1.

Any clues?

Vasudevan Deepak Kumar
Personal Homepage
Tech Gossips

Re: Select TOP n in DataSet

deGupta1-Feb-07 3:00

deGupta

1-Feb-07 3:00

Hi Deepak,

u can use the following query to select teh datarows in f/w 1.1.

DataRow[] arrDRow = ds.Tables[0].Select("Top 10");

this returns an array of datarows with the criteria specified, which you can manipulate as required.
Let me know if that works.

Regards

Deepali

Re: Select TOP n in DataSet

Vasudevan Deepak Kumar1-Feb-07 4:15

Vasudevan Deepak Kumar

1-Feb-07 4:15

Missing operand after 10 operator

Confused | :confused:

Vasudevan Deepak Kumar
Personal Homepage
Tech Gossips

Passing more parameters to stored procedure

Siva Myneni1-Feb-07 2:22

Siva Myneni

1-Feb-07 2:22

Hi All,

I have a question for you?
I have to pass 7 parameters to the procedure.
for some parameters i am not passing a value.
how to do this?

sivamyneni

Re: Passing more parameters to stored procedure

andyharman1-Feb-07 4:39

andyharman

1-Feb-07 4:39

You need to supply defaults for each of your optional parameters:

create procedure MyProc
@Param1 varchar(10) = null,
@Param2 int = null,
@Param3 date = null
as begin
   set nocount on
   --do stuff
   return(0)
end
go

exec MyProc
exec MyProc 'hello', 123, '1 feb 2007'
exec MyProc 'world'
exec MyProc @Param3 = '1 feb 2007'

Hope this helps.
Andy

Re: Passing more parameters to stored procedure

Siva Myneni1-Feb-07 18:13

Siva Myneni

1-Feb-07 18:13

Hi I tried that one i am not getting proper result because none of my field contaains null value.so i am not getting the desired.

sivamyneni

Re: Passing more parameters to stored procedure

Elina Blank2-Feb-07 3:10

Elina Blank

2-Feb-07 3:10

Try this:

If Your procedure is like following:

ALTER PROCEDURE myProcedure<br />
    @Param1	int = 0,<br />
    @Param2     int = 0,  <br />
    @Param3	int,<br />
    @Param4	int = 0,<br />
    @Param5     int = 0,  <br />
    @Param6	int,<br />
    @Param7     int<br />
<br />
         <br />
AS<br />
<br />
<br />
-- Procedure code

In order to call it (say, from another sp):

exec myProcedure @Param3 = @PassedParam1, @Param6 = @PassedParam2, @Param7 = @PassedParam3

Note: Initialize parameters in the original procedure if you are not sure, the procedure will get those parameters passed to it. (In the case above, if you will not pass one of the @Param3, @Param6, or @Param7) the error will happen.

Hope that helps.

Sincerely,
Elina

Life is great!!!
Enjoy every moment of it! Blush | :O

using XML as DataSource for RDL reports

deGupta1-Feb-07 0:46

deGupta

1-Feb-07 0:46

hi,
i need some help regarding data access from XML sent by web services.
I have a web service that sends out XML as its result, as below:

<?xml version="1.0" encoding="utf-16"?>
<Response webMethodId="GetUserListing">
<SecurityContext sessionId="154oe1upayjpnx55pg1f3r45" />
<Document>
<ArrayOfUsers xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<User>
<UserId>32</UserId>
<Name>foxtrot</Name>
<Address>Suite 712</Address>
<Zip>12345</Zip>
< PhoneNumbers>
<string 111-111-1111/>
</PhoneNumbers>
<Emails>
<string>foo@foo.bar</string>
</Emails>
</User>
</ArrayOfUsers>
<TotalRecords>25</TotalRecords>
</Document>
</Response>

In the RDL file, the Query i specify is:
<Query>
<Method Namespace ="http://tempuri.org/" Name = "ProcessRequest" ></Method>
<SoapAction>http://tempuri.org/ProcessRequest</SoapAction>
</Query>

and the result i get is in a single line that says ProcessRequestResult, with the XML specified above in a single line. however, i need the result in a tabular form, which would give me records of different users, one below the other.
if i specify the <ElementPath> element for the query, i do not get any response at all.

The <ElementPath> element i specify is:

<ElementPath>Response{}/SecurityContext{}/Document{}/ArrayOfUser{}/User</ElementPath>

i need urgent help for this and any help would be appreciated.
Thanks in advance

Deepali

SQL Case Statement

si_691-Feb-07 0:38

si_69

1-Feb-07 0:38

Hi
Can anyone help with an SQL statement im having problems with ?

i want to do something like the below

select date_in, type= CASE date_in
when datepart(hour,date_in) > 12 THEN 'Afternoon'
ELSE 'Morning'
END
from my_table
where date_in >='2007-2-1'

so it will return something like

2007-02-01 08:22:32.663 Morning
2007-02-01 09:22:32.773 Morning
2007-02-01 14:42:32.803 Afternoon
2007-02-01 15:56:32.803 Afternoon

however im getting an error thrown back at me when i try to execute it

Line 2: Incorrect syntax near '>'.

Any ideas ??

thanks

Simon

Re: SQL Case Statement

Sylvester george1-Feb-07 0:53

Sylvester george

1-Feb-07 0:53

correct your query like this

select date_in, type= CASE
when datepart(hour,date_in) > 12 THEN 'Afternoon'
ELSE 'Morning'
END
from my_table
where date_in >='2007-2-1'

Regards,
Sylvester G
Senior Software Engineer
Xoriant Solutions

Re: SQL Case Statement

deGupta1-Feb-07 3:02

deGupta

1-Feb-07 3:02

You do not have a BEGIN for the END specified below ELSE

Deepali

Re: SQL Case Statement

Mike Dimmick2-Feb-07 6:16

Mike Dimmick

2-Feb-07 6:16

CASE has two syntaxes. You can either use it like a C/C++/C# switch or VB Select Case statement, where you specify an expression to be tested against multiple possibilities, or you can use it more like a multi-armed if statement, where you supply boolean expressions - the first one to evaluate to true is then returned.

The syntax for the first is:

CASE expression
   WHEN value1 THEN result1
   WHEN value2 THEN result2
   ELSE defaultresult
END

The syntax for the second:

CASE
   WHEN booleanexpression1 THEN result1
   WHEN booleanexpression2 THEN result2
   ELSE defaultresult
END

Stability. What an interesting concept. -- Chris Maunder

User define Data type

Software_Guy_1231-Feb-07 0:06

Software_Guy_123

1-Feb-07 0:06

Hello,

Is it better to use define data type in sql tables, What is advantage and disadvantage of user define data types ?

Regards,
S Kumar

Re: User define Data type

Pete O'Hanlon1-Feb-07 0:27

Pete O'Hanlon

1-Feb-07 0:27

I like defined data types. They are a great way to enforce standards. For instance, if I have a need for Name fields throughout the database and they are always going to be 30 characters then it is much better for me to be able to define a name data type and then use this wherever there is a Name field.

Re: User define Data type

Software_Guy_1231-Feb-07 6:40

Software_Guy_123

1-Feb-07 6:40

Hi John,

Thanks a lot for your reply.

Best Regards,
S Kumar

SQL Helper File

gauthee31-Jan-07 23:44

gauthee

31-Jan-07 23:44

Hi,

I've heard that there is an sql helper file which has got all the stuff regarding the connectivity to the databse, like connectio, command objects....

Is there any any sort of refernce/link where i could get that?

Thanks!

Gautham

Re: SQL Helper File

Sylvester george1-Feb-07 0:36

Sylvester george

1-Feb-07 0:36

http://www.codeproject.com/cs/database/MS_Application_Blocks.asp[^]

Regards,
Sylvester G
Senior Software Engineer
Xoriant Solutions

Which index is good

anujose31-Jan-07 23:02

anujose

31-Jan-07 23:02

Which index is good to apply.clustered or nonclustered?

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.