Forum Stats

  • 3,752,633 Users
  • 2,250,529 Discussions
  • 7,867,903 Comments

Discussions

Replacing 0 with a associated value

User_WI23P
User_WI23P Member Posts: 219 Red Ribbon
edited Jul 14, 2021 8:56PM in SQL & PL/SQL

Hello All,

I have sample data as below and it's present in order in the sample table (let us not worry how it's ordered) and there can be some rows in last with 0 value i.e. second last or maybe third last and so on .. and the ultimate last rows with NULL data and some value. The table sample_value table also holds a unique value across rows for each category stored in the sample table. The sample table rows are the multiple of the category value of the sample value table in order. Now, the requirement is to replace the 0 value in the sample table with category value i.e. 0 by 7 when the sample last row value is greater than the sample data category value.

CREATE TABLE sample  AS
    ( SELECT 'A' AS data
        , 1  AS category
        , 7  AS value
        , 'Y' AS flag
    FROM dual UNION ALL
    SELECT 'B' AS data
       , 1  AS category
       , 14  AS value
       , 'Y' AS flag
    FROM dual UNION ALL
    SELECT 'C' AS data
       , 1  AS category
       , 7  AS value
       , 'Y' AS flag
    FROM dual UNION ALL
    SELECT 'D' AS data
       , 1  AS category
       , 0  AS value
       , 'Y' AS flag
    FROM dual UNION ALL
    SELECT NULL AS data
       , 1  AS category
       , 8  AS value
       , 'N' AS flag
    FROM dual   );

CREATE TABLE sample_value  AS
    ( SELECT 1 AS category
        , 7 AS category_value
    FROM dual    );
