Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Not applicable
‎2016-10-07 04:36 AM

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

2,531 Views
0 Likes
17 Replies
Anil_Babu_Samineni
MVP
MVP
‎2016-10-08 09:07 AM

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
509 Views
0 Likes
Not applicable
‎2016-10-10 04:40 AM
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.

509 Views
0 Likes
Not applicable
‎2016-10-10 04:41 AM
Author

In response to Anonymous

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

509 Views
0 Likes
Anonymous
Not applicable
‎2016-10-10 07:46 AM
Author

In response to

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.

2,318 Views
maxgro
MVP
MVP
‎2016-10-10 07:55 AM

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"

509 Views
0 Likes
Not applicable
‎2016-10-12 05:57 AM
Author

In response to Anonymous

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

509 Views
0 Likes
Not applicable
‎2016-10-12 05:58 AM
Author

In response to maxgro

Thank you for the reply. Much appreciated.

509 Views
0 Likes
Anonymous
Not applicable
‎2016-10-12 08:21 PM
Author

In response to

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.

509 Views