Received: from mailserv.hub.org (mailserv.hub.org [216.126.84.253]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fA2DMoq09270 for ; Fri, 2 Nov 2001 08:22:50 -0500 (EST) (envelope-from SJohnson6@bcbsm.com) Received: from newman.bcbsm.com (mail.bcbsm.com [167.242.53.42]) by mailserv.hub.org (8.11.4/8.11.4) with ESMTP id fA21Hcf60501 for ; Thu, 1 Nov 2001 20:17:39 -0500 (EST) (envelope-from SJohnson6@bcbsm.com) Received: from snt403.corp.bcbsm.com ([167.242.53.42]) by newman.bcbsm.com (Netscape Messaging Server 4.15) with ESMTP id GM5FL200.283 for ; Thu, 1 Nov 2001 20:17:26 -0500 Received: by snt403.corp.bcbsm.com with Internet Mail Service (5.5.2653.19) id ; Thu, 1 Nov 2001 20:17:26 -0500 Message-ID: <73309C2FDD95D11192E60008C7B1D5BB03FFFF5C@snt452.corp.bcbsm.com> From: "Johnson, Shaunn" To: pgsql-general@postgresql.org Subject: SQL question Date: Thu, 1 Nov 2001 20:17:16 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C1633C.1C26D8A0" X-Archive-Number: 200111/25 X-Sequence-Number: 17030 This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C1633C.1C26D8A0 Content-Type: text/plain; charset="iso-8859-1" Howdy: Not sure if this is the correct group - let me now if I should post elsewhere ... Anyhow, I'm new with SQL. I wrote a script that I *thought* should work. What I'm trying to do is query 5 tables from 5 different databases that have the same columns and collect all of the information and append it into one file. This is the script: [script] SELECT C_CONTRACT_NUM AS CONTRACT, C_MBR_NUM AS MBR_NUM, C_CLASS AS CLASS, MAX(D_END_DT) AS DENDDT FROM database1.table1_CN2PCLAS V GROUP BY C_CLASS, C_CONTRACT_NUM, C_MBR_NUM HAVING MAX(D_END_DT)=( SELECT MAX(D_END_DT) FROM database1.table1_CN2PCLAS W WHERE V.C_CONTRACT_NUM = W.C_CONTRACT_NUM AND V.C_MBR_NUM = W.C_MBR_NUM AND D_END_DT >= '2000-01-01') UNION SELECT C_CONTRACT_NUM AS CONTRACT, C_MBR_NUM AS MBR_NUM, C_CLASS AS CLASS, MAX(D_END_DT) AS DENDDT FROM database2.table1_CN2PCLAS V GROUP BY C_CLASS, C_CONTRACT_NUM, C_MBR_NUM HAVING MAX(D_END_DT)=( SELECT MAX(D_END_DT) FROM database2.table1_CN2PCLAS W WHERE V.C_CONTRACT_NUM = W.C_CONTRACT_NUM AND V.C_MBR_NUM = W.C_MBR_NUM AND D_END_DT >= '2000-01-01') UNION SELECT C_CONTRACT_NUM AS CONTRACT, C_MBR_NUM AS MBR_NUM, C_CLASS AS CLASS, MAX(D_END_DT) AS DENDDT FROM database3.table1_CN2PCLAS V GROUP BY C_CLASS, C_CONTRACT_NUM, C_MBR_NUM HAVING MAX(D_END_DT)=( SELECT MAX(D_END_DT) FROM database3.table1_CN2PCLAS W WHERE D_END_DT >= '2000-01-01') WHERE V.C_CONTRACT_NUM = W.C_CONTRACT_NUM AND V.C_MBR_NUM = W.C_MBR_NUM AND D_END_DT >= '2000-01-01') UNION SELECT C_CONTRACT_NUM AS CONTRACT, C_MBR_NUM AS MBR_NUM, C_CLASS AS CLASS, MAX(D_END_DT) AS DENDDT FROM database4.table1_CN2PCLAS V GROUP BY C_CLASS, C_CONTRACT_NUM, C_MBR_NUM HAVING MAX(D_END_DT)=( SELECT MAX(D_END_DT) FROM database4.table1_CN2PCLAS W WHERE V.C_CONTRACT_NUM = W.C_CONTRACT_NUM AND V.C_MBR_NUM = W.C_MBR_NUM AND D_END_DT >= '2000-01-01') UNION SELECT C_CONTRACT_NUM AS CONTRACT, C_MBR_NUM AS MBR_NUM, C_CLASS AS CLASS, MAX(D_END_DT) AS DENDDT FROM database5.table1_CN2PCLAS V GROUP BY C_CLASS, C_CONTRACT_NUM, C_MBR_NUM HAVING MAX(D_END_DT)=( SELECT MAX(D_END_DT) FROM database5.table1_CN2PCLAS W WHERE V.C_CONTRACT_NUM = W.C_CONTRACT_NUM AND V.C_MBR_NUM = W.C_MBR_NUM AND D_END_DT >= '2000-01-01'); [/script] But the error I'm getting is: [error] The use of the reserved word "WHERE" following "" is not valid. Expected tokens may include: "FOR WITH ORDER UNION EXCEPT QUERYNO OPTIMIZE". [/error] I'm not clear on the meaning of that error. Can someone re-explain what it's trying to say and why the query isn't working? Thanks! -X ------_=_NextPart_001_01C1633C.1C26D8A0 Content-Type: text/html; charset="iso-8859-1" SQL question

Howdy:

Not sure if this is the correct group - let me now
if I should post elsewhere ...

Anyhow, I'm new with SQL.  I wrote a script that I *thought*
should work.  What I'm trying to do is query 5 tables from 5
different databases that have the same columns and collect all
of the information and append it into one file.  This is the script:

[script]

SELECT
C_CONTRACT_NUM AS CONTRACT,
C_MBR_NUM AS MBR_NUM,
C_CLASS AS CLASS,
MAX(D_END_DT) AS DENDDT
FROM database1.table1_CN2PCLAS V
GROUP BY C_CLASS, C_CONTRACT_NUM, C_MBR_NUM
HAVING MAX(D_END_DT)=(
        SELECT MAX(D_END_DT)
        FROM database1.table1_CN2PCLAS W
        WHERE V.C_CONTRACT_NUM = W.C_CONTRACT_NUM
        AND V.C_MBR_NUM = W.C_MBR_NUM
        AND D_END_DT >= '2000-01-01')
UNION
SELECT
C_CONTRACT_NUM AS CONTRACT,
C_MBR_NUM AS MBR_NUM,
C_CLASS AS CLASS,
MAX(D_END_DT) AS DENDDT
FROM database2.table1_CN2PCLAS V
GROUP BY C_CLASS, C_CONTRACT_NUM, C_MBR_NUM
HAVING MAX(D_END_DT)=(
        SELECT MAX(D_END_DT)
        FROM database2.table1_CN2PCLAS W
        WHERE V.C_CONTRACT_NUM = W.C_CONTRACT_NUM
        AND V.C_MBR_NUM = W.C_MBR_NUM
        AND D_END_DT >= '2000-01-01')
UNION
SELECT
C_CONTRACT_NUM AS CONTRACT,
C_MBR_NUM AS MBR_NUM,
C_CLASS AS CLASS,
MAX(D_END_DT) AS DENDDT
FROM database3.table1_CN2PCLAS V
GROUP BY C_CLASS, C_CONTRACT_NUM, C_MBR_NUM
HAVING MAX(D_END_DT)=(
        SELECT MAX(D_END_DT)
        FROM database3.table1_CN2PCLAS W
        WHERE D_END_DT >= '2000-01-01')
        WHERE V.C_CONTRACT_NUM = W.C_CONTRACT_NUM
        AND V.C_MBR_NUM = W.C_MBR_NUM
        AND D_END_DT >= '2000-01-01')
