Solved

union query conditional

Posted on 2012-03-25
5
641 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:crystal_Tech
  • 2
  • 2
5 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 250 total points
ID: 37762668
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 37762785
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
 
LVL 1

Author Comment

by:crystal_Tech
ID: 37762854
@peter57r
Thanks its working.

@Michael
which way is best for speed,
calculation from report
or filter from Union Query..?
Please suggest me.
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 37762887
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
 
LVL 1

Author Closing Comment

by:crystal_Tech
ID: 37762919
Thanks for Quick Reply..
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now