"Not in" in Graphical View

[Former Member]

Hello Gurus,

I want to implement "Not In" using Input Parameter of type Column with multiple values from control table.

I would like to filter out the data based on control table.

My SGK_CONTROL Table has values (20,40,80) in ID column. I have departments (10,20,30,40, ......... 120) in employees table.

The processing should be done for all the records except values in control table. i.e. 20,40,80.

SQL would be like this.

select job_id, department_id, sum(salary)

from   employees

where department_id not in (select id from sgk_control);

Please go through all the attached Images.

I need to understand following:

1. Why it is asking/prompting for input, however, it is already assigned/defined (all column values from the table/view) while creating Input Parameter?
2. How do I filter out the data using SGK_CONTROL table?
3. Why the data is processed for the entered values (20,40 here in the screen shot) if I have mentioned the predicate "(not in ("DEPARTMENT_ID",$$IP_DEPT_NO$$))" ?
4. Why it is using IN predicate however I have defined as NOT IN?

Request your inputs so that I can understanding SAP HANA Input Parameters more !

Thanks in advance.

Shirish.

Image# 1

Image# 2

Image# 3

Image# 4

Image# 5

Image# 6

Image# 7

Image# 8

[upamanyu_mukherjee · Accepted solution]

Hi Shirish ,

I recreated the scenario at my end and it seems to work just like Rohit's case.

Which version are you on?

Not totally sure about the first question but i guess the reason it prompts for input is that IP of type column acts as LOV unlike in derived from table/procedure where the value is passed on directly to the view.

Best Regards,

Upamanyu

[upamanyu_mukherjee · Accepted solution]

Hi Shirish,

Can you just apply the filter using the  expression editor instead of the "Apply Filter" option and try it out - (not in ("DEPT_ID",$$IP_FILTER$$))

This might work!

Regards,

Upamanyu

[Former Member · Accepted solution]

Thanks Upamanyu.

Image# 3 shows the same.

Shirish.

[Former Member · Accepted solution]

Hi Upamanyu,

It means that there is no differenct between the Direct and Column Input Parameters.

Shirish.

[Former Member · Accepted solution]

Hi Upamanyu,

This is somewhat crazy thing. Seems to be a bug.

I tried to manually add the filter, and it worked as expected.....

Shirish.

[kr_pavankumar]

Hi Shirish,

 Why it is asking/prompting for input, however, it is already assigned/defined (all column values from the table/view) while creating Input Parameter?

Defined Input parameter will prompts for an entry from the user. Its NOT assigning the values to the Input parameter from SGK_Control - ID.

Values in Column 'ID' from SGK_Control will acts as Value help for Input parameter - IP_DEPT_NO i.e., whenever user tries to hit the help button while entering the Input parameter, values will be populated from ID of SGK_CONTROL table.

Is my understanding correct about this scenario:

Control table have DEPTIDs

Employee table have DEPTIDs

Whenever you pass a set of values to Input parameter, this has to cross check in SGK_Control table and if it finds the entry that should be skipped. Report should show up the values for remaining DEPTIDs. Is this right??

-- KRPK

[Former Member]

Hi Pavan,

Yes. Report should show up the values for remaining DEPT_IDs. However, it is NOT behaving as I am thinking.

Defined Input parameter will "not" always prompts for an entry from the user. If you define an Input Parameter Derived From Table. It does NOT ask for the input. However, it selects first row from the table and returns the value of desired column. This works fine in my case and it does excludes department 40 and show up the results.

Still waiting for the answer.

[rohit_chowdhury17]

Hello Shirish,

I have tried to replicate the scenario with the following data set .Over here not in filter for multiple values when applied through INPUT Parameter defined as  COLUMN is working fine in SAP HANA Studio  Version: 2.0.15 .I think its similar to the scenario mentioned above.

The control table over here is CONTROL_TEST1 and the driving table is MASTER_TEST1.

Applied filter for in the control table for 40 and 30 .When performing data preview we are getting data set devoid of 40 and 30 from the driving table.

Refer the screenshots for further information.

Regards

Rohit

[Former Member]

Thanks Rohit.

I am using SAP HANA Studio : 2.0.19. Will it have an impact?

I am still getting the same results.

Regards,

Shirish.