× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



To who ever,

That does not look OK for any table layout, my own personal opinion.

IMOO you are working way to hard to do something which a better table design would resolve easily. You are hard coding data into field names...

Position 1 is not a field name... Position is a field, 1 is it's data.

create table Locations
(
Item bigint,
Location smallint,
Position smallint,
Aisle datatype,
Bin datatype,
Qty int
) ;

Item is the Product...
Location is the Building

Happy coding...

Rob

On 01/01/2016 11:35 AM, Hoteltravelfundotcom wrote:
HI we have a task to create warehouse locations for a new facility. they
are to be 7 positions and one blank at end. Having locations, aisle, bin
etc. Off the top of your head, does this look like ok on ibm i ? this is
compiled to sql server,

create Procedure [dbo].[Sp_generatelocation]
AS
Begin
create table #1stPosition
(
FirstPosition varchar(10),
FirstFlag int
)

create table #2ndPosition
(
SecondPosition varchar(10),
SecondFlag int
)

create table #3rd4thPosition
(
ThirdFourthPosition varchar(10),
ThirdFourthFlag int
)


create table #FifthPosition
(
FifthPosition varchar(10),
FifthFlag int
)


create table #SixSevenPosition
(
SixSevenPosition varchar(10),
SixSeven int
)


create table #Location
(
Location varchar(10)
)


--------------------FOR 1ST COLUMN---------
Insert into #1stPosition
SELECT 'A',0
Insert into #1stPosition
SELECT 'B',0
Insert into #1stPosition
SELECT 'C',0
Insert into #1stPosition
SELECT 'D',0
Insert into #1stPosition
SELECT 'E',0
Insert into #1stPosition
SELECT 'F',0
Insert into #1stPosition
SELECT 'G',0
Insert into #1stPosition
SELECT 'H',0
Insert into #1stPosition
SELECT 'I',0
Insert into #1stPosition
SELECT 'J',0
Insert into #1stPosition
SELECT 'K',0
Insert into #1stPosition
SELECT 'L',0

--SELECT * FROM #1stPosition
--RETURN
--------------------FOR 2ND COLUMN---------
INSERT INTO #2ndPosition
SELECT 'A',0
INSERT INTO #2ndPosition
SELECT 'B',0

--------------FOR 3RD & 4TH COLUMN---------
INSERT INTO #3rd4thPosition
SELECT '01',0
INSERT INTO #3rd4thPosition
SELECT '02',0
INSERT INTO #3rd4thPosition
SELECT '03',0
INSERT INTO #3rd4thPosition
SELECT '04',0
INSERT INTO #3rd4thPosition
SELECT '05',0
INSERT INTO #3rd4thPosition
SELECT '06',0
INSERT INTO #3rd4thPosition
SELECT '07',0
INSERT INTO #3rd4thPosition
SELECT '08',0
INSERT INTO #3rd4thPosition
SELECT '09',0
INSERT INTO #3rd4thPosition
SELECT '10',0
INSERT INTO #3rd4thPosition
SELECT '11',0
INSERT INTO #3rd4thPosition
SELECT '12',0
INSERT INTO #3rd4thPosition
SELECT '13',0
INSERT INTO #3rd4thPosition
SELECT '14',0
INSERT INTO #3rd4thPosition
SELECT '15',0
INSERT INTO #3rd4thPosition
SELECT '16',0
INSERT INTO #3rd4thPosition
SELECT '17',0
INSERT INTO #3rd4thPosition
SELECT '18',0
INSERT INTO #3rd4thPosition
SELECT '19',0
INSERT INTO #3rd4thPosition
SELECT '20',0
INSERT INTO #3rd4thPosition
SELECT '21',0
INSERT INTO #3rd4thPosition
SELECT '22',0
INSERT INTO #3rd4thPosition
SELECT '23',0
INSERT INTO #3rd4thPosition
SELECT '24',0
INSERT INTO #3rd4thPosition
SELECT '25',0
INSERT INTO #3rd4thPosition
SELECT '26',0
INSERT INTO #3rd4thPosition
SELECT '27',0
INSERT INTO #3rd4thPosition
SELECT '28',0
INSERT INTO #3rd4thPosition
SELECT '29',0
INSERT INTO #3rd4thPosition
SELECT '30',0
INSERT INTO #3rd4thPosition
SELECT '31',0
INSERT INTO #3rd4thPosition
SELECT '32',0
INSERT INTO #3rd4thPosition
SELECT '33',0
INSERT INTO #3rd4thPosition
SELECT '34',0
INSERT INTO #3rd4thPosition
SELECT '35',0
INSERT INTO #3rd4thPosition
SELECT '36',0
INSERT INTO #3rd4thPosition
SELECT '37',0
INSERT INTO #3rd4thPosition
SELECT '38',0
INSERT INTO #3rd4thPosition
SELECT '39',0
INSERT INTO #3rd4thPosition
SELECT '40',0
INSERT INTO #3rd4thPosition
SELECT '41',0
INSERT INTO #3rd4thPosition
SELECT '42',0
INSERT INTO #3rd4thPosition
SELECT '43',0
INSERT INTO #3rd4thPosition
SELECT '44',0
INSERT INTO #3rd4thPosition
SELECT '45',0
INSERT INTO #3rd4thPosition
SELECT '46',0
INSERT INTO #3rd4thPosition
SELECT '47',0
INSERT INTO #3rd4thPosition
SELECT '48',0
INSERT INTO #3rd4thPosition
SELECT '49',0
INSERT INTO #3rd4thPosition
SELECT '50',0

