Alternate Conditional Record Selection

[ajxmas]

Hi all,
I am fairly new at this. I'm using CR XI using an Oracle DB. I have an account table I'm reporting on with data such as:

ACCT ID NAME ADDRESS ZIP
0000220 COLBERT 234 XYZ LANE 20705
0009990 STATE 0000005 ZZZZZ 20943
0000005 UNION 1500 RED ST 21190
0009991 CRESTAR 0000220 ONTTR 28901

I select these records based on a particular selection criteria which is working properly. The problem is the records that are selected that have the first 7 characters as numeric in the ADDRESS field. For those records I need to use those 7 characters as the ACCT ID to get the proper address, zip, etc. So my report should end up looking like this:

ACCT ID NAME ADDRESS ZIP
0000220 COLBERT 234 XYZ LANE 20705
0009990 STATE 1500 RED ST 21190
0000005 UNION 1500 RED ST 21190
0009991 CRESTAR 234 XYZ LANE 20705

I tried creating a view on my db that gives me the correct address for all the records with 7 character numeric address. Then I tried doing a whilereading or whileprinting formula in the Display formula of the address field in the report such as:

WhileReading;
If NumericText (Left({VENDOR_ACCOUNT.ACCT_NAME}, 7)) = true
then
if NumericText (Left({VENDOR_ACCOUNT.ACCT_NAME}, 7)) ={MKTGALTVU_ACCT_ID} then
{MKTGALTVU_ADDRESS}
else {VENDOR_ACCOUNT.ACCT_NAME};

Neither option worked.

Any ideas on how to get the results I'm looking for?

 

[lbass]

Ideally, you would use a command as your datasource where you could add the table a second time and link the address field to the acct Id field, like this:

select "acct"."acctID","acct_1"."acctID" "ID2","acct"."address","acct"."address" "Addr2"
from "acct" left outer join "acct_1" on
left("acct"."address",7) = "acct_1"."address"

What you really want to do is get the correct address field, not the account ID, so write a formula like this:

if isnull({command.Addr2}) then
{command.address} else
{command.Addr2}

...to use as the address field.

-LB