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

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!!

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)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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.

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.