----#FifthPosition
INSERT INTO #FifthPosition
SELECT 'A',0
INSERT INTO #FifthPosition
SELECT 'B',0
INSERT INTO #FifthPosition
SELECT 'C',0
INSERT INTO #FifthPosition
SELECT 'D',0


----#FifthPosition
INSERT INTO #SixSevenPosition
SELECT '01',0
INSERT INTO #SixSevenPosition
SELECT '02',0



DECLARE @1stPosition VARCHAR(1),
@2ndPosition VARCHAR(1),
@3rd4thPosition VARCHAR(2),
@FifthPosition VARCHAR(1),
@SixSevenPosition VARCHAR(2),
@LOCATION VARCHAR(10)

SELECT @LOCATION='A'

--SELECT FirstPosition FROM #1stPosition
--SELECT SecondPosition FROM #2ndPosition
--SELECT ThirdFourthPosition FROM #3rd4thPosition
--SELECT FifthPosition FROM #FifthPosition
--SELECT SixSevenPosition FROM #SixSevenPosition


WHILE (SELECT COUNT(*) FROM #1stPosition)>0 -------------1ST COL
BEGIN
SELECT TOP 1 @1stPosition=FirstPosition FROM #1stPosition
SELECT @LOCATION=@1stPosition
WHILE (SELECT COUNT(*) FROM #2ndPosition where SecondFlag=0)>0
-------------2ND COL
BEGIN
SELECT TOP 1 @2ndPosition=SecondPosition FROM #2ndPosition where
SecondFlag=0
SELECT @LOCATION=@1stPosition+@2ndPosition
WHILE (SELECT COUNT(*) FROM #3rd4thPosition where ThirdFourthFlag=0)>0
-------------3/4 COL
BEGIN
SELECT TOP 1 @3rd4thPosition=ThirdFourthPosition FROM
#3rd4thPosition where ThirdFourthFlag=0
SELECT @LOCATION=@1stPosition+@2ndPosition +@3rd4thPosition

WHILE (SELECT COUNT(*) FROM #FifthPosition where FifthFlag=0)>0
-------------5 COL
BEGIN
SELECT TOP 1 @FifthPosition=FifthPosition FROM #FifthPosition
where FifthFlag=0
SELECT @LOCATION=@1stPosition+@2ndPosition
+@3rd4thPosition+@FifthPosition

WHILE (SELECT COUNT(*) FROM #SixSevenPosition WHERE
SixSeven=0)>0 -------------6/7 COL
BEGIN
SELECT TOP 1 @SixSevenPosition=SixSevenPosition FROM
#SixSevenPosition WHERE SixSeven=0
SELECT @LOCATION=@1stPosition+@2ndPosition
+@3rd4thPosition+@FifthPosition+@SixSevenPosition
insert into #Location select @LOCATION
UPDATE #SixSevenPosition SET SixSeven=1 WHERE
SixSevenPosition=@SixSevenPosition
END
update #SixSevenPosition set SixSeven=0

UPDATE #FifthPosition SET FifthFlag=1 WHERE
FifthPosition=@FifthPosition
END
update #FifthPosition set FifthFlag=0


Update #3rd4thPosition set ThirdFourthFlag=1 WHERE
ThirdFourthPosition=@3rd4thPosition
END
update #3rd4thPosition set ThirdFourthFlag=0
Update #2ndPosition set SecondFlag=1 WHERE SecondPosition=@2ndPosition
END
update #2ndPosition set SecondFlag=0
DELETE FROM #1stPosition WHERE FirstPosition=@1stPosition
END


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.