Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
How can i convert Date values into number format.
I used below fn but no luck. How can i convert.
NUM(date("DISTRIBUTED_DT",'YYYYMMDD'))
MY number should be YEARMONTHDATE e.g 20160817
Above 2 condition giving same error
The following error occurred:
SQL##f - SqlState: S1000, ErrorCode: 936, ErrorMsg: [Oracle][ODBC][Ora]ORA-00936: missing expression
Are you renaming the Distributed_DT to something else in your SELECT statement using as?
[EXP_F_EXPENSE]:
LOAD*,
Num#(Date("DISTRIBUTED_DT",'YYYYMMDD')) AS DATE_KEY;
SELECT "RECORD_DATE_KEY",
"COMPANY_KEY",
"ACFT_KEY",
"AIRCRAFT_UNIT_KEY",
"GL_ACCOUNT_KEY",
"INV_KEY",
"INV_LINE_ITEM_KEY",
"FLEET_KEY",
"LOCATION_KEY",
"FACT_KEY",
"ORD_LINE_ITEM_KEY",
"SERVICE_LOCATION",
"SUPPLIER_LOCATION",
"ORD_KEY",
"SUP_ITEM_KEY",
"ATA_KEY",
"SUP_KEY",
"EXP_CLASS_CD",
"EXP_DIVISION_CD",
"EXP_COST_ROLLUP_CD",
"EXP_COST_ACCOUNT_CD",
"EXP_COST_SUBCATEGORY_CD",
"INVOICED_RATE",
"INVOICED_QUANTITY",
"INVOICE_LINE_TOTAL",
"DISPUTED_AMOUNT",
"CLAIM_LINE_ITEM_SUM",
"DEBIT_LINE_ITEM_SUM",
"CREDIT_LINE_ITEM_SUM",
"ACTUAL_EXPENSE",
"ACCRUAL_EXPENSE",
"GRAND_TOTAL_EXPENSE",
"ACCRUAL_IND",
//Num("DISTRIBUTED_DT") AS DATE_KEY,
"APPROVED_DT"
FROM "MICROS_APP_TEAM"."EXP_F_EXPENSE";
The following error occurred:
Field not found - <DISTRIBUTED_DT>
My friend, why are you not adding the DISTRIBUTED_DT in your SELECT statement? Unless you do, you will continue to get this error
[EXP_F_EXPENSE]:
LOAD*,
Num#(Date("DISTRIBUTED_DT",'YYYYMMDD')) AS DATE_KEY;
SELECT "RECORD_DATE_KEY",
"COMPANY_KEY",
"ACFT_KEY",
"AIRCRAFT_UNIT_KEY",
"GL_ACCOUNT_KEY",
"INV_KEY",
"INV_LINE_ITEM_KEY",
"FLEET_KEY",
"LOCATION_KEY",
"FACT_KEY",
"ORD_LINE_ITEM_KEY",
"SERVICE_LOCATION",
"SUPPLIER_LOCATION",
"ORD_KEY",
"SUP_ITEM_KEY",
"ATA_KEY",
"SUP_KEY",
"EXP_CLASS_CD",
"EXP_DIVISION_CD",
"EXP_COST_ROLLUP_CD",
"EXP_COST_ACCOUNT_CD",
"EXP_COST_SUBCATEGORY_CD",
"INVOICED_RATE",
"INVOICED_QUANTITY",
"INVOICE_LINE_TOTAL",
"DISPUTED_AMOUNT",
"CLAIM_LINE_ITEM_SUM",
"DEBIT_LINE_ITEM_SUM",
"CREDIT_LINE_ITEM_SUM",
"ACTUAL_EXPENSE",
"ACCRUAL_EXPENSE",
"GRAND_TOTAL_EXPENSE",
"ACCRUAL_IND",
//Num("DISTRIBUTED_DT") AS DATE_KEY,
DISTRIBUTED_DT,
"APPROVED_DT"
FROM "MICROS_APP_TEAM"."EXP_F_EXPENSE";
Please add the field in red above, else you will continue to get the error
You have to put this condition on he above of the
Ex:-
LOAD
Order,
Item,
Date#( Date ( "DISTRIBUTED_DT" ,'YYYYMMDD' ) ,'YYYYMMDD') as DateField;
SQL SELECT *
FROM <TableName>
Regards
Anand
your correct.I will check result let you know.thanks a lot.
Yay!!! Sounds good