Request does not give the same result in oracle and qlik sense

[OmarBenSalem]

Hi all , 

I'm really confused and hope someone would have an explanation for this;

I have a request that does not give back the same result when executed directly in ORACLE and in QLIK SENSE.

Here's the request:

SELECT "ID_DEMANDE_TAMPON",

length(
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
REGEXP_REPLACE("DESCRIPTION",
'[^a-zA-Z0-9\ê@:"0123_45=6789/àé+?!&#èÉçÇ]+', '')
,' ',''),'-',''),'.',''),'\',''),'=',''),'+',''),'@',''),'/',''),'_',''),')',''),'(',''),' ','')) as "1",
length (replace(replace( replace(replace( replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(DESCRIPTION,' ',''),'-',''),'.','')
,'\',''),'=',''),'+',''),'@',''),'/',''),'_',''),')',''),'(',''),','),'''',''),' ',''),CHR(10),'')) as "2",
case when
length(
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
REGEXP_REPLACE("DESCRIPTION",
'[^a-zA-Z0-9\ê@:"0123_45=6789/àé+?!&#èÉçÇ]+', '')
,' ',''),'-',''),'.',''),'\',''),'=',''),'+',''),'@',''),'/',''),'_',''),')',''),'(',''),' ',''))
= length (replace(replace( replace(replace( replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(DESCRIPTION,' ',''),'-',''),'.','')
,'\',''),'=',''),'+',''),'@',''),'/',''),'_',''),')',''),'(',''),','),'''',''),' ',''),CHR(10),''))
then 'Non' else 'Oui' end as "Special_Char_demande"

FROM "SOCLE"."DEMANDE_TAMPON";

basically what this does is control wheither their is a special char in the description field or not; it works just fine:

example for an ID:

in oracle :

1 = 310 , 2= 310 then no special char

in qlik

1=309 , 2=310 their is a special char !!!!!!

and in ORACLE:

Why? Just WHY?...

[nazaQ]

Hi,
Have you tried specifing "SQL" word before the select?
Have you tried checking you odbc configuration? For oracle there's some parameters to set.

[marcus_sommer]

For the reason that Qlik doesn't execute the SQL else it just transferred the statement to the database and received only from there the results of the query I think it might be caused by the used database-driver which might not be able to handle all the special-chars stuff properly.

Beside this doing the same within Qlik by using keepchar/purgechar might be an (easier) alternatively.

- Marcus

[OmarBenSalem]

Thanks for the reply but this has not fixed it

[OmarBenSalem]

Hi Marcus,

Here's the driver I'm using (the one that qlik sense proposes for oracle databases) 

and this is the database:

When I try to load the field directly as it is and it contains some special chars; it won't load (don't know how to fix it); that's why I'm using the regex thing beacause I was not able to handle things in the load statement part.

See the error I have when I try to load the field:

Besides, to return to my query, see when I executed within Qlik SENSE:

and in ORACLE:

Have no clue how to fix this...

[OmarBenSalem]

Any ideas? Please? 

@sunny_talwar @ swuehl @Gysbert_Wassenaar @tresesco   @jonathandienst@ @rwunderlich

Anyone?...

[marcus_sommer]

I don't know if there is any other more suitable driver available but maybe within the used one is an option to define a certain char-set which might be able to handle all these special chars.

Another approach might be to transform these data with cast(), convert() or similar and to re-transform it again on the Qlik side.

Further possible could be to export the data from Oracle into a text-file and to load this into Qlik.

- Marcus

[OmarBenSalem]

Thanks again for ur availibilty Marcus,

if u please are aware of any other options to add in the connector in order to let it "accept" these special chars, don't hesitate to share.

For the further Qlik sided transformations, if the select statement returns false count for each side (exp: 1=314 and 2=315), I don't see how it would be possible to correct it in the load statement...

For the text file, this is not an option ...

Greatly appreciate any help !

[marcus_sommer]

Within the wizard there might not be an option to specify a char-set but if it's a general supported feature it could be added manually. Maybe the Oracle community has any hints in this direction.

- Marcus