Skip to main content

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?

Hi @dkjensen ,

 

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 tried that select statement but am still getting an error.

Also, who do I tell which fields are BLOB/CLOB?

I am trying to find which supplier invoices do NOT have attachments.  IS the invoice_attachment the correct table?  

 

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

 

Also, 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?

@dkjensen , Try this query. This will return the invoices without attachments.

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

Thank you so much, that is exactly what I was looking for and I would not have found the correct table.

 

Sincerely, 

Deb Jensen

Reply


Terms & ConditionsCookie settings