Convert excel formula to Oracle SQL

Question

How to convert excel formula to oracle sql. =A2&COUNTIF($A$2:A2,A2)

In excel in have 2 columns: order and helper, i am running the above formula in excel column 2, which basically copy data from order column and add suffix to same order_id.

<table>
  <thead>
    <tr>
      <th>Order_id</th>
      <th>Helper</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>45235</td>
      <td>452351</td>
    </tr>
    <tr>
      <td>45235</td>
      <td>452352</td>
    </tr>
  </tbody>
</table>

Please add sample data in tabular form. This is especially needed for those who know SQL, but don't know Excel. — Tim Biegeleisen, Commented Jul 16, 2022 at 15:07
So, what happens if there are ten or more rows with the same order_id? In your sample data, you have order_id = 5407640 in three rows, and in each you add one more digit at the end (1, 2, 3). What if there are 10 such rows? Does the last row become 540764010. adding two digits (1 and 0) at the end? — user5683823, Commented Jul 16, 2022 at 18:44
@mathguy In my case same order_id cannot exceed after 9 repeatations. so far i have seen its ending 3 or max at 4.. its a bad table structure though. — Rizwan, Commented Jul 17, 2022 at 11:45

psaraj12 · Accepted Answer · 2022-07-17 05:13:09Z

1

You can use row_number() to acheive the same.Please find the db fiddle

    with data as(
      select 
        45235 order_id 
      from 
        dual 
      union all 
      select 
        45235 order_id 
      from 
        dual
    ) 
    select 
      order_id, 
      order_id || row_number() OVER(
        partition by order_id 
        order by 
          null
      ) 
    from 
      data order dy 1;

edited Jul 17, 2022 at 5:13

answered Jul 16, 2022 at 15:34

psaraj12

5,0323 gold badges24 silver badges33 bronze badges

Thanks @psaraj12 for the snippet, but it will reapeat each order 4 time, while if you see in the picture i just uploaded in the question, i am counting and adding the count as suffix in column 2..
– Rizwan
Commented Jul 16, 2022 at 15:48
1

my SQL also returns the same ,have included order by,please check again
– psaraj12
Commented Jul 16, 2022 at 16:59
Thanks a lot @psaraj12, made few changes to your sql and got the result... got result with the below SQL select order_id, order_id || ROW_NUMBER() OVER(PARTITION BY order_id ORDER BY order_id) as helper from RAW_ORDER_99 where order_id = 5407640 order by 1;
– Rizwan
Commented Jul 16, 2022 at 18:39
"order by order_id" doesn't do anything in your analytic function (since the ordering is only within each partition, and in a given partition, "order_id" is constant). row_number() does indeed require an order by clause (for reasons that aren't clear to me); when you want to ignore that, it's better to write "order by null", to make it crystal clear that the order is irrelevant.
– user5683823
Commented Jul 16, 2022 at 18:45
@mathguy updated the answer with order by as null in row_number() and it returns the same results
– psaraj12
Commented Jul 17, 2022 at 5:00

| Show 1 more comment

Collectives™ on Stack Overflow

Convert excel formula to Oracle SQL

1 Answer 1

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Related