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
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
Hi Govind,
Try this
=Num([DISTRIBUTED_DT])
May be this:
Num#(Date(DISTRIBUTED_DT, 'YYYYMMDD')) as YEARMONTHDATE
Select "DEBIT_LINE_ITEM_SUM", | |
"CREDIT_LINE_ITEM_SUM", | |
"ACTUAL_EXPENSE", | |
"ACCRUAL_EXPENSE", | |
"GRAND_TOTAL_EXPENSE", | |
"ACCRUAL_IND", | |
NUM#(date("DISTRIBUTED_DT",'YYYYMMDD')) AS "DATE_KEY", |
"DISTRIBUTED_DT",
"APPROVED_DT" |
FROM "EXP_F_EXPENSE";
Showing Error. I given like above.Please check
The following error occurred:
SQL##f - SqlState: S1000, ErrorCode: 936, ErrorMsg: [Oracle][ODBC][Ora]ORA-00936: missing expression
The operation won't work in SQL Select statement. This will need to be carried out in a Load statement....
Load ....
TransformationHere;
Select
....
From ....
Please give me steps.
My table
[EXP_F_EXPENSE]:
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_SK",
"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#(date("DISTRIBUTED_DT",'YYYYMMDD')) AS DATE_KEY,
"DISTRIBUTED_DT" AS DATE_KEY,
"APPROVED_DT"
FROM "MICROS_APP_TEAM"."EXP_F_EXPENSE";
Try like this:
[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_SK",
"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",
"DISTRIBUTED_DT",
"APPROVED_DT"
FROM "MICROS_APP_TEAM"."EXP_F_EXPENSE";
Try this way also
Date#( Date ( "DISTRIBUTED_DT" ,'YYYYMMDD' ) ,'YYYYMMDD')
Or
NUM( Date#( Date ( "DISTRIBUTED_DT" ,'YYYYMMDD' ) ,'YYYYMMDD') )
Regards
Anand
The following error occurred:
Field not found - <DISTRIBUTED_DT>