Avatar of jknj72
jknj72
 asked on

Oracle procedure logic

I had asked a similar(not really) question last week but after I got what I thought I needed I was wrong and this is the logic I need.....
We have a web page builder built in ASP and when a section of the page is deleted I have to be able to realign the numeric values of the row where the section was deleted. The way the page is designed is by adding certain objects to your page(Header, line, paragraph, etc...), with every object having a row number. You can add more than 1 object per row. If you have 1 object in the row, you would have a row number(in my table its the SectionOrdinal field) and a row cell number(in table its SectionCell field). I have to be able to realign the SectionCell values after an object is deleted. If there is only 1 object in the row, its SectionCell value is 0. If there is more than 1 object in the row it is 1 based, An example of the data is attached. I also have some code written in my first attempt, which I will continue to work on, but if youd like to see it let me know.....

THANKS!!

PS - I also attached a visual of how the page is built and how we are saving the row and row cell values
Ordinal-Position.doc
Realign-Sections.docx
Oracle DatabaseSQL

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
Franck Pachot

Hi,

Ok, so you have numbers and you want to avoid gaps when you delete a row.
The correct approach is to keep an increasing number, but don't care about the gaps in the table.

It's only at query time (possibly through a view) that you will renumber the result with analytic function:
rank()over(partition by PAGE_ID order by SECTION_ORDINAL) SECTION_NOGAP
rank()over(partition by PAGE_ID,SECTION_ORDINAL order by SECTION_CELL)-1 CELL_NOGAP

Regards,
Franck.
PortletPaul

I would suggest row_number() instead of: rank() or, dense_rank()

however I don't believe you have the information needed to calculate the wanted order if we cannot rely on the existing SECTION_ORDINAL or SECTION_CELL (which is what we want to calculate anyway)

partition by page_ID is needed and that's, ok

but after that you are in trouble, sectionid is 'random' and sectiontype is alpha, but the alpha arrangement does not align to implied hierarchy

you can try alternative 'order by' portion of row_number (or rank) on your sample data here http://sqlfiddle.com/#!4/32693/9
jknj72

ASKER
Sorry for the delay. What I need to do is order the SectionCell, if deleted. If the Row(SectionOrdinal) has more than 1 SectionCell it will be 1 based and I would have to delete the SectionCell and reorder the remaining cells. Here are a few examples

if a row has 3 cells and the 2nd cell gets deleted I would keep the SectionCell1 the same and update the SectionCell 3 to 2 so .....

123  ---> and I delete 2 then
1 stays the same and 3 becomes 2

12 -----> and I delete 2 then
1 becomes 0 because if there is only 1 value in the SectionCell for that SectionOrdinal(row) then it is 0 based. Only when it has more than 1 value does the SectionCell become 1 based.

Does that make sense?  Please ask any questions that you have and I will stay on top of this and get you answers.....

THANKS everyone!!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
PortletPaul

can you take the set of data below, and mimic the delete/inserts as separate test cases

in other words an 'as is' (has correct numbers)
and a 'to be' (that requires a sequence recalculation)

so we are not guessing
CREATE TABLE Pages
	("PAGE_ID" int, "SECTIONID" int, "SECTIONTYPE" varchar2(9), "SECTIONORDINAL" int, "SECTIONCELL" int)
;

