CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Hey StackExchange
Hey StackExchange

Reputation: 2125

Oracle 10g: How insert conditionally rows from table A to B based

Got a table A like this

|----------|----------|
|  DT      |  FLAG    |
|----------|----------|
| 2015-MAY |  E       |
| 2015-JUN |  H       |
| 2015-OCT |  S       |
| 2016-FEB |  E       |
|----------|----------|

I want to insert in B the rows for which the flag aggregated data of A match a given pattern.

This query do the aggregation below:

     SELECT
            (lag(rep.flag) over (ORDER BY rep.DECLARATIONPERIOD) || flag) AS ERRCODE,
            (lag(rep.DECLARATIONPERIOD) over (ORDER BY rep.DECLARATIONPERIOD)) AS DECFROM, DECLARATIONPERIOD AS DECTO
    FROM  XE_ERR_OVLP rep;

Result (table A):

|----------|---------------|---------------|
|  FLAG    |  DTFROM       |  DTFTO        |
|----------|---------------|---------------|
|  VV      |    2014-02-01 |    2014-03-01 |
|  VE      |    2014-03-01 |    2014-04-01 |
|  EE      |    2014-04-01 |    2014-05-01 |
|  EV      |    2014-05-01 |    2014-06-01 |
|  VV      |    2014-06-01 |    2014-07-01 |
|  VS      |    2014-07-01 |    2014-08-31 |
|----------|---------------|---------------|

See demo

I would like to populate this table (B) from the table (A), for the value of FLAG that match certain value only (eg EE and HH):

|----------|---------------|---------------|
|  FLAG    |  DTFROM       |  DTFTO        |
|----------|---------------|---------------|
|  ...     | ...           | ...           |
|----------|---------------| --------------|

If possible on the fly (while aggregating).

Any suggestion to get it right welcome

Upvotes: 0

Views: 53

Answers (2)

xQbert
xQbert

Reputation: 35333

It's an order of operation issue: Note we materialize the results of the analytic using a derived table before we filter on it. The SELECT doesn't execute until the where clause has been executed so the ERRCODE alias is unknown to the where clause.

SQL order of operations can be found online, I'm not going to restate; but in this case the below are the important elements/order. You'll note the link specifically states your issue: Window functions ... are not available to the WHERE clause, which happened before the evaluation of the window function.

  1. FROM
  2. WHERE
  3. SELECT

This means the WHERE Clause doesn't know about ERRCODE yet; so it can't limit by it. This is generally solved by using a derived table/inline view or a common table expression.

Here's the inline view/derived table:

   INSERT INTO XE_ERR_RANG
      SELECT Z.* 
      FROM (SELECT EMPLOYERID
                 , EMPLOYEEID
                 , lag(DECLARATIONPERIOD) over (ORDER BY DECLARATIONPERIOD) AS DECFROM
                 , DECLARATIONPERIOD AS DECTO
                 , lag(flag) over (ORDER BY DECLARATIONPERIOD) || flag AS ERRCODE
            FROM XE_ERR_OVLP) Z
      WHERE Z.ERRCODE in ('HS', 'SE');

Alternative approach using a common table expression (CTE):

Untested: but you said oracle 10g appears to support CTE; but not recursion; so should work if it supports the insert syntax before the CTE...

INSERT INTO XE_ERR_RANG

WITH Z as (SELECT EMPLOYERID
                , EMPLOYEEID
                , lag(DECLARATIONPERIOD) over (ORDER BY DECLARATIONPERIOD) AS DECFROM
                , DECLARATIONPERIOD AS DECTO
                , lag(flag) over (ORDER BY DECLARATIONPERIOD) || flag AS ERRCODE
           FROM XE_ERR_OVLP)

SELECT * 
FROM Z
WHERE ERRCODE in ('HS', 'SE');

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269933

This does what you describe:

insert into b (dt, flag)
    select dt, flag
    from a
    where flag in ('E', 'H');

I'm not sure how it relates to the queries, which are considerably more complicated than your sample data and question.

Upvotes: 1

Related Questions