Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Oracle Loading and Parenthesis Problems

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",

17 Replies
Anil_Babu_Samineni

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;

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Not applicable
Author

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.

Not applicable
Author

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

Anonymous
Not applicable
Author

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.

maxgro
MVP
MVP

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"

Not applicable
Author

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

Not applicable
Author

Thank you for the reply. Much appreciated.

Anonymous
Not applicable
Author

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.