Using results multiple times - Database Discussion Boards

Hello,

I am using a typed dataset and have created a query the same as the one below:

SELECT     TaskID, Details<br />
FROM         IncidentTask<br />
WHERE     Type IN (@typeArray)

I have created the methods to return a data table and fill a data table.

My table adapter is called incidentTask.

I am using the code to get all the types that the user will want to query on, as below.

'Add the support type that have been checked<br />
            If (Me.chkITPhoneSupport.Checked) Then<br />
                typeList.Add("'IT Phone Support'")<br />
            End If<br />
            If (Me.chkITRemoteSupport.Checked) Then<br />
                typeList.Add("'IT Remote Support'")<br />
            End If<br />
            If (Me.chkITEmailSupport.Checked) Then<br />
                typeList.Add("'IT Email Support'")<br />
            End If

I then put all these into an array called typeArray. And attempt to fill the data table. It works ok, if i only have one item, but if i have more than 1 it won't work, as the parameter in the query is only expecting a single value, not an array.

Me.TA_IncidentTask_DSJobSheet1.FillByCritera(Me.DsJobSheet1.IncidentTask, String.Join(",", typeArray))<br />

Is there any way I can send an array to a query,

Many thanks for your suggestions,

Steve

Here's my article on the topic:

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

HI plse help me How to bind data to dataset using Views in sqlserver2005...

I encountered this problem in crystal reports I want to bind to tables and create a report for
that two tables or is there any another way help me...

came out of hardwork

Hi,
please can anyone let me know the date formats in sql server for this query.

my table structure is"

PDate PMonth PYear
10 05 2007
03 05 2004
12 10 2007
o1 12 2005

now i have to retrieve the records based on some specified dates.

for example, i want the records which matches "12/10/2007"

i know how to write the code for these 3 columns to format (mm/dd/yyyy)using C# & vb.Net but i want to know how to write using Tsql

Thanks,
Suketh

Hi

Use the date functions like Month(), Day() and Year() to get month,date and year. Based on these functions you can filter records. For example

Month(DateColumn) will display month of the given datecolumn

Hope you got it...

Smile | :)

Harini

use this query

select * from t1 where pdate=day('10/12/2007') and pmonth=month('10/12/2007') and pyear=year('10/12/2007')
Note= you should format the date as 'MM/dd/yyyy'
date function returns day('10/12/2007') as 12

shally

set dateformat dmy
Go
select * from Table
where
Pdate=select Datepart(Day,'12/10/2007')
and PMonth=Datepart(Month,'12/10/2007')
and PYear=Datepart(Year,'12/10/2007')

OK, probably it is an easy answer, but I just can't get it working. I'm working on SQL SERVER 2005. Here is stored procedure:

<br />
SELECT    TOP (50) *<br />
FROM         h_Case<br />
WHERE   ((FirstName LIKE @FirstName) AND <br />
			(LastName LIKE @LastName) OR<br />
 			((IsNull(Aliases, '') LIKE @FirstName) AND<br />
			(IsNull(Aliases, '') LIKE @LastName))) AND<br />
			(DOB BETWEEN @DobFrom AND @DobTo) AND<br />
			(IsNull(GenderId, -1) BETWEEN @GenderFrom AND @GenderTo) AND<br />
			(IsNull(PHN, '') LIKE @PHN)<br />
<br />
<br />
SELECT * FROM h_Encounter<br />
WHERE CaseId IN (SELECT TOP (50) CaseId FROM h_Case<br />
				 WHERE ((FirstName LIKE @FirstName) AND <br />
					(LastName LIKE @LastName) OR<br />
 					((IsNull(Aliases, '') LIKE @FirstName) AND<br />
					(IsNull(Aliases, '') LIKE @LastName))) AND<br />
					(DOB BETWEEN @DobFrom AND @DobTo) AND<br />
					(IsNull(GenderId, -1) BETWEEN @GenderFrom AND @GenderTo) AND<br />
					(IsNull(PHN, '') LIKE @PHN))<br />
