topic Re: Oracle Loading and Parenthesis Problems in QlikView

Oracle Loading and Parenthesis Problems

Wed, 25 Nov 2020 16:16:04 GMT

I get the following error when I add the //line into the load script below.

SQL##f - SqlState: 37000, ErrorCode: 907, ErrorMsg: [Microsoft][ODBC driver for Oracle][Oracle]ORA-00907: missing right parenthesis

Can anyone help please?

SQL SELECT "ANT_PART_NO",

"ANT_PART_NO2",

"ANT_RELEASE_LEVEL",

"ANT_RELEASE_LEVEL2",

"ANT_SITE",

"ANT_SITE2",

COMMENTS,

"CREATED_BY",

"CREATED_DATE",

"CREATED_REASON_CODE",

"CREATED_REASON_TEXT",

"CUSTOMER_INSTALL",

"CUSTOMER_NO" AS NOTIFMCUSTNOS,

"CUSTOMER_REFERENCE",

"CUSTOMER_SIGN_DATE",

DATA,

"ENG_ACTUAL_FINISH",

"ENG_ACTUAL_START",

"ENG_ASSIGNED_CODE",

"ENG_BILLABLE_TIME",

"ENG_ORIGINAL_CODE",

"ENG_PLANNED_START",

"ENG_PO_NO",

"FM_VEHICLE",

"INSTALLATION_DATE",

INVOICED AS NOTIFMASINVCED,

"LAST_AMENDED_BY",

"LAST_AMENDED_DATE",

"LEASE_END_DATE",

"LEASE_PERIODS",

"LEASE_START_DATE",

"LINKED_TO",

"LINKED_TO_SERVICE",

"LOC_ID_NUMBER",

//IF (LEFT("LOC_ID_NUMBER",2) = '1.','DT','FM') AS "DATABASE",

"LOC_PART_NO",

Re: Oracle Loading and Parenthesis Problems

krishnacbe — Fri, 07 Oct 2016 17:50:07 GMT

Hi,

can you share the full query to debug the issue.

Re: Oracle Loading and Parenthesis Problems

Anonymous — Fri, 07 Oct 2016 17:53:37 GMT

The // is a Qilk comment not a SQL comment, try deleting the line.

Re: Oracle Loading and Parenthesis Problems

sunny_talwar — Fri, 07 Oct 2016 17:54:22 GMT

This is not the complete query, right? I don't see any issue in what you have provided, may be if you can paste the rest of the query it might help

Re: Oracle Loading and Parenthesis Problems

Anonymous — Fri, 07 Oct 2016 17:56:05 GMT

or use a SQL comment, usually --

or the /* */ comment style

Re: Oracle Loading and Parenthesis Problems

sunny_talwar — Fri, 07 Oct 2016 17:58:08 GMT

Bill I think QlikView style commenting does work in SQL code. I have been running a code with // commenting and it seems that it works for me.

Re: Oracle Loading and Parenthesis Problems

Anonymous — Fri, 07 Oct 2016 18:03:31 GMT

Do you prefix your SELECT with SQL ?

Re: Oracle Loading and Parenthesis Problems

sunny_talwar — Fri, 07 Oct 2016 18:04:43 GMT

Checking now

Re: Oracle Loading and Parenthesis Problems

sunny_talwar — Fri, 07 Oct 2016 18:07:36 GMT

I did

Re: Oracle Loading and Parenthesis Problems

Anonymous — Fri, 07 Oct 2016 18:14:50 GMT

Sunny: May have been sorted with some QlikView Release

Ian: What version are you on ?

Re: Oracle Loading and Parenthesis Problems

Anil_Babu_Samineni — Sat, 08 Oct 2016 13:07:39 GMT

Try to create same expression in db and then load that field. Or else load that db using load statement then followed by select

Then use preceding load like

Load *, condition as field;

Load * from table;

Select * from table;

Re: Oracle Loading and Parenthesis Problems

Mon, 10 Oct 2016 08:40:07 GMT

Bill

Thanks for taking the time to look at this.

The whole script works fine until I add the line

IF (LEFT("LOC_ID_NUMBER",2) = '1.','DT','FM') AS "DATABASE",

However, when I use the statement in list box as an expression it works perfectly. I am just trying to load the reference so that I do not have to get it to calculate as this will slow down the process. I have lots of data. There is a unit type within the data source that is for instance FM3316i or the unit number 1.7896 as an example this denotes the separate databases that it loads from.

I have read something about Oracle databases having a problem with such loads, and to put the 1. into another field and compare the field rather than '1.'?

Any help is gratefully received.

Re: Oracle Loading and Parenthesis Problems

Mon, 10 Oct 2016 08:41:43 GMT

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YY';

SET TimestampFormat='DD/MM/YY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

SET FirstWeekDay=0;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-GB';

ODBC CONNECT32 TO i2gerp_live;

//-------- Start Multiple Select Statements ------

SQL SELECT "BILLING_PLAN_ID",

"BILLING_PLAN_LINE_NO",

INVOICED,

"NOTIFICATION_NO",

"PERIOD_NO",

RELEASED,

"SALES_INVOICE_LINE_NO" AS BPSALESINVLINENOS,

"SALES_INVOICE_NO" AS BPSALEINVNO,

VALUE,

"VALUE_CURR",

"WAS_ZERO_TEMP",

"YEAR_NO"

FROM MFGOWNER."BILLING_PLAN_LINE"

