Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor
Contributor

Qlik sense Unable to read Oracle Script

Hi Guru's,

Iam compiling oracle EBS Script in Qlik sense, its unable to read Composite key script in SQL.

How to get it work done,any help would be greately appreciated.

SELECT L.LEDGER_ID "LEDGER_KEY",

  TO_NUMBER(TO_CHAR(L.EFFECTIVE_DATE,'YYYYMMDD')) "JOURNAL_DT_KEY",

  TO_NUMBER(TO_CHAR(H.POSTED_DATE,'YYYYMMDD')) "POSTED_DT_KEY",

  L.STATUS "JOURNAL_LINE_STATUS",

  L.CODE_COMBINATION_ID "GL_ACCOUNT_KEY",

  B.NAME "JE_BATCH_NAME",

  L.JE_HEADER_ID "JE_HEADER_ID",

  L.JE_LINE_NUM "JE_LINE_NUM",

  L.JE_HEADER_ID

  ||'~'

  ||L.JE_LINE_NUM "JOURNAL_ID",

  H.NAME "JE_HEADER_DESCRIPTION",

  L.DESCRIPTION "JE_LINE_DESCRIPTION",

  L.CODE_COMBINATION_ID,

  H.JE_SOURCE "JE_SOURCE",

  H.JE_CATEGORY "JE_CATEGORY_NAME",

  L.REFERENCE_6 "JE_SOURCE_REFERENCE",

  H.CURRENCY_CODE "DOC_CURR_CODE",

  GL.CURRENCY_CODE "LOC_CURR_CODE" ,

  XDL.UNROUNDED_ENTERED_DR "ENTERED_DR",

  XDL.UNROUNDED_ENTERED_CR "ENTERED_CR",

  XDL.UNROUNDED_ACCOUNTED_DR "ACCOUNTED_DR",

  XDL.UNROUNDED_ACCOUNTED_CR "ACCOUNTED_CR",

  B.STATUS "BATCH_STATUS",

  L.CREATION_DATE "CREATION_DATE",

  L.LAST_UPDATE_DATE "LAST_UPDATE_DATE",

  L.JE_HEADER_ID

  ||'~'

  ||L.JE_LINE_NUM

  ||'~'

  ||L.CODE_COMBINATION_ID

  ||'~'

  ||XDL.EVENT_ID

  ||'~'

  ||XDL.AE_HEADER_ID

  ||'~'

  ||XDL.AE_LINE_NUM

  ||'~'

  ||xdl.source_distribution_id_num_1 "INTEGRATION_ID",

  --ai.invoice_num ap_invoice_id ,

  ai.invoice_num "invoice_num" ,

  ai.invoice_date "invoice_date",

  ail.DESCRIPTION "invoice_line_desc",

  V.VENDOR_NAME "SUP_CUST_NAME",

  PA.PROJECT_ID,

  PA.NAME "PROJECT_NAME",

  PA.SEGMENT1 "PROJECT_NUMBER",

  PA.PROJECT_TYPE,

  PA.DESCRIPTION "PROJECT_DESCRIPTION",

  PA.START_DATE "PROJECT_START_DATE",

  PA.COMPLETION_DATE "PROJECT_END_DATE",

  FU.USER_NAME "CREATED_BY"

FROM GL_JE_LINES L,

  GL_JE_HEADERS H,

  GL_JE_BATCHES B,

  GL_LEDGERS GL ,

  GL_IMPORT_REFERENCES gir,

  XLA.XLA_AE_LINES xlal ,

  XLA.XLA_AE_HEADERS xlah ,

  XLA.XLA_EVENTS xlae ,

  XLA.XLA_TRANSACTION_ENTITIES xlate ,

  xla.xla_distribution_links xdl,

  ap_invoice_distributions_all aid,

  AP_INVOICES_ALL ai,

  PO_VENDORS V,

  ap_invoice_lines_all ail,

  PA_PROJECTS_ALL PA,

  FND_USER FU

WHERE 1            =1

AND L.JE_HEADER_ID = H.JE_HEADER_ID

AND H.JE_BATCH_ID  = B.JE_BATCH_ID

AND GL.LEDGER_ID   = H.LEDGER_ID

  --AND B.STATUS                         = 'P'

AND L.LEDGER_ID                     IN (874,919,916,875,876,125,22)

AND b.je_batch_id                    = gir.je_batch_id

AND l.je_header_id                   = gir.je_header_id

AND l.je_line_num                    = gir.je_line_num

AND gir.gl_sl_link_table             = xlal.gl_sl_link_table

AND gir.gl_sl_link_id                = xlal.gl_sl_link_id

AND xlal.ae_header_id                = xlah.ae_header_id

AND xlal.application_id              = xlah.application_id

AND xlah.event_id                    = xlae.event_id

AND xlah.application_id              = xlae.application_id

AND xlae.entity_id                   = xlate.entity_id

AND xlae.application_id              = xlate.application_id

AND h.je_source                      = 'Payables'

AND xlate.source_id_int_1            = ai.invoice_id

AND xlate.security_id_int_1          = ai.org_id

AND xlate.entity_code                = 'AP_INVOICES'

AND V.VENDOR_ID                      = xlal.party_id

AND xdl.ae_header_id                 = xlah.ae_header_id

AND xdl.ae_line_num                  = xlal.ae_line_num

AND (xlae.EVENT_ID                   =xdl.EVENT_ID

AND xlae.APPLICATION_ID              =xdl.APPLICATION_ID)

AND xdl.source_distribution_id_num_1 = aid.invoice_distribution_id

AND aid.invoice_id                   = ai.invoice_id

AND ail.invoice_id                   = ai.invoice_id

AND ail.line_number                  = aid.invoice_line_number

AND AID.PROJECT_ID                   = PA.PROJECT_ID(+)

AND L."CREATED_BY"                   = FU.USER_ID

AND SUBSTR(h.POSTED_DATE,8,9) BETWEEN 05 AND 08

AND SUBSTR(l.effective_date,8,9) BETWEEN 05 AND 08

Thanks,

joshi

5 Replies
MVP
MVP

What do you mean by "unable to read" - do you get a specific error message and could you share it with us?

Do you get this problem in the Data Manager og Data Load Editor?

Creator III
Creator III

Hi Joshi,

can you elaborate the Error?

Regards

Sathish

Contributor
Contributor

Iam getting below error in data load Editor

QVX_UNEXPECTED_END_OF_DATA: ERROR [22018] [Qlik][ODBC Oracle Wire Protocol driver][Oracle]ORA-01722: invalid number.

Sql Developer Its running without error But in Qlik sense its throwing error.

May be its unable to read below code

L.JE_HEADER_ID

  ||'~'

  ||L.JE_LINE_NUM

  ||'~'

  ||L.CODE_COMBINATION_ID

  ||'~'

  ||XDL.EVENT_ID

  ||'~'

  ||XDL.AE_HEADER_ID

  ||'~'

  ||XDL.AE_LINE_NUM

  ||'~'

Thanks,

Joshi

MVP
MVP

Maybe you have to do an explicit CAST of the numeric column before trying to concatenate it.

I am not sure if Oracle will do an implicit type conversion from numeric to char ...?

Creator III
Creator III

Hi Joshi,

oracle not able to convert string to int. better to use resident and convert to int.

Regards

Sathish