Re: Need someone to look at SQL statement

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Tom Ellison (tellison_at_jcdoyle.com)
Date: 08/03/04 

Date: Tue, 03 Aug 2004 10:39:46 -0500

Dear D:

Simplify! Simplify! Simplify!

I think you would have this error with just the middle SELECT query by
itself. If so, then the rest of this SQL is unnecessary to fixing the
problem. Look at just that query:

SELECT T1.[PolicyNumber], T1.[Net Investment Amount],
    T1.[Anticipated Investment Fund Name], T1.[Contact Fund?],
    T1.[Confirm to Accounting?], T1.[AccountID (from)],
    T1.[Account ID (to)], T1.[Sub Doc], T1.[Cover Letter],
    T1.[Trade Date per Info *** (for new premium only)],
    T1.[Effective Date], T1.[Wire Date], T1.[Sub Doc Date],
    T1.[Asset Allocation Instructions],
    T1.[Client Allocation Instructions], T1.[Wire Instructions],
    T1.[Status], T1.[Complete], T3.[SiteIDNumber], T4.Custodian,
    Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null,
    Null, Null, Null
 FROM ([Money Market Reserve] AS T1
    INNER JOIN ([Account Inventory] AS T3
      INNER JOIN [Site Inventory] AS T4
        ON T3.SiteIDNumber = T4.SiteIDNumber)
      ON T1.[Account ID (to)] = T3.AccountID)
  WHERE [PolicyNumber] NOT IN
    (SELECT DISTINCT [PolicyNumber] FROM [Pending Transactions])

I have formatted this to my personal liking to help me study it.

Now, in the WHERE clause you reference [PolicyNumber] without
specifying in which table it is found. The error message is about
this. The query references 3 tables:

[Money Market Reserve]
[Account Inventory]
[Site Inventory]

Apparently, the column named PolicyNumber appears in more than one of
these 3 tables. So, the query cannot resolve this ambiguity and gives
you that error message.

So, change that part of the query to read:

  WHERE XX.[PolicyNumber] NOT IN

replacing XX with T1, T3, or T4, depending on which of those 3 tables
contains the PolicyNumber you want to test. It may well be that the
different PolicyNumber columns will always contain the same value, but
that doesn't matter. This error is detected in the query syntax
before the query even starts looking at the data. So, it may not be
ambiguous in reality, but it is ambiguous syntactically.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

On Tue, 3 Aug 2004 11:14:58 -0400, "Dkline" <Dkline001@comcast.net>
wrote:

>SELECT T1.PolicyNumber, T1.[Net Investment Amount], T1.[Anticipated
>Investment Fund Name], T1.[Contact Fund?], T1.[Confirm to Accounting?],
>T1.[AccountID (from)], T1.[Account ID (to)], T1.[Sub Doc], T1.[Cover
>Letter], T1.[Trade Date per Info *** (for new premium only)],
>T1.[Effective Date], T1.[Wire Date], T1.[Sub Doc Date], T1.[Asset Allocation
>Instructions], T1.[Client Allocation Instructions], T1.[Wire Instructions],
>T1.Status, T1.Complete, T3.SiteIDNumber, T4.Custodian, T2.ID, T2.Requestor,
>T2.[Request Type], T2.PolicyNumber, T2.AccountID, T2.[Date Redemption
>Submitted], T2.[Requested Effective Date], T2.[Full or Partial], T2.[Partial
>Amount], T2.[Date Expected], T2.[% or $ Expected], T2.[Date Residual
>Expected], T2.[Residual % or $ Expected], T2.Comment
>FROM ([Money Market Reserve] AS T1 INNER JOIN ([Account Inventory] AS T3
>INNER JOIN [Site Inventory] AS T4 ON T3.SiteIDNumber = T4.SiteIDNumber) ON
>T1.[Account ID (to)] = T3.AccountID) INNER JOIN [Pending Transactions] AS T2
>ON T1.PolicyNumber = T2.PolicyNumber
>UNION
>SELECT T1.[PolicyNumber], T1.[Net Investment Amount], T1.[Anticipated
>Investment Fund Name], T1.[Contact Fund?], T1.[Confirm to Accounting?],
>T1.[AccountID (from)], T1.[Account ID (to)], T1.[Sub Doc], T1.[Cover
>Letter], T1.[Trade Date per Info *** (for new premium only)],
>T1.[Effective Date], T1.[Wire Date], T1.[Sub Doc Date], T1.[Asset Allocation
>Instructions], T1.[Client Allocation Instructions], T1.[Wire Instructions],
>T1.[Status], T1.[Complete], T3.[SiteIDNumber], T4.Custodian, Null, Null,
>Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null
>FROM ([Money Market Reserve] AS T1 INNER JOIN ([Account Inventory] AS T3
>INNER JOIN [Site Inventory] AS T4 ON T3.SiteIDNumber = T4.SiteIDNumber) ON
>T1.[Account ID (to)] = T3.AccountID)
>WHERE [PolicyNumber] NOT IN (SELECT DISTINCT [PolicyNumber]
>FROM [Pending Transactions])
>UNION
>SELECT T2.[PolicyNumber], Null, Null, Null, Null, Null, Null, Null, Null,
>Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, T2.[ID],
>T2.[Requestor], T2.[Request Type], T2.[PolicyNumber], T2.[AccountID],
>T2.[Date Redemption Submitted], T2.[Requested Effective Date], T2.[Full or
>Partial], T2.[Partial Amount], T2.[Date Expected], T2.[% or $ Expected],
>T2.[Date Residual Expected], T2.[Residual % or $ Expected], T2.[Comment]
>FROM [Pending Transactions] AS T2
>WHERE [PolicyNumber] NOT IN (SELECT DISTINCT [PolicyNumber]
>FROM [Money Market Reserve]);
>
>I've got four tables in here.
>T1 = [Money Market Reserve]
>T2 = [Pending Transactions]
>T3 = [Account Inventory]
>T4 = [Site Inventory]
>
>It is the "middle" one (between the two UNIONs) that is causing the
>difficulty. I get an error message of "The specified filed '[PolicyNumber]'
>could refer to more than one table used in the FROM clause of your SQL
>statement."
>
>If I copy just the portion above the first UNION into the SQL statement it
>runs perfectly. If I copy just the bottom portion below the second UNION
>into the SQL statement it runs perfectly.
>
>I can't see straight anymore from looking at this. A second set of eyes
>looking at this would be appreciated.
>
>
>
>

Quantcast