decode from 2 tables

[138006]

Hi,
my requirement is :
Decode(T1.C1,0,T1.C2,T2.C2)
I can do it using ApplySimple.Similar construct with
Case can also be thought of.

But problem in MSTR is to select columns from 2
different tables T1 and T2 depending on the condition.
The decode statemnt is validated but whenever I am
trying to save the attribute it sets one table say T1
as Lookup and when I drag this object only in a
report, the decode statement becomes:
Decode(T1.C1,0,T1.C2,T1.C2). How to get Table T2 for
the else clause??

Any help will be appreciated.
Thanks and regards

 

[nlim]

so...here comes the "how do you create the sql that I want" question...best way is to use a SQL query tool...

MSTR is a strange tool, it wants to generate the best SQL for a specific purpose. So you want to work backwards from a SQL statement you have, it is almost always very difficult. It is much better to layout the requirements, and then figure out if the MSTR SQL is generating the right results, not the SQL you want.

138006, while this is not a consulting forum, it would actually be helpful if you posted the report layout, and your basic schema here with the question.

 

[entaroadun]

It sounds like you are trying to write an attribute form expression using columns from two different logical tables. This is impossible; in MSTR, each attribute form expression can only be bound to one logical table. This one logical table then determines the level that the attribute form is set to in the generated SQL.

You need to pick one of the tables to bind your attribute form expression to. For example:

You are using 3 columns on 2 tables:
T1.C1
T1.C2
T2.C2

You decide to bind the attribute form to logical table T1:

ApplySimple("Pass-thru", C1, C2)

You check off T1 for logical tables that the attribute form expression is bound to.

In the pass-thru SQL, you need to use a correlated subquery to bring in T2.C2:

ApplySimple("DECODE(#0,0,#1,(select C2 from T2 where T2.joinkey = T1.joinkey))", C1, C2)

Notice that MSTR isn't aware that you are using T2.C2 at all. You are embedding a reference to that column in the pass-thru expression.

Whenever you use this attribute, it will treat it as if it were bound only to table T1. T2 will not appear in the FROM clause; only in the SELECT clause in this correlated subquery.

Hope this makes sense.

 

[138006]

Hi entaroadun
Can u please elaborate a bit. Do u mean that there will be 2 ApplySimple functions (1 nested in another) ?

Also, in general what I have seen, if u write a Select statement in the Applysimple of an attribute form then it becomes something like

Select .... (select ...)

The outer Select is for the form itself and the inner one for the hardcoded Applysimple. As obvious this won't give any result.

Please let me know if my concern is correct and please elaborate ur solution.

 

[entaroadun]

My first ApplySimple was to illustrate the high-level concept. The second ApplySimple is what you should actually use. There is only one ApplySimple needed.

Select within a Select will most certainly give a result, if written properly. Please do some online research about correlated subqueries, if you have any questions about that SQL concept.

 

[138006]

Thanks entaroadun

I will try this and let u know. But just to elaborate, decode was 1 example of trying an attribute from 2 different tables.

Another attribute is T1.C1||T2.C2.

How can I do that as well?

Thanks

 

[138006]

Hi entaroadun,
As per ut suggestion , I tried the following in the Tutorial project.

ApplySimple("#0 & (SELECT a.DIST_CTR_NAME FROM LU_DIST_CTR a, LU_COUNTRY b, LU_CALL_CTR c WHERE ( a.COUNTRY_ID=b.COUNTRY_ID ) AND (b.COUNTRY_ID=c.COUNTRY_ID ))", [CENTER_NAME])

When I drag this object in a report the sql is:

select distinct a11.[CENTER_NAME] & (SELECT a.DIST_CTR_NAME FROM LU_DIST_CTR a, LU_COUNTRY b, LU_CALL_CTR c WHERE ( a.COUNTRY_ID=b.COUNTRY_ID ) AND (b.COUNTRY_ID=c.COUNTRY_ID )) AS CustCol_7
from [LU_CALL_CTR] a11

BUT,I got the following error:
"At most one record can be returned by this subquery."

From the SQL it seems logical to get the error since it can't treat this SQL as a subquery.

Any help?
Thnaks

 

[entaroadun]

What RDBMS are you using?

This SQL doesn't work because the subquery isn't guaranteed to return just one value, which is required by the concatenation operator.

You need to rewrite the ApplySimple SQL in a way that the RDBMS knows that you are returning only one value. If it were in Oracle, I would just put a "rownum = 1" statement at the end of that WHERE clause.

 

[138006]

Hi
I tried it out in MSTR Tutorial project (database Access).
I understand ur point.

My requirement was to create an attribute that will concatenate [CENTER_NAME] with [DIST_CTR_NAME ] --from 2 different tables.

By your method (rownum =1) I will concatenate all [CENTER_NAME]with the same value of [DIST_CTR_NAME]. But that is not I want. I want to concatenate each [CENTER_NAME]with the corresponding [DIST_CTR_NAME]and not just one value of the [DIST_CTR_NAME]field.

Hope it explains.

Thanks
Amitava

 

[entaroadun]

I haven't looked at the Tutorial project in ages, but I see what you're getting at.

Let's try this again.

Outside of MSTR, how would you normally join LU_DIST_CTR with LU_CALL_CTR to get the corresponding DIST_CTR_NAME for every CENTER_NAME?

Then we'll design the ApplySimple statement to address that.

 

[138006]

hi
This is the Access query SQL

SELECT LU_CALL_CTR.CENTER_NAME & LU_DIST_CTR.DIST_CTR_NAME AS Expr1
FROM LU_CALL_CTR INNER JOIN (LU_COUNTRY INNER JOIN LU_DIST_CTR ON LU_COUNTRY.COUNTRY_ID = LU_DIST_CTR.COUNTRY_ID) ON LU_CALL_CTR.COUNTRY_ID = LU_COUNTRY.COUNTRY_ID;

I get the desired result also.

As u see, the concatenation is within the select clause and hence correct. What we were trying is a nested Select in the SELECT clause of the SQL.

thanks

 

[entaroadun]

In this instance, a nested subquery won't work because there is a many-to-many relationship between Call Center and District.

For many-to-many relationships, just create a view that pre-joins the three tables and model that in instead.


Does you original question with the DECODE involve columns that are many-to-many? If so, then you'll need to create a view so that MSTR thinks that the fields come from one "table". If not, then we can do something with a nested subquery in ApplySimple that doesn't require creating any database objects.