Hi All,

I have the following sql statement that includes estimates, orders and
invoices. I want to include credits as well. The TranType field is used to
indicate the type of transaction. The problem is, the tran totals are all
positive. So to use the values in the query, I need to multiply the
TranTotal field by -1 to get the true value if the TranType field = 'CRD'.

Any idea how to do this ?

Cheers,
Paul

 SELECT Tran.TranNo
        Tran.TranType,
        Tran.DebCred,
        Tran.ClientId,
        Tran.TranDate,
        Tran.Staff,
        Tran.DeliveryCharge Handling,
        Tran.Branch,
        Tran.FinalisedDate,
        Tran.TranType,
        Tran.TaxTotal,
        Tran.TranTotal,
        Tran.LineDiscount,
        Tran.TranTotal / ((100 - Tran.LineDiscount) / 100) Tran.TranGross,
        (Tran.TranTotal / ((100 - Tran.LineDiscount) / 100)) -
Tran.TranTotal Tran.TranDiscount,
        (IF Tran.TranType = 'FDC', Tran.TranTotal*-1, Tran.Total)
Tran.TTotal,
        Debtors.Company,
        Debtors.Phone,
        Debtors.DebtorType,
        Debtors.DebtorCode,
        Debtors.ABN Abn
   FROM "Tran.DAT" Tran
        INNER JOIN "Debtors.DAT" Debtors
                ON Tran.DebCred = Debtors.DebtorId
   WHERE Tran.TranType IN ('EST','ORD','INV','CRD')
   ORDER BY Tran.TranDate ASC

Paul


Its easy - the format for the IF statement is IF(condition test, true value,false value)

So IF(Tran.TranType = 'CRD',Tran.TranTotal*-1,Tran.TranTotal)

You need to use this for each occurrence since you can't put the result into a field and then use that.

Roy Lambert [Team Elevate]

> So IF(Tran.TranType = 'CRD',Tran.TranTotal*-1,Tran.TranTotal)
>
Hi Roy,

thanks heaps for the quick reply. This worked perfect.

Cheers,
Paul

Is there a way to put in a value instead of a column name?  I've tried this and keep getting asked for a column name, but not column has the value in it.

"Paul Coshott" wrote:

> So IF(Tran.TranType = 'CRD',Tran.TranTotal*-1,Tran.TranTotal)
>
Hi Roy,

thanks heaps for the quick reply. This worked perfect.

Cheers,
Paul

Ray,

<<Is there a way to put in a value instead of a column name?>>

Yes, it should be.
Can you please post your code?

--
Fernando Dias
[Team Elevate]

Ray


What Fernando said

And for your next question please start a new thread don't attach to one four years old. It took my aging brain a while to figure out what was going on.

Roy Lambert [Team Elevate]

Roy Lambert wrote:

Ray


What Fernando said

And for your next question please start a new thread don't attach to one four years old. It took my aging brain a while to figure out what was going on.

Roy Lambert [Team Elevate]

Whoops Thanks to both of you.  Fernando helped me on another thread....
Roy Lambert [Team Elevate]

Ray


I apologise if I seem a bit harsh but hiccoughs and leaving my sig on so it looks like its from me is not on

Roy Lambert

Roy,

<< I apologise if I seem a bit harsh but hiccoughs and leaving my sig on so
it looks like its from me is not on >>

Give it up Ray, I mean Roy....

Tim Young
Elevate Software
www.elevatesoft.com