Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Lets say I have a table with 2 columns: one having distinct dates (DATE) and another having floating point values (PRICE), including nulls. What I want to achieve is add a new column (NON_ZERO_ID) which essentially has a value and is incremented only if PRICE is not null. Here's the desired output:
DATE | PRICE | NON_ZERO_ID
1/2/16| 0.12| 1
1/3/16|45.00| 2
1/4/16|null| null
1/5/16|40.00|3...

What I have tried:

I have tried using row_number() over (order by..) syntax for numbering the rows, but in all cases, the NON_ZERO_ID is getting auto-incremented even if there is a null value for PRICE. Any suggestions? Essentially, I want to be able to make the NON_ZERO_ID's with a string pre-pended as well, i.e., ID1, ID2, and so on.
Posted
Updated 9-Jan-17 17:53pm
Comments
barneyman 9-Jan-17 21:03pm    
you could do it with a trigger on the insert

Why are you trying to do it this way, it seems (on the face of it) a bit confusing - What happens if a price is added, or removed, later?

Adding strings to rowids is a bad idea too ... that should be done by the presentation layer - IMHO
Member 12189753 9-Jan-17 21:34pm    
thanks for the suggestion. A price can be added or removed later, but for the current purposes, i need to use the identifier to join with another reference table which gives benchmarks based on the prices and whether the dates are weekdays or not. I will essentially need to extend the logic here to update the indicator based on whether it is a weekday or not. And i see your point on adding strings to rowid's. Is it possible to achieve the solution without using triggers or inserts?

1 solution

First of all, this sounds like you're trying to create a sequence of ordinals for certain rows without gaps. If that is true, it should be noted that you will have gaps if the rows are deleted. Also what happens if the price is updated to null or vice versa?

Having that said, you could do the insert using a select statement. Something like
SQL
INSERT INTO TableName
(DATE, PRICE, NON_ZERO_ID)
SELECT :datevariable
       :pricevariable
       CASE 
          WHEN :pricevariable IS NULL THEN NULL
          ELSE (SELECT MAX(NON_ZERO_ID) + 1
                FROM   TableName)
       END
FROM dual;

At the moment I don't have the possibility to check syntax so hopefully it doesn't contain mistakes...
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900