🚀 go-pugleaf

RetroBBS NetNews Server

Inspired by RockSolid Light RIP Retro Guy

Article View: borland.public.interbase.sql
Article #195

Union query errors in Interbase

#195
From: Nick Alexander
Date: Mon, 22 Sep 2008 11:08
39 lines
2551 bytes
I have 2 queries that I want to union.   I can run each query
separately and I get a result.   If I run both of them with a union, I
get the error message:

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Invalid command.
Data type unknown.

The full union query is:

SELECT JOB_COST_HEADER.PROJ_MASTER_REC_ID,
JOB_COST_ITEM.GLOB_PHASE_REC_ID, JOB_COST_ITEM.NAME,
Sum(JOB_COST_ITEM.QUANTITY) AS BudgetQuantity,
Sum(JOB_COST_ITEM.QUANTITY*JOB_COST_ITEM.COST_PRICE) AS BudgetCost,
Sum(0) AS PurchaseQuantity,
Sum(0) AS PurchaseCostPrice
FROM JOB_COST_ITEM INNER JOIN JOB_COST_HEADER ON
JOB_COST_ITEM.JOB_COST_HEADER_REC_ID = JOB_COST_HEADER.REC_ID
WHERE JOB_COST_HEADER.IS_CURRENT='Y' AND JOB_COST_HEADER.HEADER_TYPE=0
GROUP BY JOB_COST_HEADER.PROJ_MASTER_REC_ID,
JOB_COST_ITEM.GLOB_PHASE_REC_ID, JOB_COST_ITEM.NAME

union

SELECT PURCHASE_HEADER.PROJ_MASTER_REC_ID,
PURCHASE_ITEM.GLOB_PHASE_REC_ID, PURCHASE_ITEM.NAME,
Sum(0) AS BudgetQuantity,
Sum(0) AS BudgetCost,
Sum(PURCHASE_ITEM.QUANTITY) AS PurchaseQuantity,
Sum(PURCHASE_ITEM.QUANTITY*PURCHASE_ITEM.COST_PRICE) AS
PurchaseCostPrice
FROM PURCHASE_ITEM INNER JOIN PURCHASE_HEADER ON
PURCHASE_ITEM.PURCHASE_HEADER_REC_ID = PURCHASE_HEADER.REC_ID
GROUP BY PURCHASE_HEADER.PROJ_MASTER_REC_ID,
PURCHASE_ITEM.GLOB_PHASE_REC_ID, PURCHASE_ITEM.NAME

Can anyone shed any light?
Technical Details
Message-ID: <ee73d23d-8d25-415b-bf8f-c5040f682787@r15g2000prh.googlegroups.com>
Path: arc1-ca.pugleaf.net!g2news1.google.com!postnews.google.com!r15g2000prh.googlegroups.com!not-for-mail

Show Full Headers
Complete Article Headers
X-Google-Thread: 114a0e,a063878e6e5825d5,start
X-Google-Attributes: gid114a0e,domainid0,public,usenet
X-Google-Language: ENGLISH,ASCII-7-bit
Path: arc1-ca.pugleaf.net!g2news1.google.com!postnews.google.com!r15g2000prh.googlegroups.com!not-for-mail
From: Nick Alexander <nic.alexander@gmail.com>
Newsgroups: borland.public.interbase.sql
Subject: Union query errors in Interbase
Date: Mon, 22 Sep 2008 04:08:26 -0700 (PDT)
Organization: http://groups.google.com
Lines: 39
Message-ID: <ee73d23d-8d25-415b-bf8f-c5040f682787@r15g2000prh.googlegroups.com>
NNTP-Posting-Host: 202.89.159.187
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Trace: posting.google.com 1222081706 4804 127.0.0.1 (22 Sep 2008 11:08:26 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Mon, 22 Sep 2008 11:08:26 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: r15g2000prh.googlegroups.com; posting-host=202.89.159.187; 
 posting-account=tF7f_woAAACRsymyIrTY-wLCwo21kD1g
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 
 1.1.4322; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe)