Link to home
Create AccountLog in
Avatar of kpurchase
kpurchase

asked on

SQL Union Statement

I have the following SQL Union statement that I am trying to perform and it returns an error "Expected Lexical Element Not Found" this is being returned by an ODBC driver of the database I am connecting to. But I really am checking to see if this UNION statement is correct...

(SELECT MASTER.FORW_NO FROM MASTER INNER JOIN FINAN ON
MASTER.FILENO = FINAN.FILENO WHERE FINAN.TYPE = 'B' AND ((FINAN.TRANS_DATE) BETWEEN #1/1/2007# AND #12/31/2007#) AND
((MASTER.DATE_RECD) BETWEEN #1/1/2007# AND #12/31/2007#))
UNION ALL
(SELECT HISTORY.FORW_NO FROM HISTORY INNER JOIN FINANH ON
HISTORY.FILENO = FINANH.FILENO WHERE FINANH.TYPE = 'B' AND ((FINANH.TRANS_DATE) BETWEEN #1/1/2007# AND #12/31/2007#) AND
((HISTORY.DATE_RECD) BETWEEN #1/1/2007# AND #12/31/2007#));

Note that History and Master tables are identical except for the fact that History contains information about closed accounts and Master provides information about open accounts. I am trying to get all of the information on all accounts, opened or closed. Also note FINAN and FINANH tables are identical as well except that FINANH table contains information regarding closed accounts and FINAN table contains information regarding open accounts.
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

remove the "( )"

SELECT MASTER.FORW_NO FROM MASTER INNER JOIN FINAN ON
MASTER.FILENO = FINAN.FILENO WHERE FINAN.TYPE = 'B' AND ((FINAN.TRANS_DATE) BETWEEN #1/1/2007# AND #12/31/2007#) AND
((MASTER.DATE_RECD) BETWEEN #1/1/2007# AND #12/31/2007#)
UNION ALL
SELECT HISTORY.FORW_NO FROM HISTORY INNER JOIN FINANH ON
HISTORY.FILENO = FINANH.FILENO WHERE FINANH.TYPE = 'B' AND ((FINANH.TRANS_DATE) BETWEEN #1/1/2007# AND #12/31/2007#) AND
((HISTORY.DATE_RECD) BETWEEN #1/1/2007# AND #12/31/2007#)

also do you get results running the queries individually


SELECT MASTER.FORW_NO FROM MASTER INNER JOIN FINAN ON
MASTER.FILENO = FINAN.FILENO WHERE FINAN.TYPE = 'B' AND ((FINAN.TRANS_DATE) BETWEEN #1/1/2007# AND #12/31/2007#) AND
((MASTER.DATE_RECD) BETWEEN #1/1/2007# AND #12/31/2007#)


Avatar of kpurchase
kpurchase

ASKER

After removing parenthesis I get the same error however both queries do provide data. I am trying to merge the two as opposed to some manual method of merging the two recordsets. And I tried to simplify the query to get it to work and move from there... what I was originally trying to do was to combine two cross tab sql statements which is the following:
(TRANSFORM Sum(FINANH.RECEIVED - FINANH.COSTS) AS SumOfRECEIVED SELECT HISTORY.FORW_NO FROM HISTORY INNER JOIN FINANH ON
HISTORY.FILENO = FINANH.FILENO WHERE FINANH.TYPE = 'B' AND ((FINANH.TRANS_DATE) BETWEEN #1/1/2007# AND #12/31/2007#) AND
((HISTORY.DATE_RECD) BETWEEN #1/1/2007# AND #12/31/2007#))
UNION ALL
(TRANSFORM Sum(FINAN.RECEIVED - FINAN.COSTS) AS SumOfRECEIVED SELECT MASTER.FORW_NO FROM MASTER INNER JOIN FINAN ON
MASTER.FILENO = FINAN.FILENO WHERE FINAN.TYPE = 'B' AND ((FINAN.TRANS_DATE) BETWEEN #1/1/2007# AND #12/31/2007#) AND
((MASTER.DATE_RECD) BETWEEN #1/1/2007# AND #12/31/2007#))
ORDER BY FORW_NO

I just didnt know if I the transforms or something were causing the problem as well.
run this

SELECT MASTER.FORW_NO FROM MASTER INNER JOIN FINAN ON
MASTER.FILENO = FINAN.FILENO WHERE FINAN.TYPE = 'B' AND ((FINAN.TRANS_DATE) BETWEEN #1/1/2007# AND #12/31/2007#) AND
((MASTER.DATE_RECD) BETWEEN #1/1/2007# AND #12/31/2007#)
UNION ALL
SELECT HISTORY.FORW_NO FROM HISTORY INNER JOIN FINANH ON
HISTORY.FILENO = FINANH.FILENO WHERE FINANH.TYPE = 'B' AND ((FINANH.TRANS_DATE) BETWEEN #1/1/2007# AND #12/31/2007#) AND
((HISTORY.DATE_RECD) BETWEEN #1/1/2007# AND #12/31/2007#)

saved the query as  qryUnion

then use the qryUnion to create the crosstab query



When trying to run the query you created above, prior to attempting to create the cross tab, I get the same error message "[ADS][BRODBC]Expected lexical element not found: <keyword> (#1015)".. any ideas?
hmm.. you are connecting to ODBC tables
you may have to change your queries to Pass through queries.
I have never used a pass through query unfortunately.. based upon my two individual queries:
1:
(TRANSFORM Sum(FINANH.RECEIVED - FINANH.COSTS) AS SumOfRECEIVED SELECT HISTORY.FORW_NO FROM HISTORY INNER JOIN FINANH ON
HISTORY.FILENO = FINANH.FILENO WHERE FINANH.TYPE = 'B' AND ((FINANH.TRANS_DATE) BETWEEN #1/1/2007# AND #12/31/2007#) AND
((HISTORY.DATE_RECD) BETWEEN #1/1/2007# AND #12/31/2007#))
GROUP BY FORW_NO ORDER BY FORW_NO PIVOT
Format([DATE_RECD],'mmm') In ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

2:
(TRANSFORM Sum(FINAN.RECEIVED - FINAN.COSTS) AS SumOfRECEIVED SELECT MASTER.FORW_NO FROM MASTER INNER JOIN FINAN ON
MASTER.FILENO = FINAN.FILENO WHERE FINAN.TYPE = 'B' AND ((FINAN.TRANS_DATE) BETWEEN #1/1/2007# AND #12/31/2007#) AND
((MASTER.DATE_RECD) BETWEEN #1/1/2007# AND #12/31/2007#))
GROUP BY FORW_NO ORDER BY FORW_NO PIVOT
Format([DATE_RECD],'mmm') In ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

do you see an easy transition to get to a pass through query that would merge the data into a recordset based upon MASTER.FORW_NO?
In the WHERE clause replace the BETWEEN stuff with Year(fldDate)=2007
Mixing UNION and TRANSFORM queries is often tricky. The optimizer will attempt to reorganize the logic, and often gets it wrong. If all else fails, you will need an intermediate temporary table (make-table from the UNION, then TRANSFORM, or make-table from both TRANSFORMs and then the UNION).

It's often best to start with the UNION, as Cap has already suggested (but without including the fields needed for the cross-tab). Try to adjust the following UNION query, which pre-calculates the month abbreviations and the delta between costs and payments, and make it work:

SELECT
  HISTORY.FORW_NO,
  Format(HISTORY.DATE_RECD,'mmm') AS MONTHABBR,
  FINANH.RECEIVED - FINANH.COSTS AS DELTA
FROM HISTORY INNER JOIN FINANH ON HISTORY.FILENO = FINANH.FILENO
WHERE FINANH.TYPE = 'B'
  AND FINANH.TRANS_DATE BETWEEN #1/1/2007# AND #12/31/2007#
  AND HISTORY.DATE_RECD BETWEEN #1/1/2007# AND #12/31/2007#

UNION ALL

SELECT
  MASTER.FORW_NO,
  Format(MASTER.DATE_RECD,'mmm') AS MONTHABBR,
  FINAN.RECEIVED - FINAN.COSTS AS DELTA
FROM MASTER INNER JOIN FINAN ON MASTER.FILENO = FINAN.FILENO
WHERE FINAN.TYPE = 'B'
  AND FINAN.TRANS_DATE BETWEEN #1/1/2007# AND #12/31/2007#
  AND MASTER.DATE_RECD BETWEEN #1/1/2007# AND #12/31/2007#

Save this as quniOverview and then try:

TRANSFORM Sum(DELTA)
SELECT FORW_NO
FROM quniOverview
GROUP BY FORW_NO
PIVOT MONTHABBR In ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

If that doesn't work, you will have to replace quniOverview with a temporary table.

Cheers!
(°v°)
@GRayL,

Using a VB function like Year() in a WHERE, ORDER BY, or GROUP BY clause prevents the database engine from using an index. The Between / And syntax is always faster, sometimes spectacularly!

(°v°)
I would split your original union query into two saved (normal select) queries.
Then create a third union query to union these two queries.

Finally, if you need a transform query, use the union query as source.

If the two base queries run correctly via ODBC there is no reason to move to pass-through queries other than it may be faster. But that I would regard as an option.

/gustav
harfang:  Thanks, I'll try to remember that.

Ray
OK maybe I wasnt asking for the right thing. I have now placed the results into two temporary tables of my two above recordsets. And I performed the a simple union.

However because I am a newbie maybe I didnt want a union..

What I was hoping to do was combine the data from the two tables with the union rather than just have them show up in the same recordset. In example say for "Jan" FORW_NO 1 has 100 dollars in the closed accounts, tblTemp1 - results of query1, and 200 dollars in the open accounts, tblTemp2 - results of query 2. What I would like to happen is that for FORW_NO 1 in the "Jan" column it to show up as 300 dollars.    is this possible? I have this feeling it has to be but i cant seem to get it right... please help.
try this query format

select FORW_NO,Sum([Jan]) As SumOFJan
From
(select FORW_NO, Jan
from tbltemp1
union all select FORW_NO, Jan
from tbltemp2)
group by FORW_NO

ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Oh my good god you're a genius... Take all the points and if I knew how to give you more I would... You're a god send I have been working on this for more than two days. Now unfortunately i have to do the same thing with about 8 more queries but you just put me in a good place. Happy Friday.
Thanks again... its people like you that allow people like me to get through the tricky stuff.