Re: Need someone to look at SQL statement FIXED THE PROBLEM

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

Date: Tue, 03 Aug 2004 10:44:19 -0500

Dear D:

Yah!

The syntax should look like this, however:

WHERE T1.[PolicyNumber] NOT IN

with the T1 outside the brackets. I'm a bit surprised it would work
the way you stated. [T1.PolicyNumber] should reference a column with
the name "T1.PolicyNumber" rather than a column named PolicyNumber in
the table aliased as T1.

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

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

>Discovered that the T3 table [Account Inventory] also had [PolicyNumber] as
>a field. Specifiying the table in the WHERE as "WHERE [T1.PolicyNumber] NOT
>IN (SELECT DISTINCT [PolicyNumber]" solved the problem.
>
>Sorry for the false alarm.
>
>"Dkline" <Dkline001@comcast.net> wrote in message
>news:%23RtMlyWeEHA.3632@TK2MSFTNGP11.phx.gbl...
>> 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 Sheet (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 Sheet (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.
>>
>>
>>
>>
>>
>

Relevant Pages

  • Re: UNION of two SELECT extremely slow
    ... I've localized the problem and it is about UNION operation. ... Another way to help the SQL statement is to remove the subquery, ... The inline view only needs to be resolved once, ... VW.SUPERCATEGORY_ID AS PARENTID, ...
    (comp.databases.oracle.misc)
  • Re: Querying a dataset
    ... > Rep, Area, Period, Qty. ... > I want to display this data on a datagrid like: ... just glancing at your sql statement. ... > UNION ALL ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Union view limits
    ... Art Kagel wrote: ... single SQL statement which is 65535 characters, so if each table and column name is two characters long that works out to about 3449 SELECTS in an maximum length UNION less the characters needed to define the VIEW. ... Suppose you approach this 65K limit on the length of SQL statement in your CREATE UNION command. ...
    (comp.databases.informix)
  • Error Message - Help Needed
    ... I'm trying to use a sql statement in an asp page. ... I have 2 separate statements that work, but I'm trying to UNION them. ... FROM AS SumOfSDAEXP ... The tables that end in A are doing current sales, the table that ends in B is doing historical sales, both have EXACTLY the same column names. ...
    (microsoft.public.sqlserver.programming)
  • Re: Import .csv question
    ... I found that the SQL statement that you ... > UNION ... > Sub ImportOneFile(FileSpec As String) ... > Dim strFolder As String ...
    (microsoft.public.access.externaldata)