topic Re: Trouble with Direct Connect Query in QlikView

Trouble with Direct Connect Query

Tue, 07 Feb 2017 13:15:44 GMT

When trying to do a direct connect to oracle I get a 'Syntax Error'

I started out making a regular select load which works as intended. But when I convert that statement into a direct connect it does not work.

I have attempted several variations of this based on what I've seen.

tester:
load ORDER_ID as Order_Id,
PART_ID,
STATUS,
SUB_STATUS_DESCRIPTION;
SQL SELECT "ORDER_ID",
"PART_ID",
STATUS,
"SUB_STATUS_DESCRIPTION"
FROM "mydb"."WIP_ORDER";

SET DirectCacheSeconds= 15;
SET DirectStringQuoteChar=’”’;
SET DirectIdentifierQuoteChar=’[]’;
SET DirectConnectionMax= 4;
SET DirectTableBoxListThreshold= 100000;
SET DirectDistinctSupport=false;
SET DirectIdentifierQuoteStyle=’ANSI’;

DIRECT QUERY
dimension
Order_Id as Order_Id,
measure on_hold
Detail
part_id,
status,
sub_status_description
FROM mydb.wip_order;

Re: Trouble with Direct Connect Query

Anil_Babu_Samineni — Tue, 07 Feb 2017 16:38:27 GMT

Try this, I've remove , before measure

DIRECT QUERY

dimension

Order_Id as order_id

measure

on_hold

Detail

part_id,

status,

sub_status_description

FROM mydb.wip_order;

Check for completion, if need sample syntax

Direct Discovery in Qlikview

Re: Trouble with Direct Connect Query

Tue, 07 Feb 2017 17:36:57 GMT

That worked (I think).

Now I have a different error.

SQL##f - SqlState: S1000, ErrorCode: 911, ErrorMsg: [Oracle][ODBC][Ora]ORA-00911: invalid character

Re: Trouble with Direct Connect Query

Tue, 07 Feb 2017 18:18:36 GMT

Took a look at my variables while waiting for my comment to get moderated.

Fixed the invalid char by setting variable SET DirectIdentifierQuoteChar='""';

Now I get a table not found error.

I have tried:

"mydb"."wip_order"

mydb.wip_order

"mydb.wip_order"

Re: Trouble with Direct Connect Query

Wed, 08 Feb 2017 12:48:53 GMT

Messing with the standard variables did the trick. Documentation for direct connect shows single quote is default for DirectStringQuoteChar But it did not work until I manually entered it with ascii code 39.

SET DirectCacheSeconds= 15;
//Documentation shows that single quote should be default for DirectStringQuoteChar,
//but manually puting in as chr(39) works while leaving it default does not.
LET DirectStringQuoteChar=chr(39);
SET DirectIdentifierQuoteChar=' ';
SET DirectConnectionMax= 4;
SET DirectTableBoxListThreshold= 100000;
SET DirectDistinctSupport=true;
SET DirectIdentifierQuoteStyle=’ANSI’;

ODBC CONNECT32 TO [My Connection String for oracle 9];

DIRECT QUERY
dimension
Order_Id as Order_Id
measure on_hold
Detail
part_id,
status,
sub_status_description
FROM MyDB.CAMS_WIP_ORDER
where date_complete_actual is null
;