How to use wildcard in where clause CE Function

[Former Member]

Hi all,

    How to use

1. concatenate,shift,sub string in CE Function

2.  wildcard in where clause in CE Function .

It will be helpful if you provide us some examples

Thanks in advance

[Former Member · Accepted solution]

Hi Mano,

The table structure i created for the following examples :

create table TAB1(col1 varchar(20),col2 varchar(20),col3 integer)

1) Concatenate:

     Use CE_CALC() function in your projection list.

          var1=ce_column_table("TAB1",["COL1","COL2"]);

          var2=ce_projection(:var1,["COL1","COL2",CE_CALC('"COL1" + "COL2"',varchar(40)) AS                                         "COL4"]);

2) shift

     By shift do you mean to shift the chars of your string one to the left? you can achieve this by using the SUBSTR function.

          charVar:=substr('someText',2,length('someText'));

          select :charVar from dummy;

3) SUBSTR already shown in the above example

4) I always use a workaround of using INSTR function to replace the like operator.

Instr function will return zero if the supplied string is not present in the parent string. hence in a way it can act as a like operator.

          For eample if you want to check for name like '%A%' , then you can use INSTR(NAME,'A')>0

          Another example with for name like 'A%' can be INSTR(NAME,'A')=1

          Ce implementation can be done with  (for scenario in where condition  is COL1 like '%A%' )

               var1=ce_column_table("TAB1",["COL1","COL2"]);

               var2=ce_projection(:var1,["COL1","COL2"],'INSTR("COL1",''A'')>0');

      PS: the qoutes surrounding A in the projection is double single qoutes.

Regards,

Lalu George

[former_member182302 · Accepted solution]

Hi Lalu,

As you already tried , can you check with MATCH() instead of INSTR?

Regards,

Krishna Tangudu

[Former Member · Accepted solution]

Hi Krishna,

I think that can be kept as an excercise for Mano

Regards,

Lalu George

[Former Member · Accepted solution]

Dear George,

   I tried for concatenate in CE function as below

/********* Begin Procedure Script ************/

BEGIN

T1 = CE_COLUMN_TABLE("HANAUSER"."LIPS",["VBELN","MATNR","LFIMG"]);

  var_out = CE_PROJECTION(:T1,["VBELN","MATNR","LFIMG",CE_CALC('"LFIMG" + "MATNR"',NVARCHAR(30)) AS "CON"]);

END /********* End Procedure Script ************/

Target as

VBELN NVARCHAR(10)

MATNR NVARCHAR(18)

LFIMG  DECIMAL(13,3)

CON NVARCHAR(30)

But i am getting error

Internal deployment of object failed;Repository: Encountered an error in repository runtime extension;Internal Error:Deploy Calculation View: SQL: transaction rolled back by an internal error: column store error: [34011] failed to save calculation scenario : The following errors occured: Inconsistent calculation model (34011)nDetails (Errors):n- CalculationNode ($$VAR_OUT$$) -> attributes -> calculatedAttribute (CON) -> expression: Expression is not valid: Evaluator: type error in expression evaluator;string string([here]plus(fixed8_10.3 "LFIMG", fixedstring_18 "MATNR")).nnnSet Schema DDL statement: set schema "SYSTEM"nType DDL: create type "_SYS_BIC"."10AMBATCH/CL_SCRIPT_TEST/proc/tabletype/VAR_OUT" as table ("VBELN" NVARCHAR(10), "MATNR" NVARCHAR(18), "LFIMG" DECIMAL(13,3), "CON" NVARCHAR(30))nProcedure DDL: create procedure "_SYS_BIC"."10AMBATCH/CL_SCRIPT_TEST/proc" ( OUT var_out "_SYS_BIC"."10AMBATCH/CL_SCRIPT_TEST/proc/tabletype/VAR_OUT" ) language sqlscript sql security definer reads sql data as  n /********* Begin Procedure Script ************/ n BEGIN nT1 = CE_COLUMN_TABLE("HANAUSER"."LIPS",["VBELN","MATNR","LFIMG"]);n t var_out = CE_PROJECTION(:T1,["VBELN","MATNR","LFIMG",CE_CALC('"LFIMG" + "MATNR"',NVARCHAR(30)) AS "CON"]);nn tnnEND /********* End Procedure Script ************/n

Kindly suggest me where i went wrong

Thanks

[Former Member · Accepted solution]

Dear George,

    i have also tried instr function for wildcard search

T1 = CE_COLUMN_TABLE("HANAUSER"."LIPS",["VBELN","MATNR","LFIMG"]);

  var_out = CE_PROJECTION(:T1,["VBELN","MATNR","LFIMG"],'INSTR("VBELN","0080012995")>0');

But i am getting error

[Former Member · Accepted solution]

Hi Mano,

Which Version are you using ?

The examples work fine in my SPS08 box.

Regards,

Lalu George

[Former Member · Accepted solution]

Hi George,

I am using SPS07

[Former Member · Accepted solution]

Dear George/Krishna,

   Wildcard filter using MATCH() and Concatenate using + operator is working fine ,

Thanks for your help and spending time

[former_member182302]

Hi There,

For calculations you can use CE_CALC and for applying filters you may want to try with CE_PROJECTION and MATCH

Regards,

Krishna Tangudu

[Former Member]

Dear Krishna,

   Can you send me sample code for Wildcard filter and string function.

Thanks