Impromptu Outer Join

[kkcarlton]

Hi All,

A while back when I was totally new to Impromptu I changed the SQL on a report. I had no idea I would not be able to go back to the original format (unless you can and I am not aware of it). I did this in order to join the same table twice and at the time I did not knwo how to modify the catalog and we were in a crunch.

anyway, I need to add an outer join to my statement. Currently the statement says
T1.TIN_NUM = T3.SVC_TIN_NUM. I changed it to
T1.TIN_NUM = T3.SVC_TIN_NUM(+)

In doing this I get an error message but all it says is "error in expression". So I added an outer join to the catalog, but my records are still not showing up.

What am I doing wrong?

Thanks,
Kristina

 

[kkcarlton]

I forgot, we are using version 7.1.339.0

 

[griffindm]

Just adding an outer join to the SQL or to the catalog is often not enough to ensure that unjoined data will appear in the report. You also need to review the filter clause in the report to make sure it also allows for null values in the missing table rows from the outerjoined table. Additionally, if there are other tables in the report joined to the outerjoined table, then you may have a cascading problem involving the joins from that table to the others as well. Sounds a little confusing, I'm sure. If it's not clear let me know and I'll try a graphical representation of the issues instead.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[kkcarlton]

Hi Dave,

I guess I am somewhat confused. There are other tables joined to the outer joined table. What I don't understand is how to review the filter clause in the report. Because I modified the SQL, I cannot get back to the original tabs I used to have. Or is there a way I can change the report back so I get all my tabs back? Does this make sense?

Thanks,
Kristina

 

[griffindm]

Unfortunately, I know of no way (short of having a backup copy either on disk or tape) to restore the report functionality once you have edited the SQL.

Let's look at two cases using OJ data. In case 1 let's assume you have a fact table (one with measures, i.e. quantifiable columns you can do sums upon), and a lookup table, which may or may not contain a reference to the fact table.

Table 1 GL Cost Detail (Fact Table)
Column   Type   Sample
------   ----   ------
YR       Num     2004
PD       Num       1
ACCT     Char   100-01
VEND     Char     ATT
EMPL     Char   12345
AMT      Num    100.00

And a Lookup Table

Table 2 EMPL (Lookup Table)
Column   Type   Sample
------   ----   ------
EMPL     Char   12345
FNAME    Char    JOHN
LNAME    Char     DOE
HDATE    Date   01/01/2004
TDATE    Date   12/15/2004
ACTIVE   Char     Y

The fact table may contain a value for VEND or EMPL, but not both on a single row. In order to get the employee name in a report with the GL data, and still include ALL of the GL data, you must use an outer join between the tables on the columns EMPL.

Now say you want to limit the report to only active employees, but still want the non-employee data. A simle filter of

... and Active = "Y" ...

would force the report to remove the non-employee data by inference. By including data from an outer-joined table in the filter, you can only keep the outer-join effective by modifying the filter to allow for the missing data as well, as follows:

... and (Active = "Y" OR Active is missing)...

This gets the report to respect the outer join even though a filter element on outer joined data is included.

In a second case, let's add a third table

[code]Table 2 EMPL_ADDR (Lookup Table)
Column   Type   Sample
------   ----   ------
EMPL     Char   12345
ADDR1    Char   123 Main St
ADDR2    Char   Apt 12
CITY     Char   New York
STATE    Char   NY
ZIP      Char   22222

If this table is only inner-joined to EMPL and you further filter your report to just see NY employees, you must also modify the join between EMPL and EMPL_ADDR to be outer-joined on EMPL (checkbox on this table) and add the "missing" check for STATE as well in order to keep non-employee data in the report.

I hope this helps. I may whip this into a FAQ at some point as well.

Regards,

Dave Griffin






The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[kkcarlton]

Well, I think what I am doing is correct, it's just that it doesn't like my (+) in the statement. Here is my scenario.

PRV - Provider Info
NAME
TIN_NUM
SUB_TIN_SEQ_NUM

UMD - Hospital/Patient Info
SVC_TIN_NUM
SVC_SUB_TIN_SEQ_NUM
PCP_TIN_NUM
PCP_SUB_TIN_SEQ_NUM

I use the PRV table twice. Once to join it to SVC tin sub tin and then a second time, with a different alias, to join by PCP tin and sub tin. However, the UMD table may or may not have a PCP tin. So, I want the UMD table to return data even if the PCP tin is null. But like I said before, when I add my outer join symbol I get an error.

Thank you for your help.

Kristina

 

[griffindm]

Ahhh. Things are getting clearer. What are your client/server settings? What is your RDBMS? Posting your full SQL statement would help also.

Many databases can only handle a single outer-join relationship for a central fact table (like your Hospital/Patient info). If you are set to "Database Only" this would cause an error. If so, try using "Limited Local Processing" or "Flexible Processing". Other than that, you are correct in that your report should run, albeit a little slower than if the database was doing all the work.

Dave G.


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[kkcarlton]

Hi Dave,

Thank you for the reply. Yoy have been very helpful and I appreciate it.

We have OpenVMS 7.3-2 and I am using an CONNX ODBC driver to connect to it.

One of the first things I tried was adding the "OR field is null" to my statement, but the report runs "forever" and my entire machine comes to an almost screeching halt. Here is my statement without the outer join and without the filter:

