SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


query to search


query to search

Author
Message
Neel 7777
Neel 7777
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 Visits: 84
I have a table with fields and data type like

companycode [nchar(4)] actcode [nchar(12)] vounum [nchar(14)] trnam [decimel (18,6)]

I like to build a query which will bring almost 900 rows

and build query like

select comcod, actcode, vounum,trnam where comcod=3305 and
vounum in ('BC201203000073',
'BC201204000001',
'BC201204000002',
'BC201205000001',
'BC201206000001',
'BC201206000002') ----------Here I would like to put 900 (nine hundred) vounum data and run the query, the
problem is data type. It is time consuming to edit every data by putting ' ' and append coma (,) for every vounum. Is there any way to paste the vounum without putting single quote ' vounum ' and appending a coma (,) for every vounum and successfully run the query.
SQLSACT
SQLSACT
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4792 Visits: 2969
Rauf Miah (6/3/2013)
I have a table with fields and data type like

companycode [nchar(4)] actcode [nchar(12)] vounum [nchar(14)] trnam [decimel (18,6)]

I like to build a query which will bring almost 900 rows

and build query like

select comcod, actcode, vounum,trnam where comcod=3305 and
vounum in ('BC201203000073',
'BC201204000001',
'BC201204000002',
'BC201205000001',
'BC201206000001',
'BC201206000002') ----------Here I would like to put 900 (nine hundred) vounum data and run the query, the
problem is data type. It is time consuming to edit every data by putting ' ' and append coma (,) for every vounum. Is there any way to paste the vounum without putting single quote ' vounum ' and appending a coma (,) for every vounum and successfully run the query.



Are you pulling the vounum data from a seperate table?

I would go for something like

select comcod, actcode, vounum,trnam from table where comcod=3305 and
vounum in (select vounum from table)



Is this what you're looking for?
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40106 Visits: 20000
Rauf Miah (6/3/2013)
I have a table with fields and data type like

companycode [nchar(4)] actcode [nchar(12)] vounum [nchar(14)] trnam [decimel (18,6)]

I like to build a query which will bring almost 900 rows

and build query like

select comcod, actcode, vounum,trnam where comcod=3305 and
vounum in ('BC201203000073',
'BC201204000001',
'BC201204000002',
'BC201205000001',
'BC201206000001',
'BC201206000002') ----------Here I would like to put 900 (nine hundred) vounum data and run the query, the
problem is data type. It is time consuming to edit every data by putting ' ' and append coma (,) for every vounum. Is there any way to paste the vounum without putting single quote ' vounum ' and appending a coma (,) for every vounum and successfully run the query.



You're copying and pasting vounum from a source into an SSMS window. What is the source? There are numerous tricks to make this easier, depending upon the source.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Bhaskar.Shetty
Bhaskar.Shetty
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1116 Visits: 509
Put the results in Temp Table from the Source and Fire In query as SQLSACT suggested.
Neel 7777
Neel 7777
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 Visits: 84
I execute a sql query in the database and the code was like,

Select a.comcod, a.actcode, b.actdesc, a.rescode, c.resdesc, a.trnam, a.refnum, a.vounum
from actrna as a
inner join acinf as b on a.comcod=b.comcod and a.actcode=b.actcode
inner join sirinf as c on a.comcod=c.comcod and a.rescode =c.sircode
where a.comcod=3305 and a.tranam> 1000000.000000

Note: datatypes as defined earlier. I like to mention specially that vounum is the primary key of the table actrna as a and datatype is nchar(14)
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40106 Visits: 20000
Instead of writing your second query like this:

vounum IN ('BC201203000073',
'BC201204000001',
'BC201204000002',
'BC201205000001',
'BC201206000001',
'BC201206000002')



write it like this:

vounum IN (
SELECT a.vounum
FROM actrna as a
INNER JOIN acinf as b ON a.comcod=b.comcod AND a.actcode=b.actcode
INNER JOIN sirinf as c ON a.comcod=c.comcod AND a.rescode =c.sircode
WHERE a.comcod=3305 AND a.tranam> 1000000.000000



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
hvermaak
hvermaak
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 167
If the other solutions don't work you can always drop the data into excel and use concatenate to add the '' and ,
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search