Union only returning records from first query

[morechocolate]

Version: Crystal 8.5

I have created a Crystal Report which is using the UNION clause, however, only the first query is returning data.

The second query is using some different fields than the first and the selection criteria is slighty different. I do, however, have the same number of columns and same data types. So that is not an issue.

When I copy the SQL from Show Query and run it in SQL Serever it works fine.

Any ideas on what I may need to change?

 

[synapsevampire]

A Union doesn't return different fields, you should be aliasing the columns, as in:

select fld1 val1, fld2 val2, fld3 val3 from Table1
union all
select mor1 val1, mor2 val2, mor3 val3 from Table2

Not sure if that's what you meant, it would make more sense to post it than try to describe it.

-k

 

[morechocolate]

Thanks for the prompt response as always K.

This may be a dumb question, but can you actually alias field objects (data fields that were dropped into the details section)?

 

[synapsevampire]

No, alias your UNION ALL query to have the same name as those in the main query.

I should have posted:

select val1, val2, val3 from Table1
union all
select mor1 val1, mor2 val2, mor3 val3 from Table2

-k

 

[IdoMillet]

There should be no need to alias the columns in the 2nd SELECT. The column names are dictated by the first SELECT.

Perhaps there's a record or group selection formula in the Crystal report that is causing the records from the 2nd select to be dropped.

hth,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

Ido: If the columns use reserved words then it can cause problems, hence the siggestion.

-k

 

[morechocolate]

Thanks Ido,

I actually have been playing around with that (the selection criteria) based on feedback that I saw from you on other posts regarding unions.

A matter of fact, a couple of months ago, I used one of your posts to solve a slightly different union problem.

 

[lbass]

If your where statement in the first select is limiting a column to a result that is only available in the first set of data, only the first set can be returned.

Maybe you should copy your statement into the post.

-LB

 

[IdoMillet]

Since the query worked fine directly against the server, I don't expect the problem to be with reserved words or with the query itself. That's why I suggested looking at the record selection within Crystal.

hth,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[morechocolate]

The following is the resulting SQL statement in Show SQL - the where clause in the first query was created by the Record Selection Criteria

