Solved

union query conditional

Posted on 2012-03-25
5
647 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

734 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