query to search

  • 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.

  • 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?

  • 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

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

  • 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)

  • 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

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

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

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