<br />
SELECT cn.*, u.Username AS Username FROM h_CaseNote cn<br />
INNER JOIN c_User u ON u.UserId = cn.UserId<br />
WHERE CaseId IN (SELECT TOP (50) CaseId FROM h_Case<br />
				 WHERE   ((FirstName LIKE @FirstName) AND <br />
				(LastName LIKE @LastName) OR<br />
 				((IsNull(Aliases, '') LIKE @FirstName) AND<br />
				(IsNull(Aliases, '') LIKE @LastName))) AND<br />
				(DOB BETWEEN @DobFrom AND @DobTo) AND<br />
				(IsNull(GenderId, -1) BETWEEN @GenderFrom AND @GenderTo) AND<br />
				(IsNull(PHN, '') LIKE @PHN))<br />
<br />
<br />
SELECT * FROM h_CasePDR<br />
WHERE CaseId IN (SELECT TOP (50) CaseId FROM h_Case<br />
				 WHERE   ((FirstName LIKE @FirstName) AND <br />
				(LastName LIKE @LastName) OR<br />
 				((IsNull(Aliases, '') LIKE @FirstName) AND<br />
				(IsNull(Aliases, '') LIKE @LastName))) AND<br />
				(DOB BETWEEN @DobFrom AND @DobTo) AND<br />
				(IsNull(GenderId, -1) BETWEEN @GenderFrom AND @GenderTo) AND<br />
				(IsNull(PHN, '') LIKE @PHN))<br />
<br />
<br />
RETURN<br />

So it is obvious what I'm trying to do? I just want to reuse selected cases. I tried with:

