Skip to Content
0

How to create a subquery in the where in the graphical view.

Apr 11, 2017 at 08:44 AM

258

avatar image
Former Member

Hello everyone, I have a requirement, I need to do a subquery in the "where" of the query but I would like to be able to do in the graphical views of SAP HANA Studio. Can anyone help me in this? Regards, Alfonso

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

avatar image
Former Member Apr 12, 2017 at 08:10 PM
1

Hello,

Try Calculated Column with complex IF structure. It would be much easier if you plan it before you code.
After that replace original column with calculated one if needed.

Regards,
Mateusz.

Show 5 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hello Mateusz,

Thanks for the help, but I have some questions...

This is my example query,

select "DocEntry", "DocNum" 
from opor where "DocEntry" in (Select "DocEntry" from por1 where "DocEntry" < '30')

How should my if structure be?

For example...

if("DocEntry" < 60 ,"DocEntry"," ")

How do I replace the original column with the calculated column?

I need to add a filter in "DocEntry" field of the table OPOR or how would it be?

And last question, I need to create a Attribute, Analytic or Calculation View? or Can it be anyone?.

Thanks Mateusz.

Regards,

Alfonso.

if.png (34.3 kB)
0
Former Member
Former Member

Hi Alfonso,

I'm not sure what do you mean in your comment, so my answer might be imprecise.
Example solution:
1. Use calculation view
2. Create 2 projections, first for table OPOR the second for POR1
3. New calculated column in projection POR1, if("DocEntry" < '30','Y','N')
4. Create outer join OPOR (main) and POR1, join on keys; take everything from OPOR and only the calc col from POR1.
5. New calc column in the Join block, apply your logic

To replace a column with a calculated column in graphical calculation view, remove the column you wish to replace and in the next block change name of the calculated column to match the removed column's old name.

Check some tutorials for more info, like http://teachmehana.com/


Regards,
Mateusz.

1
Former Member

Hi Mateusz,

Thank you very much. This is what I am looking for more or less.

Regards,

Alfonso

0
Former Member

After reading your help, this is what I have done:

1) I have created the two projections, one for "OPOR" and another for "POR1".

2) In the projection of "POR1", I added a flier ("DocEntry" <30).

3) I have made a join between the two projections.

4) I created another projection, where I create a column calculated with the docentry.

5) I show the necessary data in semantics

And I get the result I need.

Thank you very much, Mateusz.

Regards,

Alfonso.

cap.png (41.0 kB)
0
Former Member
Former Member

Good but make sure that you are using the number of records that you want to retrive is correct (from what I see you are using inner join).

You are using Filters, if you don't need those rows from POR1 then it's better solution than flag-type calculated column (less calculations).

Regards,
Mateusz.

1