[maj3091]

Hi,

I'm not too great with SQL, so looking for a little guidance on how to achieve the following through a stored procedure (or procedures).

I have 2 tables, Headers and Lines.

Cutdown schemas as follows:

[Header]
HeaderNumber, HeaderSuffix, HeaderLine, Complete

[Line]
HeaderNumber, HeaderSuffix, Line, Location, Complete


The data is populated as such that you have one header and multiple lines. The reason for the HeaderLine, is that in certain circumstances, certain lines, need to be handled outside of the main group of lines, before the full list can be worked with.

So for example, one line has Location = 'EXTERNAL', which is telling me stock is not in the current warehouse, so it needs to be handled as a single line, to get stock to the warehouse, then the line will be updated, and the LineHeader will be closed. In other words, the master header is locked, until all interim processes have been completed and once they're completed, the Master can be released.

So what I'm trying to do is as follows:

-- Stage 1 Get a list of lines, whose location was passed in. 
Select * from [Lines] where Location=@Location

-- Stage 2 Using the HeaderNumber, HeaderSuffix, Line from stage 1 Mark headers as complete
Update [Header] setting Complete=2 where Number, Suffix and Line match.

-- Stage 3 Using the HeaderNumber, HeaderSuffix, Line from stage 1
Update [Line] setting Location=@NewLocation 

-- Stage 4 Check if any other line headers exist for the Header Number returned and if not
-- update the master headers so they can be used.
Select max(HeaderLine) from [Header] where Header/Suffix/Line and Complete=0 match returned in Stage 1 and if returns Null or 0 then update Complete = 0 on Header with Line=0

I apologise that this is a little long winded, but I wasn't sure how else to describe it.

Any pointers would be greatly appreciated.

[maj3091]

I think i've figured it out myself after cobbling bits together from various examples, although it may not be the best way, it works.

/*Declare the variables that we will need in the stored proc */
Declare	@count	 Int
Declare	@LoopCount	 Int
declare @Header varchar(10)
declare @Suffix varchar(3)
declare @Lin int
declare @LineCount int

--Get all the records related to the Location
select Identity(int,1,1) id,HeaderNumber,HeaderSuffix,Line
into	#temp
from	Lines
where Location=@FromLoc

set	@count = @@rowcount
set	@LoopCount = 1

-- Loop through the records in the temp table
while @LoopCount <= @count
begin

	select	@Header= HeaderNumber,
			@Suffix = HeaderSuffix,
			@Lin = Line
	from	#temp
	where id = @LoopCount

	set @LoopCount = @LoopCount + 1

	--UPDATE Line - replace old location with new location
	update	Lines
	set	Location = @NewLoc
	where	HeaderNumber = @Header and HeaderSuffix=@Suffix and Line= @Lin 

	--UPDATE Header - Update header to mark it as complete
	update	Header
	set	Complete = '2'
	where	HeaderNumber = @Header and HeaderSuffix=@Suffix and HeaderLine= @Lin 
	
end

--Check if there are other outstanding sub-headers
--Get distinct list of Header/Suffix combos
select distinct HeaderNumber, HeaderSuffix
into	#tempDummy
from	#temp

select Identity(int,1,1) id, HeaderNumber, HeaderSuffix
into	#temp2
from	#tempDummy

set	@count = @@rowcount
set	@LoopCount = 1

-- Loop through the records in the temp2 table
while @LoopCount <= @count
begin

	select	@Header= HeaderNumber,
			@Suffix = HeaderSuffix
	from	#temp2
	where id = @LoopCount

	set @LoopCount = @LoopCount + 1

	-- Get the highest line number still not processed for this Header/suffix combo
	set @LineCount= (select MAX(HeaderLine) from Header where HeaderNumber=@Header and HeaderSuffix=@Suffix and Complete<>'2')

	-- If highest is zero, then only master remaining, so release it
	if @LineCount=0
		begin
			--UPDATE Header to release master
			update	Header
			set	Complete = 0
			where	HeaderNumber = @Header and HeaderSuffix=@Suffix and HeaderLine=0 and complete='H'
		end
			
end

[mojo666]

For future reference, I think this may be an easier way to update the tables

SELECT HeaderNumber, HeaderSuffix, Line
INTO #temp
FROM Lines
WHERE Location=@FromLoc