UNION
SELECT
C_CONTRACT_NUM AS CONTRACT,
C_MBR_NUM AS MBR_NUM,
C_CLASS AS CLASS,
MAX(D_END_DT) AS DENDDT
FROM database4.table1_CN2PCLAS V
GROUP BY C_CLASS, C_CONTRACT_NUM, C_MBR_NUM
HAVING MAX(D_END_DT)=(
        SELECT MAX(D_END_DT)
        FROM database4.table1_CN2PCLAS W
        WHERE V.C_CONTRACT_NUM = W.C_CONTRACT_NUM
        AND V.C_MBR_NUM = W.C_MBR_NUM
        AND D_END_DT >= '2000-01-01')
UNION
SELECT
C_CONTRACT_NUM AS CONTRACT,
C_MBR_NUM AS MBR_NUM,
C_CLASS AS CLASS,
MAX(D_END_DT) AS DENDDT
FROM database5.table1_CN2PCLAS V
GROUP BY C_CLASS, C_CONTRACT_NUM, C_MBR_NUM
HAVING MAX(D_END_DT)=(
        SELECT MAX(D_END_DT)
        FROM database5.table1_CN2PCLAS W
        WHERE V.C_CONTRACT_NUM = W.C_CONTRACT_NUM
        AND V.C_MBR_NUM = W.C_MBR_NUM
        AND D_END_DT >= '2000-01-01');

[/script]

But the error I'm getting is:

[error]

The use of the reserved word "WHERE" following "" is not valid.
Expected tokens may include:  "FOR WITH ORDER UNION EXCEPT QUERYNO OPTIMIZE".

[/error]

I'm not clear on the meaning of that error.  Can someone re-explain
what it's trying to say and why the query isn't working?

Thanks!

-X

------_=_NextPart_001_01C1633C.1C26D8A0--