OPENQUERY results....

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: DylanM (DylanM_at_discussions.microsoft.com)
Date: 09/01/04 

Date: Wed, 1 Sep 2004 02:23:07 -0700

Can someone please explain this one to me...!

I have a linked server from a SQL 2000 server to an IBM AS/400. I'm issuing
a query against the linked server and have tried to place the WHERE clause
inside the OPENQUERY SQL statement.

When I do this, it only returns 1 record...

1 Record Returned SQL Syntax
SELECT * FROM OPENQUERY(LS_W1CENTRALORDERS, 'SELECT A.PRDPK, A.PRDART,
A.PRDTIT, A.PRDDTP, A.PRDSDP, A.PRDCLS, A.PRDSUP, A.PRDREC, A.FMTDRT,
A.FMTCAT, A.FMTDES, A.FMTRT1, A.FMTFTM, B.COFADD, A.FMTDPR, A.FMTSOR,
A.FMTCAT, B.COFQTY, B.COFSKF, B.COFFMT, B.COFTSK, C.COACTC FROM
UKHOFQRY.CISCODTAL1 A, QAHOFLIB.COFMTPF B, QAHOFLIB.COARTPF C WHERE A.FMTPK =
B.COFSKF AND A.PRDPK = C.COASKU AND A.PRDPK = 654090')

When I issue the same statement, but with the where clause in the outer
section, it returns 2 records (which is correct)

2 Records Returned SQL Syntax
SELECT * FROM OPENQUERY(LS_W1CENTRALORDERS, 'SELECT A.PRDPK, A.PRDART,
A.PRDTIT, A.PRDDTP, A.PRDSDP, A.PRDCLS, A.PRDSUP, A.PRDREC, A.FMTDRT,
A.FMTCAT, A.FMTDES, A.FMTRT1, A.FMTFTM, B.COFADD, A.FMTDPR, A.FMTSOR,
A.FMTCAT, B.COFQTY, B.COFSKF, B.COFFMT, B.COFTSK, C.COACTC FROM
UKHOFQRY.CISCODTAL1 A, QAHOFLIB.COFMTPF B, QAHOFLIB.COARTPF C WHERE A.FMTPK =
B.COFSKF AND A.PRDPK = C.COASKU')
WHERE PRDPK = 654090

The second query runs much slower, I assume this is because it's selecting
all records in the table first (approx 300k) , then selecting out those that
meet the criteria?

Why would the results be different tho, if there are 2 records that meet the
criteria surely the first statement should also return them?

Confused....!

Thanks

Relevant Pages

  • Re: SQL Syntax Error
    ... match all the criteria you input. ... SQL view and never open it in the query grid view. ... The syntax for the "where" statement is incorrect becuase of this. ...
    (microsoft.public.access.queries)
  • Re: Using combo box to search form
    ... "Jeff Boyce" wrote: ... Since you "started adding" additional criteria, ... about SQL syntax to know how to form a grammatically-correct SQL statement. ... One way to do that is to see what Access generates when you build a query. ...
    (microsoft.public.access.formscoding)
  • Re: Adjusting SQL in Query
    ... from the RESPEL table which satisfy the criteria in the PRICELIST Table. ... RESPEL INNER JOIN PRICELIST ON RESPEL.PRICELIST = ... Subject: Adjusting SQL in Query ...
    (microsoft.public.access.queries)
  • Re: Using combo box to search form
    ... Since you "started adding" additional criteria, ... about SQL syntax to know how to form a grammatically-correct SQL statement. ... One way to do that is to see what Access generates when you build a query. ... Dim strWhere As String ...
    (microsoft.public.access.formscoding)
  • Re: Criteria linked to form combo Yes/No or All expression help
    ... The way I'd debug it would be to start w/ one OR clause in the criteria. ... Run the query & see if it works for that criteria. ... I followed your suggestion of pasting the sql, ...
    (microsoft.public.access.queries)