union query conditional

I am using following Union Query.
And its working like charm.

SELECT CM.CM_Date AS Cdate, CM.Cash_MemoNo AS CmNo,0 as BookDate, 0 as LR_No, CM.CM_Total as CMAmt, 0 as Amt_Rcvd, 0 as ChqDDNo, 0 as ChqDDdate, 0 as Acc_Typ, 0 as RcptNo,0 as MemoNo, 0 as M_Date, CM.CNee as conName
from (CashMemo as CM
INNER JOIN ClientLedger ON (ClientLedger.CName = CM.CNee))
UNION ALL SELECT Receipt_CLNT.Rcpt_Date, 0 as CmNo, 0 as BookDate, 0 as LR_No, 0 as CM_Total, Receipt_CLNT.Amt_Rcvd as RcptAmt,Receipt_CLNT.ChqDDNo as RefNo, Receipt_CLNT.ChqDDdate as RefDate, Receipt_CLNT.Amt_Mode as Mode, Receipt_CLNT.RcptNo as RcptNo, 0 as MemoNo, 0 as MDate, Receipt_CLNT.G_Name
from Receipt_CLNT
INNER JOIN ClientLedger ON (ClientLedger.CName = Receipt_CLNT.G_Name)
ORDER BY Cdate;

Open in new window


now a new field ( GrMod ) added in CashMemo table.
What i want is if GrMod = PAID then FROM CM_Total - CM.Freight else CM_Total
CM.Freight is not in this query but it is exist in CM table.
how can i do this..?
if GrMod = PAID then FROM CM_Total - CM.Freight else CM_Total
LVL 1
crystal_TechAsked:
Who is Participating?
 
peter57rCommented:
At the end of line 1 add

, iif(grmod="Paid", CM_Total - CM.Freight ,CM_Total ) as CMTotal

At the end of line 4 add

, Null as CMTotal

I see that you are not showing any fields from the ClientLedger  table; I assume it is there to limit the records from the other tables?
0
 
mlmccCommented:
Looks good.  You could also pass both GrMod and CM.Freight into the report with NULLs from the other part and do the calculation there.

mlmcc
0
 
crystal_TechAuthor Commented:
@peter57r
Thanks its working.

@Michael
which way is best for speed,
calculation from report
or filter from Union Query..?
Please suggest me.
0
 
mlmccCommented:
generally it will be faster from the SQL.

Particularly in this case since only part of the data needs the calculation

Another considerationis you are passing less data to the report so the network traffic will be less.

In this case, you probably won't notice much difference unless you have 10,000 or 100,000 records.  Not sure you would even really notice the difference then.

mlmcc
0
 
crystal_TechAuthor Commented:
Thanks for Quick Reply..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.