<br />
WITH C AS<br />
(<br />
SELECT TOP (50) CaseId FROM h_Case<br />
				 WHERE   ((FirstName LIKE @FirstName) AND <br />
				(LastName LIKE @LastName) OR<br />
 				((IsNull(Aliases, '') LIKE @FirstName) AND<br />
				(IsNull(Aliases, '') LIKE @LastName))) AND<br />
				(DOB BETWEEN @DobFrom AND @DobTo) AND<br />
				(IsNull(GenderId, -1) BETWEEN @GenderFrom AND @GenderTo) AND<br />
				(IsNull(PHN, '') LIKE @PHN)<br />
<br />
SELECT * FROM C<br />
<br />
<br />
SELECT * FROM h_Encounter<br />
WHERE CaseId IN (SELECT CaseId FROM C)<br />
<br />
SELECT cn.*, u.Username AS Username FROM h_CaseNote cn<br />
INNER JOIN c_User u ON u.UserId = cn.UserId<br />
WHERE CaseId IN (SELECT CaseId FROM C)<br />
<br />
SELECT * FROM h_CasePDR<br />
WHERE CaseId IN (SELECT CaseId FROM C)<br />

But C can be used just one time Frown | :(

So is there a way to do this over some 'table alias', or I'm destined to write this query using temporary table? If I can only do this using temporary table would how faster solution that would be over this multiple select?

Tnx in advance

The temporary table is most likely your best resort. The table will sit in memory waiting till the end of the procedure to be used. That being said, if you want to be lazy... You could do what I call a "String Procedure" (instead of a stored procedure). Remember that this method is very vulnerable to SQL injection/truncation attacks

DECLARE @FilterSQL AS varchar(8000)

SET @SQL = 'SELECT TOP (50) * FROM h_Case WHERE ((FirstName LIKE ''' + @FirstName + ''') AND (LastName LIKE ''' + @LastName + ''') OR ((IsNull(Aliases, '''') LIKE ''' + @FirstName + ''' AND (IsNull(Aliases, '''') LIKE ''' + @LastName + '''))) AND (DOB BETWEEN + ''' @DobFrom + '''AND ''' + @DobTo + ''') AND (IsNull(GenderId, -1) BETWEEN ' + @GenderFrom + ' AND ' + @GenderTo + ') AND
(IsNull(PHN, '''') LIKE ''' + @PHN + ''')'

Then insert the @SQL variable into your other SQL.

So use the temporary table.

Hogan

Would it be smarter to use Table variables in this case? I read that they interrupt parallel execution.

Thanks for your previous answer in any case!

Yes, I agree that table variables are the best solution here.

Hogan

Tnx for your answers!

I am using SQL DMO object in vbscript to connect to SQLServer2 ,
All is working well , but I want to trap error in case if connection or login fails

ex:

Dim oSQLServer : set oSQLServer = createObject("SQLDMO.SQLServer2")

oSQLserver.Connect machine,"sa"

Develop2Program & Program2Develop

try googling vbscript error handling[^]

topcoderjax - Remember, Google is your friend.

What is the output of the following query SELECT TRUNC(1234.5678,-2) FROM DUAL;
i came to knw the answer as 1200 hw?

If places is positive, number is truncated to places decimal places. If places is negative, number is truncated on the left of the decimal point
so in ur case
the number on the left of the decimal point is 1234
-2 = Number of decimal places used for truncating
so u have to truncate 1234 for two places
that means 1200

sdf

I believe the following is correct:

SELECT TRUNC(1234.5678, 2) = 1234.5600
SELECT TRUNC(1234.5678, 1) = 1234.5000
SELECT TRUNC(1234.5678, 0) = 1234.0000
SELECT TRUNC(1234.5678, -1) = 1230.0000
SELECT TRUNC(1234.5678, -2) = 1200.0000

Seeing a pattern yet?

topcoderjax - Remember, Google is your friend.

[Message Deleted]

If you are using ADO for data accessing then you
will not need to include any dll with your exe file
becaues ADO will be there on the Target Computer if it is
Win98 and more.

Still if you are sure that ADO is not on the target computer
than
you should supply msad0*.* files and then register these files on target computer by using regsvr32 utility

Thanks

Sorry, you have to use an official updater for Windows 2000 or Windows XP, otherwise Windows File Protection will simply undo your changes.

It reduces pain all round if you follow the official method of installing software.

Stability. What an interesting concept. -- Chris Maunder

Why you delete your question?

WhiteSky

Don't delete your question - It is considered rude.

Upcoming events:
* Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services...

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

hi everyone ....

Ive a problem to select orders count by date only

Here ive table called ORDERSMAIN

In this ive columns Date , Order list

Here the date is along vth time wen it is entered...

SO i want to count the total number of orders entered in each day .. so the date will be distinct finally ...

n one of our frnd told me the answer in one topic ,in that the orders r counting by the Date with time...

but i need only vth date .. how to ignore the time ....

ive table like this

Sno Date Orders

1 2007/3/4 02:02:02 sfjsk
2 2007/3/4 02:02:01 sfjgf
3 2007/3/4 02:02:00 szdjfdj
4 2007/3/5 02:56:02 sfsdfs

finally i want to get is

Date ordersCount

2007/3/4 50
2007/3/3 80
2007/3/2 90

Like this plz help

Thanks inadv

Naresh

Hi

You can convert the datetime column to specific format ('mm/dd/yyyy'). Check different formats in SQL Help online. Use the converted column in group by clause. Here is the example for you. Here I have converted to mm/dd/yyyy that is format parameter is 103. You can change it to your requirement

select convert(nvarchar(12),DTimeStamp,103) as Dsb_Time, count(*) from Table1 <br />
where DTimeStamp is not null <br />
Group by convert(nvarchar(12),DTimeStamp,103)

Hope you got it

Harini

Thanqqqqqqqqqqqqqqqqqqqqqq

Harini.....

I am getting my result nowwwwwww....

Thats great

Actually iam new to sqlserver

This group is very helpfull for me ....

Thanq once again....

Naresh