OpenQuery AND Where Question

OpenQuery AND Where Question

Post by Pablo Cru » Sun, 23 Dec 2001 07:11:10



I need help with a query. I'm currently querying an
external database. I'm using the following query:
Select SAT.BAN
From TSUTSONE AS SAT, OpenQuery (Dataware, 'Select BAN
From LD.TAX') AS ED WHERE SAT.BAN=ED.BAN
The query will run for hours but will not return any
results. If I add "Where BAN='11111'" right after
the "From LD.TAX" it works fine but when I move
the "where" part outside it will not work. What I want to
do is to compare the column "BAN" on both databases and if
they match to display both columns. Can you help???
 
 
 

OpenQuery AND Where Question

Post by Dwayne Lanclos [M » Sat, 29 Dec 2001 01:03:04


Briefly, when you use the OPEN QUERY syntax, anything within the
parentheses is passed to the linked server as-is.  So when you provide a
WHERE clause, this is processed on the other server and reduces the amount
of data that needs to be sent to the server from where you are invoking the
query.  If you don't restrict the OPEN QUERY statement, then you are
forcing the linked server to return too many rows, most of which you are
not interested in anyway.

This newsgroup is intended for Microsoft English Query questions.  You
should direct this query to the microsoft.public.sqlserver.programming
newsgroup.

------
Dwayne Lanclos
Microsoft SQL Server Support

Please reply only to the newsgroup so that others can benefit.
When posting, please state the version of SQL Server being used and the
error number/exact error message text received, if any.
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------

> Content-Class: urn:content-classes:message

> Newsgroups: microsoft.public.sqlserver.mseq

> I need help with a query. I'm currently querying an
> external database. I'm using the following query:
> Select SAT.BAN
> From TSUTSONE AS SAT, OpenQuery (Dataware, 'Select BAN
> From LD.TAX') AS ED WHERE SAT.BAN=ED.BAN
> The query will run for hours but will not return any
> results. If I add "Where BAN='11111'" right after
> the "From LD.TAX" it works fine but when I move
> the "where" part outside it will not work. What I want to
> do is to compare the column "BAN" on both databases and if
> they match to display both columns. Can you help???


 
 
 

1. Openquery question

Howdy everyone.
I'm using a openquery to return data from a Teradata box to my SQL server.
I've successfully run the query in Teradata, but when I try it in my query,
I keep getting an error message about a column not existing.  The column the
error message returns is part of my criteria.  If I remove this criteria, my
query works.
I'm using dates, so I don't know if this is causing problems.  Here is my
query:

Select * from OPENQUERY(ddw,
'select a4.item_upc,(abs(SUM(a1.can_tot+(a1.can_cust+a1.can_credit))))
NETVOIDUNITS,
 (ABS(SUM(a1.can_tot_dlrs+(a1.can_cust_dlrs+a1.can_credit_dlrs))))  NETVOID,
 (SUM(a1.confirmed_units-ABS(a1.can_credit+a1.can_cust)))  NETCONFIRMEDU,
 (SUM(a1.confirmed_units_dlrs-ABS(a1.can_credit_dlrs+a1.can_cust_dlrs)))
NETCONFIRMED
from  RRR_PROD.TRAN_ITEM_SUMMARY_V a1,
 RRR_PROD.ITEM_SKU_V a2,
 RRR_PROD.CLASS_V a3,
 RRR_PROD.ITEM_V a4,
 RRR_PROD.PROD_YDEPT_V a5,
 RRR_PROD.PROD_YBUYER_V a6,
 RRR_PROD.PROD_YMGM_V a7,
 RRR_PROD.VEND_V a8,
 RRR_PROD.PROD_YDIV_V a9,
 RRR_PROD.PROD_YPDIV_V a10

where  a2.ITEM_SKU_ID = a1.ITEM_SKU_ID
  and  a3.CLASS_ID = a2.CLASS_ID
  and  a4.ITEM_ID = a1.ITEM_ID
  and  a5.YDEPT_ID = a3.YDEPT_ID
  and  a6.YBUYER_ID = a5.YBUYER_ID
  and  a7.YMGM_ID = a6.YMGM_ID
  and  a2.DIV_VEND_ID = a8.DIV_VEND_ID
  and  a7.YDIV_ID = a9.YDIV_ID
  and  a10.YPDIV_ID = a9.YPDIV_ID
/* is there a problem with passing date as string? */
  and  (a1.BOOK_DATE_FIRST Between 12/17/2000 AND 12/23/2000)
/* error occurs here */
  and   (a1.APPL_ID = MCOM)
Group by a4.item_upc')

Here is the error message returned:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'MSDASQL'.
[OLE/DB provider returned message: [NCR][Teradata RDBMS] Column 'MCOM' does
not exist. ]

TIA
Patrick Hatcher

2. EBU - Please advise...

3. OPENQUERY Question..

4. Using Oracle to facade Sybase

5. OPENQUERY() question

6. HELP : Multi-Record Report.

7. OPENQUERY Question

8. SQL Server 7...can you iterate through Check constraints in VB?

9. question about openquery

10. OPENQUERY Question

11. OpenQuery Question

12. OPENQUERY processing

13. PLEASE HELP! MS Indexing Service / OPENQUERY