How to use IN and Between operator in CE Function

[Former Member]

Hi

In Sql we are using the following  expression,

SELECT * FROM Customers

WHERE City IN ('Paris','London','Berlin','Bangalore');

If we want to write the same in "CE function" then How we can write.

thanks

Bapin

Message was edited by: Tom Flanagan

[Former Member · Accepted solution]

Hi

use CE_PROJECTION(:var_tab_cust,["city"],'in("city",''Paris'',''London'',..)');

Best Regars

[Former Member · Accepted solution]

Hi Dmitriy,

I have tried as per your suggested syntax but I got the error.

Please go through the code bellow and next the error

CODE

Create procedure "NEO_3FBAUC9ICQ9CNU5H4NQNQXMVO"."in_test10" ()

language sqlscript sql security invoker as

Begin

     var1 = CE_COLUMN_TABLE("NEO_3FBAUC9ICQ9CNU5H4NQNQXMVO"."Billable",["Project ID"]);

     var2 = CE_PROJECTION (:var1, ["Project ID"],'in(''B001'',''B002'')');

select * from :var2;

END

Error

SAP DBTech JDBC: [2048]: column store error: [34011] failed to save calculation scenario : The following errors occured: Inconsistent calculation model (34011)

Details (Errors):

- CalculationNode ($$VAR2$$) -> filter: Expression cannot be converted to query entries: Expression may not be converted to query entries;[here]in(string 'B001', string 'B002').

Thanks and Regards

Bapin

[Former Member · Accepted solution]

Hi

Dmitriy,

I have tried with the code

Create procedure "NEO_3FBAUC9ICQ9CNU5H4NQNQXMVO"."in_test10" ()

language sqlscript sql security invoker as

Begin

var1 = CE_COLUMN_TABLE("NEO_3FBAUC9ICQ9CNU5H4NQNQXMVO"."Billable",["Project ID"]);

var2 = CE_PROJECTION (:var1, ["Project ID"],'in("Project ID",' 'B001' ',' 'B002' ')');

select * from :var2;

END

and it is working fine. thanks for your help.

Now can you tell me how to use Between......

Thanks and Regards

Bapin

[Former Member · Accepted solution]

Try to use smth. like this: "A">''1'' and "A" < ''2''

[Former Member · Accepted solution]

Hi Dmitriy,

I have tried that and it is working fine also.

just I want to know how to use between operator that's it. 

Thanks for your help.

Thanks and Regards

Bapin

[Former Member · Accepted solution]

There is no between operator in CE* functions.

[Former Member · Accepted solution]

Hi Dmitriy,

Can you please clear one more issue of mine.

I have a list of value stored in a variable i.e. VAR_CI through Select statement.

now I want to use the values via "IN" operator. the SQL statement what I am using is

select * from "NEO_3FBAUC9ICQ9CNU5H4NQNQXMVO"."PrjRev"

Where "NEO_3FBAUC9ICQ9CNU5H4NQNQXMVO"."PrjRev"."CI" in (select * from :VAR_CI);

Can you tell me the equivalent CE function.

Thanks and Regards

Bapin

[Former Member · Accepted solution]

Hi Tripathy,

try to use APPLY_FILTER(<tab_name>,<filter_variable>)

Example: