Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

query to search Expand / Collapse
Author
Message
Posted Monday, June 3, 2013 3:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 1, 2013 12:41 AM
Points: 44, 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.

Post #1459132
Posted Monday, June 3, 2013 3:29 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 1,380, Visits: 2,698
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?
Post #1459140
Posted Monday, June 3, 2013 6:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:26 AM
Points: 6,813, Visits: 14,027
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
Post #1459235
Posted Monday, June 3, 2013 7:51 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, October 16, 2014 9:28 AM
Points: 512, Visits: 433
Put the results in Temp Table from the Source and Fire In query as SQLSACT suggested.
Post #1459278
Posted Monday, June 3, 2013 9:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 1, 2013 12:41 AM
Points: 44, 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)


Post #1459521
Posted Tuesday, June 4, 2013 12:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:26 AM
Points: 6,813, Visits: 14,027
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
Post #1459564
Posted Tuesday, June 4, 2013 1:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 6:41 AM
Points: 25, Visits: 167
If the other solutions don't work you can always drop the data into excel and use concatenate to add the '' and ,

Post #1459573
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse