Re: will this query work in ibm i

[Rob <rob.couch@xxxxxxxxxxxxxx>]

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