CODEWITHSUNDEEP
oracle-apex
Abdullah
Abdullah

Reputation: 1033

Why LISTAGG function not working as expected in oracle apex?

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

INVALID NUMBER ERROR SHOW

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

in SQL working

Upvotes: -1

Views: 49

Answers (2)

Abdullah
Abdullah

Reputation: 1033

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.

LISTAGG working

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143053

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

enter image description here

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.com, create sample IR page and provide login developer's credentials so that we could take a look.

Upvotes: 2

Related Questions