SELECT
LOAN_IPT.LOAN, LOAN_IPT.SHORT_NAME, LOAN_IPT.INTEREST, LOAN_IPT.PCT_INTEREST, LOAN_IPT.END_DATE, LOAN_IPT.GUARANTY_TYPE,
LOANHIST.CORRESPONDENT, LOANHIST.LOAN_NAME, LOANHIST.LOAN_KIND, LOANHIST.CLOSED_DATE, LOANHIST.ORIG_PRIN_BAL,
ACCOUNTS.PRI_ACCTG_DATE,
BALHIST.PRIN_BAL_P,
EXTGENERAL.NYL_PART_PER, EXTGENERAL.BANK_DEAL ,
'N',
{fn IFNULL(EXTSERVINFO.SERV_FEE,0 )},
SERV_FEE_RECVD = 0
FROM
{ oj ((((LMS_NYL.dbo.LOAN_IPT LOAN_IPT INNER JOIN LMS_NYL.dbo.LOANHIST LOANHIST ON
LOAN_IPT.LOAN = LOANHIST.LOAN)
INNER JOIN LMS_NYL.dbo.BALHIST BALHIST ON
LOANHIST.LOAN = BALHIST.LOAN AND
LOANHIST.ACCOUNTING_DATE = BALHIST.ACCOUNTING_DATE)
INNER JOIN LMS_NYL.dbo.EXTGENERAL EXTGENERAL ON
LOANHIST.LOAN = EXTGENERAL.LOAN)
INNER JOIN LMS_NYL.dbo.ACCOUNTS ACCOUNTS ON
LOANHIST.ACCOUNT = ACCOUNTS.ACCOUNT AND
LOANHIST.ACCOUNTING_DATE = ACCOUNTS.PRI_ACCTG_DATE)
LEFT OUTER JOIN LMS_NYL.dbo.EXTSERVINFO EXTSERVINFO ON
LOANHIST.LOAN = EXTSERVINFO.LOAN}
WHERE
(LOAN_IPT.END_DATE IS NULL OR
LOAN_IPT.END_DATE >= {ts '2005-03-16 00:00:00.00'}) AND
LOAN_IPT.INTEREST = 'PARTICIPANT' AND
EXTGENERAL.BANK_DEAL = 'N' AND
LOANHIST.LOAN_KIND <> 'MEZZANIN'
UNION
SELECT COMPHIST.LOAN, COMPHIST.PARTICIPANT,NULL,
(select i.percentage
from prtbkinv i
where i.loan = COMPHIST.LOAN and
i.participant = COMPHIST.PARTICIPANT and
i.name = 'principal'),NULL,PARTMAST.INVESTOR_TYPE,LOANHIST.CORRESPONDENT,
LOANHIST.LOAN_NAME, LOANHIST.LOAN_KIND, LOANHIST.CLOSED_DATE,
LOANHIST.ORIG_PRIN_BAL,ACCOUNTS.PRI_ACCTG_DATE,
COMPHIST.PRIN_BAL_P,EXTGENERAL.NYL_PART_PER, EXTGENERAL.BANK_DEAL,
TRACK = 'Y', EXTSERVINFO.SERV_FEE,PRTBRKDN.SERV_FEE_RATE
FROM { oj (((((LMS_NYL.dbo.COMPHIST COMPHIST
INNER JOIN LMS_NYL.dbo.EXTGENERAL EXTGENERAL ON
COMPHIST.LOAN = EXTGENERAL.LOAN)
INNER JOIN LMS_NYL.dbo.LOANHIST LOANHIST ON
COMPHIST.LOAN = LOANHIST.LOAN AND
COMPHIST.ACCOUNTING_DATE = LOANHIST.ACCOUNTING_DATE)
INNER JOIN LMS_NYL.dbo.PRTBRKDN PRTBRKDN ON
COMPHIST.LOAN = PRTBRKDN.LOAN AND
COMPHIST.PARTICIPANT = PRTBRKDN.PARTICIPANT)
INNER JOIN LMS_NYL.dbo.ACCOUNTS ACCOUNTS ON
COMPHIST.ACCOUNT = ACCOUNTS.ACCOUNT AND
COMPHIST.ACCOUNTING_DATE = ACCOUNTS.PRI_ACCTG_DATE)
INNER JOIN LMS_NYL.dbo.PARTMAST PARTMAST ON
COMPHIST.PARTICIPANT = PARTMAST.PARTICIPANT)
LEFT OUTER JOIN LMS_NYL.dbo.EXTSERVINFO EXTSERVINFO ON
COMPHIST.LOAN = EXTSERVINFO.LOAN} WHERE
LOANHIST.LOAN_KIND <> 'MEZZANIN' AND
EXTGENERAL.BANK_DEAL = 'N' AND
PARTMAST.INVESTOR_TYPE = 'EXTERNAL'

 

[morechocolate]

One thing I forgot to mention is that LOAN_IPT.GUARANTY_TYPE field in the first query has 12 characters and the corresponding PARTMAST.INVESTOR_TYPE in the second query can have a maximum of 8 characters. I used the LOAN_IPT.GUARANTY_TYPE field so I could have a place holder since I knew I would need to select on the PARTMAST.INVESTOR_TYPE.

That is what I was playing around with for the selection criteria, but it was not working.

 

[kskid]

Then try this

SELECT
blah...
LOAN_IPT.GUARANTY_TYPE,
NULL,
clah...
UNION
SELECT
blah...
NULL,
PARTMAST.INVESTOR_TYPE,
blah...

-LW

 

[morechocolate]

kskid, thanks for the response, but I am already doing that, unless I am looking at my queries incorrectly.

 

[kskid]

