query to search

Question

query to search

Neel 7777

Right there with Babe

Points: 778
More actions
June 3, 2013 at 3:17 am

#278438

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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLSACT SSC-Insane Points: 21838 More actions · Answer 1

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 SSC Guru Points: 186127 More actions · Answer 2

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

Bhaskar.Shetty Hall of Fame Points: 3054 More actions · Answer 3

Put the results in Temp Table from the Source and Fire In query as SQLSACT suggested.

Neel 7777 Right there with Babe Points: 778 More actions · Answer 4

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 SSC Guru Points: 186127 More actions · Answer 5

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

hvermaak SSChasing Mays Points: 642 More actions · Answer 6

If the other solutions don't work you can always drop the data into excel and use concatenate to add the '' and ,