WHERE "YEAR_NO" > 2015;

SQL SELECT "BILLING_FREQUENCY",

"BILLING_PLAN_ID",

"COMMUNICATIONS_TYPE",

"CURR_NOTIFICATION_NO",

"CURRENCY_CODE",

"CUSTOMER_NO",

DEFERRED,

"DEPOT_NO",

"EARIEST_TERMINATION_DATE",

"EARIEST_TERMINATION_MONTHS",

"PART_NO",

"RELEASE_LEVEL",

"RENEWAL_DATE",

"RENEWAL_STOPPED",

"SALES_INVOICE_LINE_NO",

"SALES_INVOICE_NO",

"SALES_ORDER_LINE_NO",

"SALES_ORDER_NO",

"START_DATE",

STATUS

FROM MFGOWNER."BILLING_PLAN";

SQL SELECT "CUSTOMER_NO",

NAME,

"SALES_LEDGER_ACCOUNT_NO"

FROM MFGOWNER.CUSTOMER;

SQL SELECT "ANT_PART_NO",

"ANT_PART_NO2",

"ANT_RELEASE_LEVEL",

"ANT_RELEASE_LEVEL2",

"ANT_SITE",

"ANT_SITE2",

COMMENTS,

"CREATED_BY",

"CREATED_DATE",

"CREATED_REASON_CODE",

"CREATED_REASON_TEXT",

"CUSTOMER_INSTALL",

"CUSTOMER_NO" AS NOTIFMCUSTNOS,

"CUSTOMER_REFERENCE",

"CUSTOMER_SIGN_DATE",

DATA,

"ENG_ACTUAL_FINISH",

"ENG_ACTUAL_START",

"ENG_ASSIGNED_CODE",

"ENG_BILLABLE_TIME",

"ENG_ORIGINAL_CODE",

"ENG_PLANNED_START",

"ENG_PO_NO",

"FM_VEHICLE",

"INSTALLATION_DATE",

INVOICED AS NOTIFMASINVCED,

"LAST_AMENDED_BY",

"LAST_AMENDED_DATE",

"LEASE_END_DATE",

"LEASE_PERIODS",

"LEASE_START_DATE",

"LINKED_TO",

"LINKED_TO_SERVICE",

"LOC_ID_NUMBER",

//IF (LEFT("LOC_ID_NUMBER",2) = ''1.'','DT','FM') AS "DATABASE",

"LOC_PART_NO",

"LOC_PIN_NUMBER",

"LOC_RELEASE_LEVEL",

"LOC_SERIAL_NO",

"LOC_SITE",

"MONITORED_BY",

"NOTIFICATION_NO",

"ORIGINAL_SLA_DATE",

"ORIGINAL_SLA_DAYS",

REINSTALL,

"SALES_ORDER_LINE_NO" AS NOTMSALESORDLINENO,

"SALES_ORDER_NO" AS NOTMSALESORDNOS,

SIM,

"SLA_CODE",

"SLA_DATE",

STATUS AS NOTIFMSTATUS,

"UPDATE_RATE",

"VEHICLE_REG_NO",

"WARRANTY_EXPIRY_DATE"

FROM MFGOWNER."NOTIF_MASTER";

SQL SELECT "BILLING_PLAN_ID",

"YEAR_PERIOD"

FROM MFGOWNER."BILL_PLAN_LAST_INV";

//-------- End Multiple Select Statements ------

Re: Oracle Loading and Parenthesis Problems

Anonymous — Mon, 10 Oct 2016 11:46:56 GMT

Put that line as a preceding load above the load

Load

IF (LEFT("LOC_ID_NUMBER",2) = '1.','DT','FM') AS "DATABASE",

;

SQL SELECT "ANT_PART_NO",

"ANT_PART_NO2",

"ANT_RELEASE_LEVEL",

"ANT_RELEASE_LEVEL2",

.... the rest of your sql here ...............

It is a Qlik statement and not SQL so will fail if placed as part of the SQL statement.

Re: Oracle Loading and Parenthesis Problems

maxgro — Mon, 10 Oct 2016 11:55:12 GMT

The whole script works fine until I add the line

IF (LEFT("LOC_ID_NUMBER",2) = '1.','DT','FM') AS "DATABASE",

Oracle? try to replace with

CASE SUBSTR(LOC_ID_NUMBER, 1, 2)

WHEN '1.' THEN 'DT'

ELSE 'FM'

END AS "DATABASE"

Re: Oracle Loading and Parenthesis Problems

Wed, 12 Oct 2016 09:57:20 GMT

Bill

You are a gentleman and a scholar.

Thanks. It worked perfectly.

I thought that you had to load it as part of the full script. I did not realise that you could do it as a separate load statement.

Regards

Re: Oracle Loading and Parenthesis Problems

Wed, 12 Oct 2016 09:58:17 GMT

Thank you for the reply. Much appreciated.

Re: Oracle Loading and Parenthesis Problems

Anonymous — Thu, 13 Oct 2016 00:21:01 GMT

The Preceding Load is great and I use it a lot, have a look at his blog post by Henric Preceding Load

It is especially good in this scenario with a select from a database source. Databases usually have significant latency, especially if held on spinning rust [aka hard discs]. The Preceding Load pipes the data record by record from the high latency data source into RAM to then be transformed by Qlik, so can make things faster as it can transform whilst waiting for more data. It also reduces CPU load on the database server which would otherwise reduce production database performance.