Table Join Problem

[MESReportGuru]

Hi, my name is Curt. I am trying to create a report that needs data based on two tables. Table A has a field called Tagnumber and it is a number field. Table B is in the same Oracle instance but created by a different vendor. In Table B the Tagnumber value is a string. CR9 won't let me link these two field together because they are not of the same type.

Here is the question. How do I work around this problem? I was wondering if it would be possible to create a view or stored procedure to make one of the fields appear as the same type as the other field. Other ideas?

Curt

 

[synapsevampire]

A View or SP is a fine idea, but you can also use real SQL in CR 9 to do the same thing.

If you're able to create Views/SP's, then I would advise doing so, it helps to promote reusability, simplifies maintenance, and provides an insulating layer in case the database changes.

I generally create a script at each contract which creates Views from every table (not select *, select <field1>, <field2>, etc...) and diallow all developers, SQL, application and reporting from using tables directly.

-k

 

[MESReportGuru]

Ok. I think we are on the right track so let me change the question a bit.

Given that I have a number field that I want CR9 to interpret as a string field (or vice versa) can anyone provide me a simple example where an Oracle view is created that changes the column type from its original type?

Example:
Table A (original) - Column 1 = number type
Table A View - Column 1 = string

Curt

 

[synapsevampire]

There isn't a number type nor a string type in Oracle.

Try downloading PL/SQL Developer or Toad to aid in creating Views/SP's

You'd want to understand the indexing of both fields (and kick your DBA out the door for allowing 2 different data types) and alter the one of interest using the Oracle CAST or CONVERT or to_text or to_number function.

Here's an example:

where
table1.field = to_number(table2.field)

-k

 

[MESReportGuru]

Actually this is not a DBA bad guy issue. I am dealing with two separate databases made by two separate application vendors. My report is trying to link data from Vendor A to B.

I am not an Oracle DBA type and only know enough SQL to be dangerous, so I need help so I can be more dangerous. Let me reword the question again.

Is it possible to create an Oracle view where the column type is different in the view than it is in its parent table? If so, any way I can get a simple example of that?

Maybe I am missing something but I could not understand how the previous example did this. To further the explanation I am going to show the SQL for the view I am creating and how I want it to be different.

CREATE VIEW QDC_KEY (SG_ID,LABEL_VALUE)
AS SELECT SG_ID, LBL_VALUE
from QDBA.L_DATA WHERE LBL_ID = 1

Results in the creation of a view that looks like this:

Name Null? Type
SG_ID NOT NULL NUMBER(10)
LABEL_VALUE NOT NULL VARCHAR2(100)

I want the LABEL_VALUE column to be a number(10) instead.

 

[GJParker]

CREATE VIEW QDC_KEY (SG_ID,LABEL_VALUE)
AS SELECT SG_ID, to_number(LBL_VALUE) as LABEL_VALUE
from QDBA.L_DATA WHERE LBL_ID = 1

HTH



Gary Parker
Systems Support Analyst
Manchester, England

 

[MESReportGuru]

That did it. Thanks a ton.

Curt