topic Using IsNum in SQL SELECT in QlikView

Using IsNum in SQL SELECT

Black_Hole — Sat, 16 Nov 2024 03:32:09 GMT

Hello all,

When I run my script I have an error in the following lines in particular:

SQL SELECT *

FROM Table_Cus

WHERE SUBSTRING(NUM_CUS,1,1)='S' AND ISNUMERIC(SUBSTRING(NUM_CUS,2,LEN(NUM_CUS)-1));

In attached file, I screened the detail of my error.

Please could you help me to identify what's wrong in my where clause.

Thank you in advance for your help.

Re: Using IsNum in SQL SELECT

Anil_Babu_Samineni — Mon, 20 May 2019 09:17:33 GMT

Can i convert this to Qlik, If so - Here u go?

Load * Where Mid(NUM_CUS,1,1)='S' AND ISNUM(Mid(NUM_CUS,2,LEN(NUM_CUS)-1));
SQL SELECT * FROM Table_Cus

Re: Using IsNum in SQL SELECT

peterwh — Mon, 20 May 2019 09:25:54 GMT

Hello,

in the error message I see, that you're using a DB2 database. I've made a search on "Db2 for i SQL reference" and it seems there is no "Isnumeric" function.

On "www.experts-exchange.com" I found the following solution, maybe this will help you:

you can use the translate function and translate each numeric character to blank,
then use trim and check if you get the empty string
for example

select ....
from
where rtrim(ltrim(translate(your_string,' ','1234567890'))) = ''

Kind regards

Peter

Re: Using IsNum in SQL SELECT

Black_Hole — Mon, 20 May 2019 09:55:47 GMT

Hello @Anil_Babu_Samineni , @peterwh ,

Thank you both for your help.

@Anil_Babu_Samineni : I try it and it's run successfully.

@peterwh : I understand why sometimes there are functions which are not identified in my QVW. I will consider the connection when I will use the functions implemented in QVW.