Retrieve data from 2 different tables depending on criteria on another

[n1oio]

To all,

We are running CR 7 and Oracle 8.0.5.
There are 3 tables:
1. opc_source_templ
2. opc_monitor_cond
3. opc_logfile_source

Table 1 is linked to table 2 by a field called template_id.
Table 1 is also linked to table 3 by the same field(template_id). There is a field called source_type_id in table 1. Depending on that type, data is stored in different tables. I want to create a formula that will pull template_name from table 1, and depending on the source_type, either threshold from table 2 or logfile from table 3. I tried the following formula, and either one of the lines work by themselves, but not together:

If {New Field Heading.SOURCE_TYPE_ID} = 4.00 then {New Field Heading.LOGFILE}
else if {New Field Heading.SOURCE_TYPE_ID} = 8.00 then {New Field Heading.THRESHOLD}

Any help would be appreciated.

Thanks,

 

[kenhamady]

Is there a one to many relationship between 1 and 2/3?
Can a record find matches in both tables? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[n1oio]

Yes,

There is a one to many from opc_source_templ to opc_monitor_cond and a one to many from opc_source_templ to opc_logfile_source.

Thanks,

 

[kenhamady]

And will one record in 1 find matches in both 2 and 3? Always? sometimes? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[n1oio]

If source_type in opc_source_templ = 8.00 then there should be an entry in opc_monitor_source. If opc_source_templ = 4.00 then there should be an entry in opc_logfile_source. This is an HP OpenView database, so I am not sure that there will ALWAYS be an entry for each.

Thanks,

 

[kenhamady]

Is there ever a match in both other tables? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[n1oio]

Ken,

I'm not sure what you mean. Do you mean is what is in the logfile field the same as what is in the threshold field? If that is what you mean, then no, the data will never be the same.

Thanks,

 

[kenhamady]

No, I mean will the record in table one have a matching record in both table two and table three. The same template ID will be in all three tables. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[n1oio]

Ken,

I'm sorry this is taking so long, I really appreciate your help.

No, the template_id does not always have an enrty in the other 2 tables.

Jim

 

[MalcolmW]

This is really a join issue, and is best dealt with in the Crystal Reports Data Access forum.
How about starting a new thread there, and include your FROM clause from your SQL statement? And in preparation for the solution, does your version of Oracle support CASE statements in SQL?
Malcolm

 

[kenhamady]

Jim,

You still missed my question.
Will the template ID ever exist in all 3 tables at the same time? Or is it always matching only one of the other tables? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[n1oio]

Ken,

Sorry, the template_id will only match one of the other tables.

Thanks,
Jim

 

[kenhamady]

So, you must be using outer joins to link to both of these tables. Otherwise you wouldn't get anything from the database, since nothing is in all three. Please confirm.

I am also confused by your first formula. It appears that the table name is the same for all three fields? Anyway, try this formula:

If IsNull({field1})
then {field2}
else {field1} Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[IdoMillet]

Do outer joins from the main table to the two lookup tables.

Then create a formula field like:

If {opc_source_templ.SOURCE_TYPE_ID} = 4.00
then
{opc_logfile_source.LOGFILE}
else
{opc_monitor_cond.THRESHOLD}


Note that the data types of LogFile and Threshhold must be the same. If not, do the necessary type conversion.

hth,
- Ido

 

[n1oio]

To all,

Thanks for all of your help. It was a join problem. It works great now.

Thanks,
Jim