topic Re: 'SUBSTR' is not a recognized built-in function name- Script Error in QlikView

'SUBSTR' is not a recognized built-in function name- Script Error

Jacob — Sat, 16 Nov 2024 04:57:07 GMT

Hi All,

I am getting following .

Connector reply error: SQL##f - SqlState: 37000, ErrorCode: 195, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]'SUBSTR' is not a recognized built-in function name.: SQL SELECT DATAAREAID,SUBSTR(STORE_NUMBER,4) as STORE_NUMBER1,STORE_NUMBER,SUPERVISOR,AREA,CATEGORY,SHOPNAME FROM shop_list where REGION = 'UAE' AND COMPANY = 'RETAIL' and STORE_NUMBER <> ' '

My Script

SQL SELECT DATAAREAID,SUBSTR(STORE_NUMBER,4) as STORE_NUMBER1,STORE_NUMBER,SUPERVISOR,AREA,CATEGORY,SHOPNAME FROM shop_list where REGION = 'ACX' AND COMPANY = 'RETAIL' 
and STORE_NUMBER <> ' ';

Re: 'SUBSTR' is not a recognized built-in function name- Script Error

PradeepReddy — Mon, 17 Dec 2018 07:11:14 GMT

if you are extracting the data from SQL server try like this..
substring(STORE_NUMBER, Start_Position_Number, Length) as SortNumber1

Re: 'SUBSTR' is not a recognized built-in function name- Script Error

Anil_Babu_Samineni — Mon, 17 Dec 2018 07:16:38 GMT

I didn't see any field related from SELECT statement like red color? Did you taken any where?

SQL SELECT DATAAREAID,SUBSTR(STORE_NUMBER,4) as STORE_NUMBER1,
    STORE_NUMBER,SUPERVISOR,AREA,CATEGORY,SHOPNAME FROM shop_list 
    where REGION = 'ACX' AND COMPANY = 'RETAIL' and STORE_NUMBER <> ' ';

Re: 'SUBSTR' is not a recognized built-in function name- Script Error

swapniltaz — Fri, 28 Dec 2018 06:28:50 GMT

Hi ,

I undesrtand you are using SQL server , In SQL server we have Substring function as Substring(STORE_NUMBER,4)

And not Substr(STORE_NUMBER,4) , Chnage this and it should work

)

Re: 'SUBSTR' is not a recognized built-in function name- Script Error

Jacob — Fri, 28 Dec 2018 09:31:22 GMT

Thanks for help

i have one more question

can u help me with this.

https://community.qlik.com/t5/New-to-Qlik-Community/SQL-IsNull/m-p/1524405#M7802