Why LISTAGG function not working as expected in oracle apex? - Stack Overflow

admin管理员组

文章数量:1324849

I built SQL query using LISTAGG its working in SQL as expected this is the QUERY :

select LISTAGG(distinct sd.TEST_NO,','),
       to_number(sd.sample_id)   
  from lab_sample_header a , lab_tests b  , lab_sample_details sd , lab_orders o
  where sd.test_no = b.test_no
  and a.order_id = o.order_id
  and sd.order_id = o.order_id
  and a.order_id = sd.order_id 
  and a.sample_id = sd.sample_id
  and a.order_id = 2025000047
  and a.collected_by is not null
  and a.sample_status=2
  group by to_number(sd.sample_id) 
  

when I run the QUERY in SQL its working but when run from apex its not working and show error

ORA-01722: invalid number

sample_id datatype is varchar2 I used TO_NUMBER but it still show the error

but in SQL working without TO_NUMBER see the image please FIRST ROW 3 TESTS :

I built SQL query using LISTAGG its working in SQL as expected this is the QUERY :

select LISTAGG(distinct sd.TEST_NO,','),
       to_number(sd.sample_id)   
  from lab_sample_header a , lab_tests b  , lab_sample_details sd , lab_orders o
  where sd.test_no = b.test_no
  and a.order_id = o.order_id
  and sd.order_id = o.order_id
  and a.order_id = sd.order_id 
  and a.sample_id = sd.sample_id
  and a.order_id = 2025000047
  and a.collected_by is not null
  and a.sample_status=2
  group by to_number(sd.sample_id) 
  

when I run the QUERY in SQL its working but when run from apex its not working and show error

ORA-01722: invalid number

sample_id datatype is varchar2 I used TO_NUMBER but it still show the error

but in SQL working without TO_NUMBER see the image please FIRST ROW 3 TESTS :

• oracle-apex

Share Improve this question Follow edited Jan 12 at 18:21 Abdullah asked Jan 12 at 6:23 AbdullahAbdullah 1,0331414 silver badges2929 bronze badges 5

• That query looks a bit weird. You say it works in sql but it does not in APEX. Did you run exactly the same query in sql and in APEX ? Or did you run the query in sql and then add the 2 columns in APEX for the report ? Since those columns reference sd.test_no and are not included in the group by the result will be 1 row per sd.test_no. Which is what you are seeing. – Koen Lostrie Commented Jan 12 at 14:22
• As a best practice, use the ANSI JOIN syntax over the old syntax. – Koen Lostrie Commented Jan 12 at 14:23
• @KoenLostrie yes I run the same query now its show error invalid number same query in SQL and APEX thank you – Abdullah Commented Jan 12 at 18:27
• Based on your image, sample ID contains letters, and will always error if you try to apply to_number(sample_id) – Scott Commented Jan 13 at 0:00
• @Scott in SQL its working without to_number , group by sample_id because its not working in apex I tried to use to_number but got same error – Abdullah Commented Jan 13 at 5:16

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

LISTAGG works OK, as far as I can tell. Demo, based on sample EMP table on apex.oracle:

As you can see, result is just the same in both SQL Workshop and Interactive report.

---

What might be wrong in your case? Can't tell; query looks OK, you said it works - just not in Apex. Did you run query in the same schema (database) as Interactive report?

I (we) can't reproduce it without your tables and data, so you must have done something wrong. If possible, create sample tables with data on apex.oracle, create sample IR page and provide login developer's credentials so that we could take a look.

I solved it as the following way :

1- I changed Interactive report region type to Interactive grid

2- I changed the QUERY as the following added test name with in LISTAGG also because I am showing the name of test not test number:

select LISTAGG(distinct sd.TEST_NO, ',') within group (order by sd.TEST_NO) as test_no,
         LISTAGG(distinct b.TEST_NAME_ENG, ',') within group (order by b.TEST_NAME_ENG) as test_names,
       a.patient_no,
       a.order_id,
       sd.sample_id,
       a.SECTION_ID,
       b.sample_type,
       b.test_container,
       b.test_volume,
       decode(o.ORDER_PRIORITY, 1, 'URGENT', 2, 'ROUTINE') as order_priority
     from lab_sample_header a,
       lab_tests b,
       lab_sample_details sd,
       lab_orders o
 where sd.test_no = b.test_no
   and a.order_id = o.order_id
   and sd.order_id = o.order_id
   and a.order_id = sd.order_id
   and a.sample_id = sd.sample_id
   and a.collected_by is not null
   -- Filtering parameters
   and ((:P60_SECTION is not null and a.section_id = :P60_SECTION) or :P60_SECTION is null)
   and ((:P60_MRN is not null and a.patient_no = :P60_MRN) or :P60_MRN is null)
   and ((:P60_ORDER is not null and a.order_id = :P60_ORDER) or :P60_ORDER is null)
   -- Ensure at least one parameter is selected
   and (:P60_SECTION is not null or :P60_MRN is not null or :P60_ORDER is not null)
   and a.sample_status = 2
   and a.received_by is null
 group by a.patient_no,
          a.order_id,
          sd.sample_id,
          a.SECTION_ID,
          b.sample_type,
          b.test_container,
          b.test_volume,
        --   b.TEST_NAME_ENG,
          decode(o.ORDER_PRIORITY, 1, 'URGENT', 2, 'ROUTINE')

3- I used interactive grid checkbox functionality and its available by default in interactive grid and I suggest always to use Interactive grid if there is Transactions and the column for checkbox is APEX$ROW_SELECTOR

and this is the output now its working LISTAGG I hope this information will help others.

本文标签： Why LISTAGG function not working as expected in oracle apexStack Overflow