Re: will this query work in ibm i

On 01-Jan-2016 10:35 -0700, Hoteltravelfundotcom wrote:

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

All queries without a FROM clause will not /work/ with the DB2 for i SQL. Additionally, many other statements are incorrect syntax, or might choose an alternate statement entirely; e.g. the many INSERT statements for each file would best be consolidated into one using VALUES() vs SELECT. The statements are also not separated\followed by a semicolon. The control statement WHILE is not coded with proper\expected syntax, nor is the CREATE PROCEDURE; missing an End for the Begin and IIRC a DECLARE must precede actual executable statements.

As has been noted many times, the proper syntax is documented in the KnowledgeCenter; reference to those docs could have easily saved having asked the question [and having received such a near-useless reply]. I believe also having been pointed out in past and similar inquiries, there exists some ¿migration? tooling enabling conversion from some variants of non-ANS SQL into /generic/ DB2 SQL [i.e. not specific to IBM i DB2 SQL; possibly also not strictly ANSI]; that probably can be found searching for something related to DB2 LUW.