SQL Query in Apex if date not exist return value from previous date

Question

I need to generate interactive report in Oracle Apex by comparing 2 tables. Each table has Columns: Date, Tag, Number.

Table 1: contains History of the changes of Column: Number and writes on which date the column: Number was changed.

Table 2 : Also has column: Number and column: Date (column: Date which shows different values for column: Number for the different dates of the month).

The Report is being generated when comparing columns "Tag_id" between table 1 and table 2 so we know which value from column: number in table 1 is referring to column: number in table 2. (...where table1.tag=table2.tag).

Table 2 contains date in column: number for every day of the month, and Table 1 contains date that is changed in column: number once every 1,2,3 or more days.

I want the report to show values from column: Number from Table 1 and to show the values from column: Number from Table 2 while making comparison between table 1 and table 2 by column: Date.

The problem is when in Table 1 there is no date in column:Date which is equal to date in column:Date in Table 2, the Database doesnt know which value for column:Number to return in the Generated Report.

What I want to achieve: When there is no date match in the columns:Date between Table 1 and Table 2 I want in the Generated report in Apex to return the value from column:Number from Table 2 for the Date for which there is no match* and the value of column:Number from Table 1 for the closest previous date by using only SQL Query.

in your example, the first line in the report result, the value vor numberT1, shouldn't that be 300, because 4/15/2016 is *closer* to 4/14/2016 than 4/11/2016? — Tudor Constantin, Apr 26 '16 at 06:56
Yes but i want closest previous existing in Table1 date, not closest next date. — Kristian Aleksiev, Apr 26 '16 at 07:08
I imagined that - that's what my answer should solve. I'm curious if it works :) — Tudor Constantin, Apr 26 '16 at 07:09

score 0 · Answer 1 · answered Apr 26 '16 at 07:07

0

Interesting question. I don't know about Apex, but if it were Oracle's MySQL, I'd go along the way of:

SELECT 
   t2.id, 
   t2.date,
   CASE WHEN t1.date IS NULL 
      THEN (SELECT table1.number FROM table1 WHERE table1.data <= t2.data ORDER BY table1.data DESC LIMIT 1) 
      ELSE t1.number 
   END AS numberT1,
   t2.number as numberT2
FROM table2 t2
LEFT JOIN table1 t1 ON t1.date = t2.date

answered Apr 26 '16 at 07:07

Tudor Constantin

26,330
7
49
72

"apex" is no database, it's a dev tool (just clearing that up) and runs on an Oracle db (not mysql). Having said that, your syntax will work _almost_. The `LIMIT` keyword isn't available to use, so OP would've to use `ROWNUM` to limit the result -> http://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering – Tom Apr 26 '16 at 07:57

SQL Query in Apex if date not exist return value from previous date

1 Answers1