Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
OmarBenSalem

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

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 !!!!!!

 

Capture.PNG

and in ORACLE:

Capture.PNG

Why? Just WHY?...

 

8 Replies
nazaQ
Partner - Contributor III
Partner - Contributor III

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
Author

Thanks for the reply but this has not fixed it

OmarBenSalem
Author

Hi Marcus,

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

Capture.PNG

 

and this is the database:

Capture.PNG

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:

Capture.PNG

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

Capture.PNG

and in ORACLE:

Capture.PNG

Have no clue how to fix this...

OmarBenSalem
Author

Any ideas? Please? 

@sunny_talwar @ 

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
Author

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