Re: Contents of an Object on a Report

Moore wrote ...

>The problem is that I have legacy data and new data. For the report the
>data needs to be combined. Therefore, some accounts will have a "legacy"
>account number and some will not.

>For example:
>
>The query: Select cust_num ||'-'||service_loc "ACCT", legacy_acct_num from
>legacy, customer, service
>etc...
>
>Some of the accounts in legacy will not have a customer number and service
>location. Therefore, if the query returns a '-' for "ACCT' then we need to
>place the legacy account number in the field on the report.

I understand:

you want to place legacy_acct_num in ACCT instead of the term cust_num ||'-'||service_loc when either cust_num and/or service_loc is empty - does empty mean NULL or spaces ?
does empty cust_num imply empty service_loc ? do these columns originate from the joined tables customer / service - does the join succeed (outer join ...) ?
when the replacement takes place, you still provide legacy_acct_num as the second column ?

if all these questions answer with yes, why not substitute col1 of the query with one of these:

when empty means ' ' and the replacement is to be done only if both are empty -
decode (cust_num, ' ', decode(service_loc, ' ', legacy_acct_num, 'whatever_you_need_if_cust_num_is empty_and_service_loc_is_not'), cust_num ||'-'||service_loc ) ACCT

if cust_num empty -> service_loc empty this simplifies to - decode (cust_num, ' ', legacy_acct_num, cust_num ||'-'||service_loc ) ACCT

if empty means null and both have to be empty you might use - decode (nvl (cust_num, ' '), ' ', decode (nvl (service_loc, ' '), ' ', legacy_acct_num, 'whatever_you_need_if_cust_num_is empty_and_service_loc_is_not'), cust_num ||'-'||service_loc ) ACCT

and analougous ...
this forces the query to get the right results ...

in Reports exist formatting triggers - you might as well try to write a formatting trigger for the field in question and return the correct string in respect to what you want to output - thats simple PL/SQL ...

hth Received on Fri Jan 28 2000 - 03:41:31 CST