Lookup value based on another table

[Spenello]

Oracle 10g and CR10
There are two tables, table1 with a field named ot_shift_cd and table2 with a field named shift_cd. Both are number fields.

The report currently displays "No Shift Code" if the ot_shift_cd in table1 is null. However, I'm out of ideas on how to return a string value (something like "Shift Code Invalid") in the report, when the ot_shift_cd in table1 doesn't match any code values in table2.

 

[lbass]

How are the two tables linked? From what table to what table, and with what kind of join? Can you show some sample data?

-LB

 

[Spenello]

Tables are linked by shift_cd. There are also other fields in both, but are not relevant. Unfortunately, there is no referential integrity.
table1 (Employee_Detail) table2(Shift)
shift_cd ot_shift_cd shift_cd
1 0 1
2 2
1 6 3
4 23 4
3 3 5
6 1 6

So, the report should look like this:
ot_shift_cd
Not Valid Shift
No Shift Code
6
Not Valid Shift
3
1

 

[lbass]

To determine whether the ot_shift_cd was valid or not, you would have to link table2 to table1 with a left join from [red]table1.ot_shift_cd[/red] to table2.shift_cd. Then you could use a formula like this:

if isnull({table1.ot_shift_cd}) then
"No Shift Code" else
if isnull({table2.shift_cd} then
"Invalid Shift Code" else
{table1.ot_shift_cd}

-LB

 

[Spenello]

The tables are linked in the stored procedure with a left outer join.

The script works great for nulls. But, the report also needs to check for invalid codes. (EX: ot_shift_cd 0 is not null but is invalid).

 

[lbass]

You have not indicated which tables are linked. If linked as I suggested, then the formula should work to determine whether codes are invalid or missing.

-LB

 

[Spenello]

This worked:
if isnull({table1.ot_shift_cd}) then
"No Shift Code" else
if {table1.ot_shift_cd} in[1,2,3,4,5,6] then
totext({table1.ot_shift_cd}), 0) else
"Invalid Shift Code"

I guess my real question should be how to make the script work dynamically? (i.e. instead of hard coded values 1-6, have the values based on table2.shift_cd.)

 

[lbass]

I already showed you. You didn't clarify your links, so if the stored procedure isn't linked correctly for this purpose, maybe you need to see about changing it.

-LB

 

[Spenello]

That seems to be the right track.

The tables were linked:
table1.SHIFT_CD = table2.SHIFT_CD (+)

The tables are now linked:
table1.OT_SHIFT_CD = table2.SHIFT_CD (+)

Thanks for sticking with it lbass.