select T6.DIV_NUM,
T7.NAME,
T1.PROCESSOR_CODE c1 ,
T2.LAST_NAME c2 ,
T2.FIRST_NAME c3 ,
T1.UM_NUM c4 ,
T1.UM_SEQ_NUM c5 ,
T1.SYS_YYYYMMDD c6 ,
T3.UM_CASE_NUM c7 ,
T1.STATUS c8 ,
T1.POS c9 ,
T1.INITIAL_YYYYMMDD c10 ,
T1.FINAL_YYYYMMDD c11 ,
T1.TIN_NUM_02 c12 ,
T1.SUB_TIN_SEQ_NUM_02 c13 ,
T4.NAME c14 ,
T1.TIN_NUM_03 c15 ,
T1.SUB_TIN_SEQ_NUM_03 c16,
T5.NAME,
T1.TIN_NUM_02,
T1.SUB_TIN_SEQ_NUM_02,
T1.ICD_CODE
from UMDFIL_FILE T1,
UMHFIL_FILE T3,
CLMFIL_FILE T2,
PRVFIL_FILE T4,
PRVFIL_FILE T5,
INDFIL_FILE T6,
CASFIL_FILE T7
where T6.CASE_NUM = T2.CASE_NUM
AND T6.CERT_NUM = T2.CERT_NUM
AND T6.CASE_NUM = T7.CASE_NUM
AND T6.DIV_NUM = T7.DIV_NUM
AND T1.UM_NUM = T3.UM_NUM
and T3.CASE_NUM = T2.CASE_NUM
and T3.CERT_NUM = T2.CERT_NUM
and T3.CLMT_NUM = T2.CLMT_NUM
and T4.TIN_NUM = T1.TIN_NUM_03
and T4.SUB_TIN_SEQ_NUM = T1.SUB_TIN_SEQ_NUM_03
AND T5.TIN_NUM = T1.TIN_NUM_02
and T5.SUB_TIN_SEQ_NUM = T1.SUB_TIN_SEQ_NUM_02
and T1.POS in ('21', '31', '33', '35', '51', '52', '55', '56', '61')
and T3.UM_CASE_NUM NOT like 'CASE MGMT'
and t3.case_num between '0060001' and '0060002'
AND ((T1.INITIAL_YYYYMMDD >= ?Beginning Date? AND T1.INITIAL_YYYYMMDD <= ?Ending Date?) OR (T1.FINAL_YYYYMMDD >= ?Beginning Date? AND T1.FINAL_YYYYMMDD <= ?Ending Date?))
and T3.UM_CASE_NUM NOT like 'HINES CASE MGMT'
and T3.UM_CASE_NUM NOT like 'HINES CASE MGMT.'
order by 1 asc , 9 asc , 10 asc


Thanks,
Kristina

 

[griffindm]

Kristina,

I'm a little confused by the SQL. In your original post, you stated you had to add the outer join to the original join of:

T1.TIN_NUM = T3.SVC_TIN_NUM(+)

In the where clause of the SQL you posted above, I don't see these columns joined at all (with or without the Alias prefixes). Can you straighten me out?

Dave G.


The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[kkcarlton]

Hi Dave,

Sorry for the confusion. I posted the SQL statement without the outer join and without the additional filter because neither one were working for me. I want the outer join on:

AND T5.TIN_NUM = T1.TIN_NUM_02
AND T5.SUB_TIN_SEQ_NUM = T1.SUB_TIN_SEQ_NUM_02

T1.TIN_NUM_02 as well as T1.SUB_TIN_SEQ_NUM_02 could be blank in which case I don't get a record back.

I hope this makes more sense.

Kristina

 

[griffindm]

Kristina,

The SQL is making sense now. There are two sets of TIN_NUM and SUB_TIN_SEQ_NUM's in the UMHFIL table, and you use each set to connect to the aliases of PRV noted as T4 and T5 in the SQL. No conceptual reason why this should not give you what you want, as there is no filter element using T5 columns measured against constants (which is where you might have had to use the 'OR NULL').

That said, it comes down to what Impromptu is doing with the data, and parsing to the RDBMS. When you add the outer join to the catalog (i.e. checkbox checked for the UMHFIL table in the T5 PRVFIL join), do you see the OJ in the resulting SQL? You can see this without running the report (if it takes too long) by filling in prompts and then cancelling the report as it runs, then look at Report | Data | Profile | SQL. It should indicate an outer join symbol or terminology for the T1-T5 join.

If you have access to a SQL interpreter outside of Impromptu, you can often understand where Impromptu may be altering or affecting the performance or results by copying the SQL there and comparing the run times and result set between that interpreter and Impromptu using the SQL that Impromptu comes up with.

Sorry I can't offer you more, but it sounds like you are doing everything I would expect to have it work. The three-table example I provided previously does not seem to apply as you have a star-schema relationship on both T4 and T5 to the T1 fact table.Remaining issues are:

Does OpenVMS and/or the CONNX ODBC driver(neither of which I am familiar with) support outer-joins? Have you used other simple two table reports using outer joins with the expected results in Impromptu (such as just between UMH and PRV)?

Have you tried to remove T2/T6/T7 data from the report (or recreate it without it) to see if that snowflake schema is affecting your results?

Let me know if any of these help you?

Dave G.




The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[kkcarlton]

Hi Dave,

I actually just found a solution and part of the problem. There are three Provider tin nums in this file (PCP, SVC, and Primary) but in the catalog the names are TIN_NUM, TIN_NUM_02, and TIN_NUM_03. TIN_NUM_02 in my report should have actually been TIN_NUM. Once I changed it I got data back and then it was just a matter of moving columns around.

Thank you so much for your help on this problem and I am sorry I did not see the incorrect field sooner. I did not intend to waste any of your time.

Kristina

 

[the6campbells]

Impromptu allows you to model in the catalog two or more references to the same table using the ALIAS facility. Should you go to the query dialog and the edit button is not greyed and you start to edit the statement you have assumed control of the query definition. That query definition is the native SQL of the underlying RDBMS engine. Hence, Impromptu will send it directly to the RDBMS without analyzing the statement.