Solved

union query conditional

Posted on 2012-03-25
5
643 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Android development question 2 37
object variable or with block not set 6 30
Calculate Time acces 2010 12 30
Visible ppty on rpt 7 7
If you haven’t already, I encourage you to read the first article (http://www.experts-exchange.com/articles/18680/An-Introduction-to-R-Programming-and-R-Studio.html) in my series to gain a basic foundation of R and R Studio.  You will also find the …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…

810 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