How do we see the records in the invoice_attachment table? I am getting “Query returning data types BLOB/CLOB is not supported.” I just want to see the company and invoice_id for a specific company.
A previous post mentioned excluding the BLOB/CLOB fields, but not how do to that. Can someone give me a specific example for excluding those types of fields?
Best answer by NiyomalN
if you want to see the invoices with attachment then change the NOT IN to IN.
select I. company, i.INVOICE_ID
from INVOICE_LEDGER_ITEM_SU_QRY i
where i.LEDGER_ITEM_ID not in
(select SUBSTR((SUBSTR(t.key_ref,
INSTR(t.key_ref, 'LEDGER_ITEM_ID=') + 15)),
1,
INSTR((SUBSTR(t.key_ref,
INSTR(t.key_ref, 'LEDGER_ITEM_ID=') + 15)),
'^') - 1) as Invoice_id
from doc_reference_object t
where t.lu_name = 'InvoiceLedgerItem')
+10Hi
if you only need to see company and invoice id, you can use the below query. to exclude any unwanted field, you just need to specify the fields that need be in the result set rather than going with select * from XXX.
select t.company, t.invoice_id from invoice_attachment t
Kind Regard!
Niyomal.
I am trying to find which supplier invoices do NOT have attachments. IS the invoice_attachment the correct table?
+10I tried that select statement but am still getting an error.
By looking at the screenshot seems like it is executing something else.
can you restart the application and if the query works ? i tried the same using one of our internal environment and it works for me.
Kind Regards!
Niyomal.
+10Also, who do I tell which fields are BLOB/CLOB?
I don't think that we can see the data type from the sql query tool.
the easiest way is to use the pl/sql developer in this case.
Kind regards!
Niyomal.
Reopening the application does show me the fields, but no data. Now I wonder if that is the right field.
We are trying to identify which invoices do not have attachments. In the screen shot the highlighted one DOES not have anything attached. Other then clicking the paperclip on every single one, who can get a list of those invoices without attachments?
+10if you want to see the invoices with attachment then change the NOT IN to IN.
select I. company, i.INVOICE_ID
from INVOICE_LEDGER_ITEM_SU_QRY i
where i.LEDGER_ITEM_ID not in
(select SUBSTR((SUBSTR(t.key_ref,
INSTR(t.key_ref, 'LEDGER_ITEM_ID=') + 15)),
1,
INSTR((SUBSTR(t.key_ref,
INSTR(t.key_ref, 'LEDGER_ITEM_ID=') + 15)),
'^') - 1) as Invoice_id
from doc_reference_object t
where t.lu_name = 'InvoiceLedgerItem')
Thank you so much, that is exactly what I was looking for and I would not have found the correct table.
Sincerely,
Deb Jensen
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
