How to get "Conditional Join" to work...

[MitraMIS]

Hello group;
I'm joining two tables, SALES and AR using "If" statement as below and is not working. What I am doing wrong?

SALES."ORDER_NUMBER"=(if(AR."TYPE"='1')then(AR."ORDER_NBR")else(substring(AR."ID",10,6)

TIA;
Mitra

 

[drlex]

When you say it's not working, do you get an error message? Can you see any join in the SQL generated?
At first glance it looks like you're trying to make a join between an integer ("ORDER_NUMBER") and a string ("substring(AR."ID",10,6)") - put in a convert (number to string) function.


soi la, soi carré

 

[MitraMIS]

1. No join gets generated. It puts a red circle between two tables instead of a black arrow that joins the tables.

2. Eventhough the field name is Order_number, it's a chracter field. We have order nbrs like "H15642".

Mitra

 

[griffindm]

Mitra,

Please let us know which product this is for.

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

 

[MitraMIS]

Dave;

This is for Impromptu.

Mitra

 

[griffindm]

I've had little success in defining joins in Impromptu that where not equi-join based, other than three-table equi-joins. This includes the LIKE or Substring functions, and I imagine it would not look promising for this type of conditional join.

That said, if your SQL skills are up to it, I'd create a view that contains the data you need from both tables and uses the conditional join. Then just add the view to your table.

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

 

[MitraMIS]

Thanks Dave. I will try that after educating myself about Views.

Mitra

 

[drlex]

Mitra
Dave's suggestion is the best way forward; one can create conditional joins in Impromptu but the join editor makes it extremely hard so to do. Your experience of the red circles between the tables indicates that there is no join recorded.
Should you not go with the view method, try adding an equi-join diagrammatically and then use the 'view as definition' button to amend the equivalence to a condition.
Happy Friday,
lex

soi la, soi carré

 

[GJParker]

Which version of Impromptu are you using ? I am using 7.3 MR1 and have had no trouble creating conditional joins

Here is one I tested which only replicates the same join but tests the theory, this is the join condition

ACT."ACT_CODE" = IF ( ACT2."ACT,DISCONT" = 'Y') then ( substring( ACT2."ACT_CODE),1,char_length(ACT2."ACT_CODE" ))) else (ACT2."ACT_CODE)

when I create a report using this join the resultant SQL is

select T1."ACT_CODE" as c1,
	   T1."ACT_CC" as c2,
	   T1."ACT_CLAS" as c3,
	   T2."ACT_CODE" as c4,
	   T2."ACT_CC" as c5,
	   T2."ACT_CLAS" as c6
from "root"."ACT" T1,
	"root"."ACT" T2
where (T1."ACT_CODE" = CASE WHEN (T2."ACT_DISCONT" = 'Y') THEN ((substring(T2."ACT_CODE" from 1 for (length(T2."ACT_CODE" || 'Z') - 1)))) ELSE (T2."ACT_CODE") END)

Gary Parker
MIS Data Analyst
Manchester, England

 

[griffindm]

Gary,

There may be a factor of RDBMS support. Can you tell us what your database (with version)?

Thanks,

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

 

[GJParker]

Dave

Now that's an interesting one, I don't know much about the db other than it's an old Typhoon database running on UNIX, using a Transoft ODBC driver.

I've also tried something similar on Access 2000 with the same results.

Gary




Gary Parker
MIS Data Analyst
Manchester, England

 

[drlex]

Gary - I'm with you. I can (albeit with some difficulty in getting the required operands) create conditional joins using SQL server between Wintel boxes and ODBC to Progress (Wintel to Unix)
lex


soi la, soi carré

 

[MitraMIS]

lex;

I have tried to trick it that way and create equi-join and amend my condition in definition button, but it doesn't get saved. When I go back to it, just shows the definition based on equi-join. It removes my condition.

Gary;

My Impromptu version is 7.1. I wonder if that is the reason that I cannot do it. I'm basically creating the same condition that you have.

Mitra

 

[GJParker]

I'm not suer about that I have reports built in V7.0 with some very complex joins.

I did notice the 1st time I tried this test the condition didn't save, so instead of clicking Ok straight away go to another table in the join paths box and then go bacjk to the one you've just created and see if it's still there.

I'm not usre what to suggest after that, what db and connection are you using ?


Gary Parker
MIS Data Analyst
Manchester, England

 

[MitraMIS]

YES!
That did it Gary.
It's working now.
Thanks to all of you that helped me.

Mitra