Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Tricky Analytical/Aggregae Sql

dd_ramSep 6 2008 — edited Sep 9 2008
Hi,
I have data like below.
MRP_DATE SHORT_FLAG
8/25/2008 Y
8/26/2008 Y
8/27/2008 N
8/28/2008 Y
8/29/2008 Y
8/30/2008 Y
8/31/2008 N
9/1/2008 Y
9/2/2008 Y
9/3/2008 N
9/4/2008 N
I want to calculate Short_days. If the Short_flag changes from Y to N, it needs to be reset to 1. The output should look like below.
MRP_DATE SHORT_FLAG Shortage_days
8/25/2008 Y 1
8/26/2008 Y 2
8/27/2008 N 0
8/28/2008 Y 1
8/29/2008 Y 2
8/30/2008 Y 3
8/31/2008 N 0
9/1/2008 Y 1
9/2/2008 Y 2
9/3/2008 N 0
9/4/2008 N 0
Would you guys Please tell me how to achieve this??

Thanks,
Ram.
This post has been answered by Solomon Yakobson on Sep 7 2008
Jump to Answer

Comments

odie_63

Hi,

Simple example :

If the count() function returns 11, then 11 div 10 = 1.1 (datatype xs:decimal), and 1.1 is not a valid value for the sequence iterator, it must be an integer.

At this point you have different options depending on how you want to round the value to an integer :

  • using fn:round()
  • using fn:floor()
  • using fn:ceiling()
  • casting to xs:integer directly : for $v in 1 to xs:integer(fn:count($anyType1/*:adad) div 10)

Your choice.

1 - 1
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 7 2008
Added on Sep 6 2008
29 comments
4,144 views