Breaking down your selected fields, I noticed some discrepancies. The following items not work in SQL (at least not in Oracle).

1st Select
- On item 18, what is SERV_FEE_RECVD = 0? Is this what you want or
do you want a service rate to go with the 2nd select?

UNION

2nd select
- On item 16, what is TRACK = 'Y',?

1 LOAN_IPT.LOAN,
2 LOAN_IPT.SHORT_NAME,
3 LOAN_IPT.INTEREST,
4 LOAN_IPT.PCT_INTEREST,
5 LOAN_IPT.END_DATE,
6 LOAN_IPT.GUARANTY_TYPE,
7 LOANHIST.CORRESPONDENT,
8 LOANHIST.LOAN_NAME,
9 LOANHIST.LOAN_KIND,
10 LOANHIST.CLOSED_DATE,
11 LOANHIST.ORIG_PRIN_BAL,
12 ACCOUNTS.PRI_ACCTG_DATE,
13 BALHIST.PRIN_BAL_P,
14 EXTGENERAL.NYL_PART_PER,
15 EXTGENERAL.BANK_DEAL ,
16 'N',
17 {fn IFNULL(EXTSERVINFO.SERV_FEE,0 )},
18 SERV_FEE_RECVD = 0



1 COMPHIST.LOAN,
2 COMPHIST.PARTICIPANT,
3 NULL,
4 (select i.percentage
from prtbkinv i
where i.loan = COMPHIST.LOAN and
i.participant = COMPHIST.PARTICIPANT and
i.name = 'principal'),
5 NULL,
6 PARTMAST.INVESTOR_TYPE,
7 LOANHIST.CORRESPONDENT,
8 LOANHIST.LOAN_NAME,
9 LOANHIST.LOAN_KIND,
10 LOANHIST.CLOSED_DATE,
11 LOANHIST.ORIG_PRIN_BAL,
12 ACCOUNTS.PRI_ACCTG_DATE,
13 COMPHIST.PRIN_BAL_P,
14 EXTGENERAL.NYL_PART_PER,
15 EXTGENERAL.BANK_DEAL,
16 TRACK = 'Y',
17 EXTSERVINFO.SERV_FEE,
18 PRTBRKDN.SERV_FEE_RATE

 

[morechocolate]

hi kskid,

Item 16 in select 1 and Item 18 in both selects are SQL expressions.

Basically, these are not actual fields in the tables, but I need these default values. When working with SQL Server you can do this and it works fine. I was struggling with what to do for the Crystal report. Item 18 is the most important because those help me separate what the two selects (first select and second select) are doing. Each one represents something different.

Thanks for your time and feedback.

 

[kskid]

What about item 4 in the 2nd select. Is the value in i.name ='principal' in in lowercase as you have specified or should it be i.name = 'PRINCIPAL'?

-LW

 

[morechocolate]

That's a subquery built via a SQL Expression. There is no case sensitivity. Likewise, I was able to run the second select separately in Crystal with no problems.

 

[kskid]

I noticed you that you are using Crystal 8.5. Do you have Crystal SQL Designer?. I have used it to debug my unions. If so, from Databse->Show SQL Query, click cut and paste it in Crystal SQL Designer and see if it runs OK.

 

[morechocolate]

Thank You all for your help. I finally figured out the problem. Like Ido mentioned it had to be something with the record selection criteria. There were two pieces of the record selection criteria that I had to modify.

(1) I had to select the LOAN_IPT.GUARANTY_TYPE as

LOAN_IPT.GUARANTY_TYPE = '' or LOAN_IPT.GUARANTY_TYPE = 'EXTERNAL'

The first half of the statement selected the records for the first select and the second part took care of the desired records for the second select.

(2) The other adjustment was

(ISNULL({LOAN_IPT.INTEREST}) or {LOAN_IPT.INTEREST} = 'PARTICIPANT')

The first half of the statement selected the records for the second select and the second part took care of the desired records for the first select.

Thanks again

mc