Select statement in Join condition

[MSTRJD]

Hi all,

I have a requirement in order to satisfy which i need a query of the type given below:

select a.name,b.address
from table1 b
left outer join (select name from table2 where id=(Prompt)) as a
on b.id=a.id

do any one know how this can be accomplished in MSTR

Thanks.

 

[entaroadun]

How often will you be using the subquery?

 

[MSTRJD]

Hi entaroadun,

I am not sure if i understood ur question.The reason i need this type of query is I will be displaying on my report 5 addresses all the time at a given time(i.e.prompt).

the users want to see in a particular daterange how many people(name) stayed in the the addresses.If there is no one staying for a given address for a particular date,even then the address should be displayed and names might be displayed as null.

I hope u understand what i am trying to say.I found that the above query satisfy the this condition.Plz let me know if there is any other way to accomplish this.

Thanks

 

[entaroadun]

Oops, sorry. Didn't see the Prompt the first time around.

Why are you filtering on ID in the subquery? As it stands currently, you will get a list of names and addresses with names shown only for the IDs chosen. The prompt won't filter the addresses returned.

Is this what you want? Is this some sort of customer privacy requirement?

If so, will you be using a value prompt or an attribute element prompt?

 

[dmcmunn]

Example...

Assuming the following minimal data structures...

addresstable b nametable a
b.dateid a.dateid
b.address a.name


b.dateid b.address a.dateid a.name

1 Address1 3 name3
2 Address2 2 name2
3 Address3
4 Address4
5 Address5


Assuming this is your desired result...

a.name b.address

null Address1
name2 Address2
name3 Address3
null Address4
null Address5

Since there's no ID field in your example "a" on which to perform the LOJ, you will need MicroStrategy to generate SQL similar to the following...

select a.name, b.address
from addresstable b
left outer join
(select id, name
from nametable
where id=DateID.ValuePrompt) as a
on b.id = a.id

Which should equate to...

select a.name, b.address
from addresstable b
left outer join nametable a
on b.id = a.id
where a.id = DateID.AttributePrompt

You would need to turn on the report VLDB setting to support keeping all results in the final pass to enable the LOJ.

You may want to consider producing a report filter which includes the DateID.AttributePrompt containing the filtered version of addresstable and use it to filter your final report.

This is all I can guess at with this much info...

Best,
dmcmunn

P.S. How's the DOD doing anyway ?

 

[MSTRJD]

Thank you all for your suggestions.I tried the ways u all mentioned but doesn't seem it work


I am pasting my actual query here :


select b.Name,b.Customer,a.contact,count(b.phonecall)
from contacttype a
left outer join (select contacttypeid,Name,Customer,phonecall from phonecall where calldate = '2004-08-24 10:29:42.000') as b
on a.contacttypeid=b.contacttypeid
group by b.name,b.Customer,a.contact


Contacttype is a lookup table with 4 types of contact like "Incoming phone call" "outgoing phonecall".....
The counts for all the phonecalls should be displayed no matter there is a corresponding entry in the phonecall table.The above query satisfies that requirement.


The suggested query by dmcmunn is like given below(Correct me if i am wrong):

select b.Name,b.Customer,a.contact,count(b.phonecall)
from contacttype a
left outer join phonecall b
on a.contacttypeid=b.contacttypeid
where b.calldate='2004-08-24 10:29:42.000'
group by b.name,b.Customer,a.contact

The problem here is the WHERE clause makes Left outer join useless.So i get only those records which are present in phonecall table and not all the combinations present in the contacttype table.


"P.S. How's the DOD doing anyway ?" -> not sure what it means!

 

[entaroadun]

where b.calldate='2004-08-24 10:29:42.000' OR b.calldate IS NULL

 

[MSTRJD]

Hi entaroadun,

I think your method might work.Thanks.

But i am wondering, is it not possible to generate a customized sql query like the one i need in mstr? so do we have to either use a indirect method as you have mentioned or create views in order to satisfy some requirements?

once again thanks.

 

[entaroadun]

A report filter with the proper Attribute Join settings should do it.