Professional Web Applications Themes

Openquery Select Statement Returning Only 1 Record - Microsoft SQL / MS SQL Server

Although what I'm trying to do is more complicated than this, the following sums up the problem. I have a linked server which is an accountancy software package. This contains the tables INVOICE and INVOICE_ITEM. INVOICE consists of fields INVOICE_NUMBER, INVOICE_DATE, ACCOUNT_REFERENCE etc INVOICE_ITEM consists of fields INVOICE_NUMBER, LINE_NUMBER, STOCK_CODE, QUANTITY, SALES_PRICE etc The two tables relate to each other on the INVOICE_NUMBER field. Currently they both run from a low value to 81234. The following query works. Correctly returning 7 records representing all the lines on the 4 invoices:- SELECT * FROM OpenQuery(SAGE_SERVER,'SELECT * FROM INVOICE_ITEM WHERE INVOICE_NUMBER > ...

  1. #1

    Default Openquery Select Statement Returning Only 1 Record

    Although what I'm trying to do is more complicated than this, the
    following sums up the problem.

    I have a linked server which is an accountancy software package. This
    contains the tables INVOICE and INVOICE_ITEM.

    INVOICE consists of fields INVOICE_NUMBER, INVOICE_DATE,
    ACCOUNT_REFERENCE etc

    INVOICE_ITEM consists of fields INVOICE_NUMBER, LINE_NUMBER, STOCK_CODE,
    QUANTITY, SALES_PRICE etc

    The two tables relate to each other on the INVOICE_NUMBER field.
    Currently they both run from a low value to 81234.

    The following query works. Correctly returning 7 records representing
    all the lines on the 4 invoices:-
    SELECT * FROM OpenQuery(SAGE_SERVER,'SELECT * FROM INVOICE_ITEM WHERE
    INVOICE_NUMBER > 81230')

    The following does not work. It just returns 1 record (81231), when
    obviously it should return 4:-
    SELECT * FROM OpenQuery(SAGE_SERVER,'SELECT * FROM INVOICE WHERE
    INVOICE_NUMBER > 81230')

    I really do not understand why this is happening as both queries are on
    the face of it identical. Neither table seems to be different in anyway
    and so this newsgroup is my last hope.

    Regards,

    Marcus Thornton.
    Marcus Guest

  2. #2

    Default Re: Openquery Select Statement Returning Only 1 Record

    Just to take care of the obvious:

    Did you try running both queries when directly connected to that server?

    --
    Tibor Karaszi, SQL Server MVP
    Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver


    "Marcus" <co.uk> wrote in message news:168.1.50... 


    Tibor Guest

  3. #3

    Default Re: Openquery Select Statement Returning Only 1 Record

    In article <0e7001c36731$0f6dac10$gbl>,
    com says... 

    Count(*) returned different values for the two tables. I do think you
    are right about the difference between the tables being related to
    indexing. In the INVOICE table INVOICE_NUMBER is unique and sequential,
    in the INVOICE_ITEM table it is not unique (as there can be more than
    one line per invoice).

    I have worked a solution although I'm not sure how efficient it is.

    SELECT INVOICE_NUMBER, INVOICE_DATE, ACCOUNT_REF, etc FROM OPENQUERY
    (SAGE_SERVER,'SELECT INVOICE.* FROM INVOICE INNER JOIN INVOICE_ITEM ON
    INVOICE.INVOICE_NUMBER = INVOICE_ITEM.INVOICE_NUMBER WHERE
    INVOICE_NUMBER > 81230') GROUP BY INVOICE_NUMBER, INVOICE_DATE,
    ACCOUNT_REF, etc

    This works but it is not beautiful because of the number of fields in
    the INVOICE table (a lot more than the three shown here). I also have
    reservations about it, because I am reading from two tables instead of
    one and the additional table is much bigger than the one I'm interested
    in. I'm then performing a join and a group by. All of which I am sure
    must be less efficient than just selecting from the one table.

    Still thank you for the 'index' and the 'inner join' clue. It works and
    doesn't appear to be that slow, so I'm able to proceed.

    Marcus Thornton.

    Marcus Guest

Similar Threads

  1. Returning last met record only!
    By Anony in forum MySQL
    Replies: 6
    Last Post: March 30th, 04:02 AM
  2. returning an inserted SQL record
    By JoeyTMann in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 22nd, 04:38 PM
  3. Functions returning RECORD
    By Craig Bryden in forum PostgreSQL / PGSQL
    Replies: 4
    Last Post: January 14th, 01:37 PM
  4. PHP-MySQL: Returning ID when new record is made?
    By Sugapablo in forum PHP Development
    Replies: 8
    Last Post: December 5th, 05:54 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139