Need help on subquery

[MSTRJD]

I have a requirement in which i need a subquery as highlighted below:

select gtn, btn, date
from user e
join cycle c
on e.id = c.id
where gtn = '648554807' and date = (select max(date)from user e
join cycle c
on e.id = c.id
where gtn = '648554807')
group by gtn, btn, date

Can any one help me in generating the subquery as mentioned above in microstrategy?

Thanks.

 

[mstrNate]

Create a custom filter using the "Advanced Qualification" window, and add in something like the following ApplyComparison function.

ApplyComparison("#0 in (select max(date) from user e join cycle c on e.id = c.id where gtn = #1)",[Date]@Id, [GTN]@Id)

Might be other ways as well...

hth,
Nate

 

[MSTRJD]

Hi mstrNate,

Thanks a lot.It worked.

 

[MSTRJD]

I have question on the above applycomparison in the filter expression with prompts embedded into it as shown below:

ApplyComparison("#0 in (select max(date) from user e join cycle c on e.id = c.id where #1 and #2)",[Date]@Id,?[GTN],?[BTN])

Both the GTN amd BTN are optional prompts.Now when both the prompts are answered,the above subquery is generated in the sql.But if i answer only one prompt and leave the other one answered,the sub query won't be generarted.

is there any way I can have the sub query generated no matter what i answer the prompt or not?

Thanks

 

[mstrNate]

You might need to make sure you've got some sort of default value in those prompts. That way, you won't have to answer it. Also, from your query above, it looks like you're not restricting on any fields... "where #1 and #2" ... should that be something like ..."where gtn = #1 and btn = #2"?

 

[MSTRJD]

the prompts used are 'qualify on attributes'...and if i use "where gtn = #1 and btn = #2"? the query will be

select gtn, btn, date
from user e
join cycle c
on e.id = c.id
where gtn = '648554807' and date = (select max(date)from user e
join cycle c
on d.id = c.id
where e.gtn =e.gtn= '648554807')
group by gtn, btn, date

so i changed it to where #1 and #2" and it worked fine as where e.gtn= '648554807')

please let me know if there is any problem with my approach

Once again thanks.

 

[mstrNate]

Sorry. I should have specified I was thinking of Value Prompt, Numeric type when I gave the sql above. If it's working the way you are doing it, then I don't see a problem.

In fact, I might be able to use your approach with some things I do. Previously, I had problems using "Qualify on Attribute" prompts in subqueries, because of statements like "e.gtn='1111'" generated. I suppose if I change the alias in the subquery to match what will likely be generated like you've done, I could also use qualification prompts as well. So, thanks. I'll try that next time.