--Updates all rows in Lines that match a row in #temp--
UPDATE l
SET l.Location=@NewLoc
FROM
Lines l
INNER JOIN #temp t
ON t.HeaderNumber=l.HeaderNumber
AND t.HeaderSuffix=l.HeaderSuffix
AND t.Line=l.Line

--Updates all rows in Header that match a row in #temp--
UPDATE h
SET h.Complete='2'
FROM
Header h
INNER JOIN #temp t
ON t.HeaderNumber=h.HeaderNumber
AND t.HeaderSuffix=h.HeaderSuffix
AND t.Line=h.HeaderLine

--Rest of your code--

[baavgai]

Perhaps I'm missing something...

--UPDATE Header - Update header to mark it as complete
update	Header
	set Complete = '2'
	from Header a
		inner join Lines b
			on a.HeaderNumber = b.HeaderNumber
				and a.HeaderSuffix=b.HeaderSuffix
				and a.HeaderLine=b.Line
				and b.Location=@FromLoc

--UPDATE Line - replace old location with new location
update	Lines
	set Location = @NewLoc
	where Location = @FromLoc

[maj3091]

baavgai, on 21 August 2012 - 07:52 PM, said:

Perhaps I'm missing something...

--UPDATE Header - Update header to mark it as complete
update	Header
	set Complete = '2'
	from Header a
		inner join Lines b
			on a.HeaderNumber = b.HeaderNumber
				and a.HeaderSuffix=b.HeaderSuffix
				and a.HeaderLine=b.Line
				and b.Location=@FromLoc

--UPDATE Line - replace old location with new location
update	Lines
	set Location = @NewLoc
	where Location = @FromLoc

If that handles what's in the first loop, then the only thing missing is the second loop.

I never could get to grips with SQL and looking at how yourself and mojo have simplified my code, I'm still a long way off.

Thanks for the suggestions, I'll try them out tomorrow when I'm back in the office.

[mojo666]

baavgai, on 21 August 2012 - 12:52 PM, said:

Perhaps I'm missing something...

--UPDATE Header - Update header to mark it as complete
update	Header
	set Complete = '2'
	from Header a
		inner join Lines b
			on a.HeaderNumber = b.HeaderNumber
				and a.HeaderSuffix=b.HeaderSuffix
				and a.HeaderLine=b.Line
				and b.Location=@FromLoc

--UPDATE Line - replace old location with new location
update	Lines
	set Location = @NewLoc
	where Location = @FromLoc

Definately simpler and more straight forward. This will probably work well for maj, however I will express a word of caution. If a new record was inserted after the first update, you may then update the new lines without marking the header complete. Such a record would then be half processed and not picked up on the next run. The join to the temp table better ensures that you are working with a constant set. It all depends on the activity of the environment though, and that will be up to maj to determine.

Quote

I never could get to grips with SQL and looking at how yourself and mojo have simplified my code, I'm still a long way off.

Judging from the code you came up with, it looks like you are in that "iterative programmer adjusting to declarative programming" phase. My only suggestion is to try to learn and practice the programming constructs provided by SQL (JOINS, GROUPS, UNIONS, ect). With these, you will be striving to make your code more of the form "Do what I want" as opposed to "Do this, then this, then that, then...ect".

[baavgai]

mojo666, on 21 August 2012 - 05:28 PM, said:

If a new record was inserted after the first update, you may then update the new lines without marking the header complete.

If this is a concern, then transaction processing should be used. More here.

[maj3091]

mojo666, on 21 August 2012 - 10:28 PM, said:

Quote

I never could get to grips with SQL and looking at how yourself and mojo have simplified my code, I'm still a long way off.

Judging from the code you came up with, it looks like you are in that "iterative programmer adjusting to declarative programming" phase. My only suggestion is to try to learn and practice the programming constructs provided by SQL (JOINS, GROUPS, UNIONS, ect). With these, you will be striving to make your code more of the form "Do what I want" as opposed to "Do this, then this, then that, then...ect".

You're spot on. I get limited exposure to SQL through work and in the past I've even taken to doing a lot of the work in the calling program, so I do try to pick up a little bit more each time I delve into it.


In regards to the comment about new records being added, the data will be static when this procedure is executed (or at least that's what the specification says).

Thanks again to you both.

This post has been edited by maj3091: 22 August 2012 - 12:22 AM