Mid or Weekday - is not an integrated function name recognized - why?

[#1]

Hi all,

I need help to programmer a script with a new variable which is created from another variable of my database.

I need add in the SELECT a new variable, similar to...

,case

        when MYVARIABLESTRING is not null and mid(BILLNUMBER,3,1) in ('A','B') then 'White'

        when MYVARIABLESTRING is not null and mid(BILLNUMBER,3,1) in ('C','D') then 'Black'

        when BILLNUMBER is null then 'Green'

        else 'Blue'

     end as CreatedVariable

I have also tried with...

,case

        when MYVARIABLESTRING is not null and (mid(BILLNUMBER,3,1)='A' or  mid(BILLNUMBER,3,1)='B') then 'White'

        when MYVARIABLESTRING is not null and (mid(BILLNUMBER,3,1)='C' or  mid(BILLNUMBER,3,1)='D') then 'Black'

        when BILLNUMBER is null then 'Green'

        else 'Blue'

     end as CreatedVariable

and I have also tried in the LOAD and with the función "SubstringCount"...

but the program tell me the following error:

"

Se ha producido el siguiente error:

Unable to get column information for the fields that are used in the query: ERROR [HY000] [Qlik][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]'mid' no es un nombre de función integrada reconocido. ERROR [HY000] [Qlik][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]No se pudo analizar el lote debido a errores de compilación.

"

To what should?, with the rest of functions as year, month, within a case I have not given problem.

Thanks in advance,

Regards,

[PrashantSangle · Accepted solution]

Where you are using mid() in sql query or load query.

if your database source is sql server then use substring(column_name,start,length)

Regards,

[Miguel_Angel_Baeyens]

Quizá deberías hacer la sentencia SQL más sencilla y elaborar la parte del LOAD, siguiendo tu ejemplo:

Tabla:

LOAD If(Len(MYVARIABLESTRING) > 0 AND Match(Mid(BILLNUMBER, 3, 1), 'A', 'B') > 0, 'White',

     If(Len(MYVARIABLESTRING) > 0 AND Match(Mid(BILLNUMBER, 3, 1), 'C', 'D') > 0, 'Black') AS Field

;

SQL SELECT *

FROM table

;

Si no me ha fallado ningún paréntesis, el campo "Field" tendrá ahora el valor "White" o "Black" dependiendo de las condiciones mencionadas.

Como clarificación, MYVARIABLESTRING y BILLNUMBER en el caso de arriba deben ser campos de la tabla incluida en la sentencia SELECT o variables ya definidas en el script.

[#4]

Many thanks!

I have used substring(column_name, start, length) in the Select of the query, and It has worked!! For all that you have indicated to me, being the sql server.

As I know the code of queries in sql server, now everything goes very well !!

Thanks again!!

[#5]

Muchas gracias Miguel,

Aun, la parte del LOAD la tengo un poco menos controlada y me ha servido para para poder hacer sentencias en esa parte. Muchas muchas gracias!!