Supress Repeating Values made by an if-statement and layout Problem

Paul Horan[Sybase]

2009-01-01 17:19:16 UTC

First, a CASE() statement would be much more readable here:

CASE (tage_gerundet
WHEN >0 THEN 'Nicht fällig'
WHEN 0 THEN '1 bis 30 Tage überfällig'
WHEN -1 THEN '31 bis 60 Tage überfällig'
WHEN -2 THEN '61 bis 90 Tage überfällig'
WHEN -3 THEN '91 bis 120 Tage überfällig'
ELSE 'Mehr als 120 Tage überfällig')

Second, I don't read German, but I believe this is to be an aged receivables
report, corrrect?

You can solve the "rows to columns" problem right in your SQL with
conditional function expressions. I don't know your column names, but I'll
assume "tage_gerundet" is the age of the balance. I'll use "tage_amount" as
the balance amount.

SELECT
sum( tage_amount *
(case when tage_gerundet > 0 then 1 else 0 end)) as nicht_fallig,
sum( tage_amount *
case when tage_gerundet = 0 then 1 else 0 end)) as uberfallig_30,
sum( tage_amount *
case when tage_gerundet = -1 then 1 else 0 end)) as uberfallig_60,
sum( tage_amount *
case when tage_gerundet = -2 then 1 else 0 end)) as uberfallig_90,
sum( tage_amount *
case when tage_gerundet = -3 then 1 else 0 end)) as uberfallig_120,
sum( tage_amount *
case when tage_gerundet between -4 and -99 then 1 else 0 end)) as
uberfallig_als_120
from theBalanceTable
where (theBalanceTable.customer_account_number = :some_argument) ;

This will show data for one customer account. To show all customer
accounts, you'd add customer_account_number to the SELECT list, GROUP BY
clause, and ORDER BY clause.
--
Paul Horan[Sybase]
paulhoran.pbdjmagazine.com

"Dominic Bialas" <***@communicate-online.de> wrote in message news:fccd5a88-a1b7-4546-a245-***@w39g2000prb.googlegroups.com...
Hello out there,

IŽm still just a little beginner in SQL and Infomaker.
IŽve got two question that donŽt let me sleep anymore. IŽm hope you
can help me...

IŽve got a computed field with an if clause. It looks like that:

"if( tage_gerundet >0,'Nicht fällig', if( tage_gerundet =0,'1 bis 30
Tage überfällig', if( tage_gerundet =-1,'31 bis 60 Tage
überfällig',if( tage_gerundet =-2, '61 bis 90 Tage überfällig',if
( tage_gerundet =-3, '91 bis 120 Tage überfällig', if( tage_gerundet
between -4 and -99, 'Mehr als 120 Tage überfällig', '' ) ) ) )))"

The problem is, that for every "tage_gerundet" that is >0 a seperate
Text "Nicht fällig" is shown and i canŽt suppress that only one is
displayed. I think this is propably an easy problem, but I really do
not know how i could manage this.

My second problem is about the layout.

I would like to show my rows as columns. Sounds funny, but it must
be :-)
Example:

The actual result is:

x Tage überfällig: 5.000
y Tage überfällig: 6.000

but what i want is the following:

x Tage überfällig y Tage überfällig
5.000,00 6.000,00

I hope you can help me...

Greets Dominic Bialas