«13

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,926 Red Diamond
    edited Jul 14, 2021 8:38PM

    Hi, @User_WI23P

    Thanks for posting the sample data. Don't forget to post the exact results you want from that sample data, and your Oracle version number.

    (let us not worry how it's ordered) 

    If we're not worried about how it's ordered, then terms like "last" and "second to last" have no meaning, because any row could be the last row, or second to last. If order is important, then say how the rows are ordered. If necessary, add another column that specifies the order.

    However the rows are ordered, say how you want to break ties (if ties are possible).

    It looks like category = 1 on all rows. Does category play any role in this problem? If so, wouldn't it make sense to post some sample data and results that had a couple of different values for category?

  • User_WI23P
    User_WI23P Member Posts: 219 Red Ribbon
    edited Jul 14, 2021 9:07PM

    @Frank Kulash - The rows are coming from mainframe software and inserted into the table in order. With the order, I mean to say - First order is A, B, C, D, NULL, NULL. Also did edit in sample data and included flag column.

    A, 1, Some quantity

    B, 1, Some quantity

    C, 1, Some quantity

    NULL, 1, 0 quantity, the flag is Y for the 0 quantity rows.

    NULL, 1, Some quantity (0 or greater or less than category value of table sample value), the flag is always N for the last row.

    Expected result -


    Data   VALUE

    - ----------

    A     7

    B     14

    C     7

    D     21

    E     8

    NULL 1

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,926 Red Diamond

    Hi,

     Also did edit in sample data 

    Please don't change your messages after you post them; it makes the thread hard to read and your changes easy to miss. Post all additions and corrections in a new reply, at the end of the thread.

    First order is A, B, C, D, NULL, NULL.

    What does "first order" mean? Is there a second or a third order, too? Are ties possible (e.g., the two rows with NULL in the example above)? If so, how do you break ties?

    Once again, what are the exact results you want from the given sample data? If I don't know where you want to go, I can't give you very good directions.

  • User_WI23P
    User_WI23P Member Posts: 219 Red Ribbon

    There will not be any ties in the sample table data. Also, posted sample data in previous post.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,926 Red Diamond

    Hi, @User_WI23P

    Once again, you edited a message after posting it. No kidding, it's really confusing when you do that. Please make all changes and corrections in a new reply, at the end of the thread.

    Expected result -


    Data   VALUE

    - ----------

    A     7

    B     14

    C     7

    D     21

    E     8

    NULL 1

    So, you want 6 rows in the the result set, even though there are only 5 rows in the table. Explain how you determine the number of rows, and how you get the values (like data = 'E') on the new rows.

  • Paulzip
    Paulzip Member Posts: 8,423 Blue Diamond

    You talk about inserting "in order" and last row, but that just highlights to me that you may not understand how databases and sets work. There is no order in a DB until you define one, via an "order by" or such, so if your mainframe inserts those records, I have no guarantee a query will pull them back in the same order. Couple that with multiple nulls on what I suspect to be the column you want to order on, then we still have issues.

    I'm still none the wiser as what you actually want, as it appears Frank is, and we've solved many many thousands of complex problems on this forum. So that also suggests to me you haven't been able to describe (what appears to be a simple requirement), concisely or unambiguously. If you can't describe your problem properly, and you know your problem domain, how do you expect us - who know nothing about it - to solve it?

    Frank KulashBluShadow
  • User_WI23P
    User_WI23P Member Posts: 219 Red Ribbon
    edited Jul 15, 2021 1:36PM

    @Frank Kulash and @Paulzip -

    Ok, let explain again - We are not worried about the order because there is some process that takes care of it before inserting it in our table but it is guaranteed that rows are in order of column named "data" for every column "category" with column "flag" as "Y". The last row can have NULL as data for any category with some quantity, now this quantity can be greater than or less than the column "category value". There can also be rows with quantity 0 for data and categories before the last row with flag N row e.g. 2nd last or 3rd last.

    My requirement is to pick quantity from the last row, check if there's any 0 quantity row, if yes check if the last row is greater than "category value", if no - don't do anything but if yes change 0 with "category value" for that category subtracting it from the last row.

    CREATE TABLE sample  AS
        ( SELECT 'A'  AS data
         , 1    AS category
         , 7    AS value
         , 'Y'  AS flag
    FROM dual
    UNION ALL
    SELECT 'B'  AS data
         , 1    AS category
         , 14   AS value
         , 'Y'  AS flag
    FROM dual
    UNION ALL
    SELECT 'C'  AS data
         , 1    AS category
         , 7    AS value
         , 'Y'  AS flag
    FROM dual
    UNION ALL
    SELECT 'D'  AS data
         , 1    AS category
         , 0    AS value
         , 'Y'  AS flag
    FROM dual
    UNION ALL
    SELECT NULL  AS data
         , 1     AS category
         , 8     AS value
         , 'N'   AS flag
    FROM dual  );
    
    CREATE TABLE sample_value  AS
        ( SELECT 1 AS category
            , 7 AS category_value
        FROM dual    );
    

    Desired result -


    D  CATEGORY   VALUE FLAG

    - ---------- ---------- -

    A     1     7 Y

    B     1     14 Y

    C     1     7 Y

    D     1     7 Y

          1     1 N

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,926 Red Diamond
    edited Jul 15, 2021 2:21PM

    Hi, @User_WI23P

    Ok, let explain again - We are not worried about the order

    Ok, let me explain again - If you are not worried about the order, then it doesn't make sense to talk about the "last" row. "Last" is just a short way of saying "last in order by ...", when the order is understood.

    rows are in order of column named "data" for every column "category" with column "flag" as "Y".  The last row can have NULL as data

    Now you seem to be saying that you are concerned about the order: in particular, rows with flag='Y' are in order by data (NULLS last). What about rows where flag is not 'Y'? How do they compare to each other, or to the rows where flag='Y'?

    D  CATEGORY   VALUE FLAG

    - ---------- ---------- -

    A     1     7 Y

    B     1     14 Y

    C     1     7 Y

    D     1     7 Y

          1     1 N

    You explained that when when value=0 in the table, you may want to display a different number (7 in this example) as value in the output. You haven't explained why, when value <>0 you may want to display a different number. In this case, when value=8 in the table, why do you want to display value=1 in the output?

  • User_WI23P
    User_WI23P Member Posts: 219 Red Ribbon

    @Frank Kulash - I meant that rows are inserted in order but I think it's not relevant based on your comments because anyhow when writing queries we need them in the order A, B, C, D. Flag column N is the row from where we want to pull quantity and give it to the 0 rows so I think we should worry about only 0 rows and N flag row. I hope it's clear to you now.

  • EdStevens
    EdStevens Member Posts: 28,409 Gold Crown

    We are not worried about the order because there is some process that takes care of it before inserting it in our table

    No. Once those rows are inserted into the table, the "order" is lost. In a relational database, tables are unordered. Rows in a table are like balls in a basket. The balls may be dropped into the basked in a certain order, but once they hit the basket, that order becomes meaningless, null, and void.

    In relational database theory, by definition, data in a table is unordered. There is no concept of 'first' or 'last' until you apply an ORDER BY clause on your SELECT statement.

    You can prattle on all you want about how they are inserted in order, but until you grasp that relational data is un-ordered, your "requirement" will continue to make no sense.

    Frank Kulash