Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I have a problem with this query.
"SELECT O.ordernumber
,magasin.VALUE as "ID Magasin"
,vendeur.VALUE as "ID Vendeur"
FROM `SPC_ORDER` O
INNER JOIN `SPC_EXTRAFIELDORDER` magasin on magasin.INSTANCEKEY=CAST(O.ORDER_ID AS CHAR(20))
LEFT JOIN `SPC_EXTRAFIELDORDER` vendeur on magasin.INSTANCEKEY=vendeur.INSTANCEKEY
WHERE O.CREATIONDT >= str_to_date('01/07/2013 00:00:00','%d/%m/%Y %H:%i:%s')
AND vendeur.NAME='borneCodeFRA'
AND not isnull(vendeur.VALUE)
AND magasin.NAME='borneIdMag'
AND not isnull(magasin.VALUE);"
If i execute a explain plan with MySQL WorkBench or Toad Mysql ,it returns :
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
1 | SIMPLE | O | ALL | CREATION_IDX | 1524709 | Using where | |||
1 | SIMPLE | vendeur | ref | Index_InsTypKey,idx_name,Index_IK_NAME | Index_IK_NAME | 124 | func,const | 1 | Using where |
1 | SIMPLE | magasin | ref | Index_InsTypKey,idx_name,Index_IK_NAME | Index_IK_NAME | 124 | func,const | 1 | Using where |
.
If i execute the same explain plan with QlikView, it returns :
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | magasin | ref | Index_InsTypKey,idx_name,Index_IK_NAME | Index_IK_NAME | 124 | WLPEM.vendeur.INSTANCEKEY,const | 1 | Using where |
1 | SIMPLE | O | ALL | CREATION_IDX | 1524709 | Using where | |||
1 | SIMPLE | vendeur | ref | Index_InsTypKey,idx_name,Index_IK_NAME | idx_name | 102 | const | 2474940 | Using where |
To connect to the database, I use ODBC connector.
I think that QlikView misinterprets the function Cast.
What do you think?
Thank you.
In your case you queries your database directly with its own functionalities and limitations will be only by the odbc-driver. Often it is better and faster to query only the raw-data and make all transforming within qlikview.
- Marcus