INSERT ALL 
	INTO Pages ("PAGE_ID", "SECTIONID", "SECTIONTYPE", "SECTIONORDINAL", "SECTIONCELL")
		 VALUES (750002, 2102, 'HEADER', 1, 0)
	INTO Pages ("PAGE_ID", "SECTIONID", "SECTIONTYPE", "SECTIONORDINAL", "SECTIONCELL")
		 VALUES (750002, 1084, 'LINE', 2, 0)
	INTO Pages ("PAGE_ID", "SECTIONID", "SECTIONTYPE", "SECTIONORDINAL", "SECTIONCELL")
		 VALUES (750002, 2120, 'HEADER', 3, 0)
	INTO Pages ("PAGE_ID", "SECTIONID", "SECTIONTYPE", "SECTIONORDINAL", "SECTIONCELL")
		 VALUES (750002, 50100, 'PARAGRAPH', 4, 0)
	INTO Pages ("PAGE_ID", "SECTIONID", "SECTIONTYPE", "SECTIONORDINAL", "SECTIONCELL")
		 VALUES (750002, 1101, 'LINE', 5, 0)
	INTO Pages ("PAGE_ID", "SECTIONID", "SECTIONTYPE", "SECTIONORDINAL", "SECTIONCELL")
		 VALUES (750002, 2121, 'HEADER', 6, 0)
	INTO Pages ("PAGE_ID", "SECTIONID", "SECTIONTYPE", "SECTIONORDINAL", "SECTIONCELL")
		 VALUES (750002, 50101, 'PARAGRAPH', 7, 1)
	INTO Pages ("PAGE_ID", "SECTIONID", "SECTIONTYPE", "SECTIONORDINAL", "SECTIONCELL")
		 VALUES (750002, 50102, 'PARAGRAPH', 8, 2)
	INTO Pages ("PAGE_ID", "SECTIONID", "SECTIONTYPE", "SECTIONORDINAL", "SECTIONCELL")
		 VALUES (750002, 50103, 'PARAGRAPH', 9, 3)
	INTO Pages ("PAGE_ID", "SECTIONID", "SECTIONTYPE", "SECTIONORDINAL", "SECTIONCELL")
		 VALUES (750002, 50105, 'PARAGRAPH', 10, 1)
	INTO Pages ("PAGE_ID", "SECTIONID", "SECTIONTYPE", "SECTIONORDINAL", "SECTIONCELL")
		 VALUES (750002, 50106, 'PARAGRAPH', 11, 2)
	INTO Pages ("PAGE_ID", "SECTIONID", "SECTIONTYPE", "SECTIONORDINAL", "SECTIONCELL")
		 VALUES (750002, 50107, 'PARAGRAPH', 12, 3)
	INTO Pages ("PAGE_ID", "SECTIONID", "SECTIONTYPE", "SECTIONORDINAL", "SECTIONCELL")
		 VALUES (750002, 50108, 'PARAGRAPH', 13, 1)
	INTO Pages ("PAGE_ID", "SECTIONID", "SECTIONTYPE", "SECTIONORDINAL", "SECTIONCELL")
		 VALUES (750002, 50109, 'PARAGRAPH', 14, 2)
	INTO Pages ("PAGE_ID", "SECTIONID", "SECTIONTYPE", "SECTIONORDINAL", "SECTIONCELL")
		 VALUES (750002, 50110, 'PARAGRAPH', 15, 3)
	INTO Pages ("PAGE_ID", "SECTIONID", "SECTIONTYPE", "SECTIONORDINAL", "SECTIONCELL")
		 VALUES (750002, 50111, 'PARAGRAPH', 16, 1)
	INTO Pages ("PAGE_ID", "SECTIONID", "SECTIONTYPE", "SECTIONORDINAL", "SECTIONCELL")
		 VALUES (750002, 50112, 'PARAGRAPH', 17, 2)
	INTO Pages ("PAGE_ID", "SECTIONID", "SECTIONTYPE", "SECTIONORDINAL", "SECTIONCELL")
		 VALUES (750002, 50113, 'PARAGRAPH', 18, 3)
	INTO Pages ("PAGE_ID", "SECTIONID", "SECTIONTYPE", "SECTIONORDINAL", "SECTIONCELL")
		 VALUES (750002, 1102, 'LINE', 19, 0)
SELECT * FROM dual
;

Open in new window

ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
jknj72

ASKER
Paul, I attached a document hopefully recognizing all the scenarios. If you can find one that I didnt put down just let me know. Thanks for your help...

Slight, Im gonna try your post now. I will let you know....

Thanks guys!!
Realign-Sections.doc
PortletPaul

I'm just going to make an observation and let the more algorithm oriented experts continue I think...

IF, each row identified "parent" (and maybe "sibling"?) then I think you might have a maintainable design. At the moment I think you will have a dreadful time ensuring the integrity of this data.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
jknj72

ASKER
Pretty lame, but thanks anyway

Thanks Slight, Im gonna go with your approach and I will post the finished product. I have just been real busy but will have it up by the end of the week...
jknj72

ASKER
Thanks once again!!
PortletPaul

>>Pretty lame
perhaps, but you didn't provide what I was looking for when I asked for data, and I'm afraid I don't have time to build-up data for detialed test cases. I expressed my opinion - which is heartfelt. You are free to ignore it of course. It would make your task a lot more simple (I believe) if you provided parent.

I do wish you well. Just feel I cannot viably contribute. I trust other experts will continue with there endeavours.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck