I need help with putting a condition on a List Of Values in Oracle apex. So I have 2 tables: and The list of values displays v.BROJ_VEZ but returns v.ID_VEZ. The LOV is displayed in the form for the table BRODICE. I want the LOV to display only the v.BROJ_VEZ (returning v.ID_VEZ) where v.MAX_DULJINA > b.DU…
Tag: oracle
Oracle SQL to delete the records except last 7 days/1 week
I am using Oracle SQL DB for ERP JD Edwards. Dates are stored in Julian Format for this ERP. We usually use this code to convert date from Julian to normal format. decode(szupmj,0,’ ‘,to_char(to_date(1900000 + olupmj,’YYYYDDD’),’MM/DD/YYYY’)) However we have a requirement w…
Need oracle query that merges two rows with user ids into one row with multiple user columns
I have data in a large table that looks like this: case_nbr event_id event_desc User_ID 201 1001 Start Discussion 201 1002 Push Agent U67800 201 1003 Accept U67800 201 1004 Transfer U67800 201 1005 Push Agent U67999 201 1006 Accept U67999 201 1007 Transfer U67999 201 1008 Push Agent U11800 201 1009 Accept U11…
How to create trigger that will insert into table old and new value on update/insert
I have looked a lot through the internet the past 3 hours but I cannot manage to make the trigger work. Here is my setup: There is a user USER1 that has granted UPDATE on view_faktura only. Making UPDATE statement on the view will change the underlying faktura table data. What I want to do is log into another…
How to create an ID column based on condition of previous row?
I am trying to group hospital admission transfers, with the initial index admission. So far I have been able to categorize each admission as ‘index’ or ‘transfer’ based on the time since previous discharge. Now I am trying to group together the admissions together that are in the same …
SQL: Search two strings in the same column through subquery
I have three tables using an Oracle database: The mix table interconnects Recipe and Ingredients. I am supposed to find the one cocktail made by the barman Otto which utilizes ingredients from Spain and Cuba. This is what I have tried until now: Expected Output from this query: At the moment, I am just gettin…
Why does (SHAPE).SDO_ORDINATES(1) syntax fail, but (SHAPE).ST_PointN(1) succeeds?
Oracle 18c: In a related question, we determined: The syntax of extracting collection elements by index is not supported in SQL. So a query that uses this syntax will fail: (shape).sdo_ordinates(1), Source: Why does SHAPE.SDO_ORDINATES(1) work in PL/SQL, but not in SQL? However, I have a query that is similar…
How to optimize the performance of the following SQL query?
For the following query , I tried creating index on table tmp.req_index_cont_t with columns indcont_key_1 and ind_no but still optimizer is performing full table scan , I am not getting how to resolve this issue , please guide : Answer There’s not enough information here to answer your question. Why do …
Can you help me to correct this query in sql
ERROR at line 1: ORA-00979: not a GROUP BY expression Answer You are getting this error for WEIGHT_IN_PERCENTAGE. If WEIGHT_IN_PERCENTAGE is same for every row in a group you can use (SUM(POINTS) * max(WEIGHT_IN_PERCENTAGE)) or you can SUM(POINTS*WEIGHT_IN_PERCENTAGE) multiply it with POINTS before sum: OR
Returning multiple XML from ref cursor oracle
There is a procedure in the Oracle DB which takes a date range and generates XML file for each day and the return type is ref cursor. When the procedure is called from C# code, I am unable to extract the XML from the cursor. Any pointers will be helpful on how to extract the XML Below given is the