Creating a Positive or Negative Field

[PETEHO]

I have two fields in a Universe.I need to show the value of these value field as a Debit or credit.

These are running against oracle databases

My field indicator is either DR or CR.

I would imagine the select statment in the universe to be

SUM(amount) * -1 where DRCR indicator like 'CR' else SUMAmount).

Can anyone advise please

 

[blom0344]

Do NOT use where clauses in object definitions !!!
Use CASE expressions:

sum(
case when
DRCR indicator = 'CR'
then amount*-1
else amount
end)

Ties Blom

 

[PETEHO]

Thanks fopr your reply. I had astarted along that path, however I have something slightly amiss.I am getting and Invalid relationship operator for the following.

Sum(case when Mi Suspense Analysis V\Debit Credit Ind = 'CR' then Mi Suspense Analysis V\Suspense Amt *-1 else Mi Suspense Analysis V\Suspense Amt)

Any idesa

Cheers

Pete

 

[blom0344]

You need to define a proper case (which ALWAYS ends with 'end') The syntax that you mention is not sound..

Do a search on how to write case expressions..

Ties Blom

 

[skrandel]

It also looks like you are using object names. Don't do that. You should use database table and column names with your case.

If you are going to use object names, then you need to use @select.

Steve Krandel
Intuit