Re: Assigning Multiple Variable Based On Table Row

["Ryan Hunt" <ryan.hunt@xxxxxxxxxxxxx>]

Hopefully this is the last time on need syntax help on this...

Charles, I am getting an error at my END WHILE; statement.  Any ideas why?
___________________________________________________________
CREATE PROCEDURE DEVDTA.SPCONTACTIMPORT ( )
 LANGUAGE SQL
 SPECIFIC DEVDTA.SPCONTACTIMPORT
 NOT DETERMINISTIC
 MODIFIES SQL DATA
 CALLED ON NULL INPUT
BEGIN
DECLARE @STREET1 VARCHAR(100);
DECLARE @STREET2 VARCHAR(100);
DECLARE @CITY VARCHAR(50);
DECLARE @STATE CHAR(2);
DECLARE @ZIP VARCHAR(15);
DECLARE @ADDRESSNAME VARCHAR(50);
DECLARE @STREET3 VARCHAR(100);
DECLARE @COUNTER INTEGER;
DECLARE @MAXCOUNTER INTEGER;
DECLARE @ADDRESSID INTEGER;
DECLARE @TENANTID INTEGER;
DECLARE @LEASEID INTEGER;
DECLARE @PHONENUMBER VARCHAR(50);
DECLARE @PHONEID INTEGER;
DECLARE @FAXNUMBER VARCHAR(50);
DECLARE @FAXID INTEGER;
DECLARE @FIRSTNAME VARCHAR(50);
DECLARE @MIDDLENAME VARCHAR(50);
DECLARE @LASTNAME VARCHAR(50);
DECLARE @NICKNAME VARCHAR(50);
DECLARE @SURNAME CHAR(5);
DECLARE @SUFFIXNAME CHAR(5);
DECLARE @PEOPLEID INTEGER;
DECLARE @HIWCONTACTTYPE INTEGER;
DECLARE @ADDRESSTYPE INTEGER;



SET @COUNTER=1;
SET @MAXCOUNTER = (SELECT MAX(ID) FROM DEVDTA.CONTACTIMPORT);

WHILE @COUNTER<=@MAXCOUNTER DO

  --INSERT ADDRESS RECORD
  SELECT ADDRESS1, ADDRESS2, CITY, STATE, ZIP, ADDRESSNAME, ADDRESSTYPE
, TENANTID, LEASEID, PHONENUMBER, FAXNUMBER, CONTACTFIRST, CONTACTMIDDLE
, CONTACTLAST, CONTACTNICKNAME, CONTACSURNAME, CONTACTSUFFIXNAME,
CONTACTTYPE
  INTO
 @STREET1, @STREET2, @CITY, @STATE, @ZIP, @ADDRESSNAME, @ADDRESSTYPE,
@TENANTID, @LEASEID, @PHONENUMBER, @FAXNUMBER, @FIRSTNAME, @MIDDLENAME
, @LASTNAME, @NICKNAME, @SURNAME, @SUFFIXNAME, @HIWCONTACTTYPE
FROM DEVDTA.CONTACTIMPORT WHERE ID=@COUNTER;

INSERT INTO DEVDTA.CO_ADDRESS (SSTREET1, SSTREET2, SCITY, SSTATECD, SZIP,
SADDRESSNAME, SADDRESSNOTE, SSTREET3)
VALUES(@STREET1, @STREET2, @CITY, @STATE, @ZIP, @ADDRESSNAME, NULL,
@STREET3);

VALUES IDENTITY_VAL_LOCAL() INTO @ADDRESSID; --CAPTURE ADDRESSID IDENTITY
VALUE

  --INSERT ADDRESS MANY-TO-MANY ADDRESSTYPE RECORD
INSERT INTO DEVDTA.CO_ADDRESSMMADDRESSTYPE (NADDRESSID, NADDRESSTYPEID,
NDOCO)
VALUES(@ADDRESSID, @ADDRESSTYPE, @LEASEID);

  --INSERT COMPANY MANY-TO-MANY ADDRESS RECORD
INSERT INTO DEVDTA.CO_COMPANYMMADDRESS (NCOMPANYID, NADDRESSID)
VALUES(@TENANTID, @ADDRESSID);

IF @PHONENUMBER IS NOT NULL THEN
BEGIN
 INSERT INTO DEVDTA.CO_CONTACTVALUE (SCONTACTVALUE, NCONTACTVALUETYPEID)
 VALUES(@PHONENUMBER, 2);
 VALUES IDENTITY_VAL_LOCAL() INTO @PHONEID;
END;

IF @FAXNUMBER IS NOT NULL THEN
 BEGIN
 INSERT INTO DEVDTA.CO_CONTACTVALUE(SCONTACTVALUE, NCONTACTVALUETYPEID)
 VALUES(@FAXNUMBER, 3);
 VALUES IDENTITY_VAL_LOCAL() INTO @FAXID;
END;

INSERT INTO DEVDTA.CO_PEOPLE(SFIRSTNAME, SMIDDLENAME, SLASTNAME, SNICKNAME,
SSURNAME, SSUFFIXNAME, SPEOPLENOTE)
VALUES(@FIRSTNAME, @MIDDLENAME, @LASTNAME, @NICKNAME, @SURNAME, @SUFFIXNAME,
NULL);
VALUES IDENTITY_VAL_LOCAL() INTO @PEOPLEID;

INSERT INTO DEVDTA.CO_PEOPLEMMCOMPANY (NPEOPLEID, NCOMPANYID)
VALUES(@PEOPLEID, @TENANTID);

IF @PHONENUMBER IS NOT NULL THEN
 BEGIN
  INSERT INTO DEVDTA.CO_PEOPLEMMCONTACTVALUE(NPEOPLEID, NCONTACTVALUEID)
  VALUES(@PEOPLEID, @PHONEID);
END;

IF @FAXNUMBER IS NOT NULL THEN
  BEGIN
   INSERT INTO DEVDTA.CO_PEOPLEMMCONTACTVALUE(NPEOPLEID, NCONTACTVALUEID)
   VALUES(@PEOPLEID, @FAXID);
  END;

INSERT INTO DEVDTA.CO_PEOPLEMMHIWCONTACTTYPE(NPEOPLEID, NHIWCONTACTTYPEID,
NDOCO)
VALUES(@PEOPLEID, @HIWCONTACTTYPE, @LEASEID);

SET @COUNTER=@COUNTER+1;
END WHILE;

END

______________________________________________

I just don't see the error. RH




"Wilt, Charles" <CWilt@xxxxxxxxxxxx> wrote in
message
news:57FCBE9213FC584692CAAECA3F59426F50B33E@xxxxxxxxxxxxxxxxxxxxxxxxxx
> I think I understand what you are trying to do....you're not even close.
>
>
> Take a look at the SELECT INTO statement in the SQL Reference.
>
>
> Charles Wilt
> --
> iSeries Systems Administrator / Developer
> Mitsubishi Electric Automotive America
> ph: 513-573-4343
> fax: 513-398-1121
>
>
> > -----Original Message-----
> > From: midrange-l-bounces@xxxxxxxxxxxx
> > [mailto:midrange-l-bounces@xxxxxxxxxxxx] On
Behalf Of Ryan Hunt
> > Sent: Friday, August 18, 2006 8:05 AM
> > To: midrange-l@xxxxxxxxxxxx
> > Subject: Assigning Multiple Variable Based On Table Row
> >
> > How do you assign multiple variables at the same time on an
> > AS400?  I've
> > looked through the SQL reference for V5R2 and I'm not seeing
> > what I need.
> >
> > I am getting syntax errors on:
> >
> >   SELECT @STREET1=ADDRESS1, @STREET2=ADDRESS2, @CITY=CITY,
> > @STATE=STATE,
> > @ZIP=ZIP, @ADDRESSNAME=ADDRESSNAME, @ADDRESSTYPE=ADDRESSTYPE
> >  , @TENANTID=TENANTID, @LEASEID=LEASEID, @PHONENUMBER=PHONENUMBER,
> > @FAXNUMBER=FAXNUMBER, @FIRSTNAME=FIRSTNAME, @MIDDLENAME=MIDDLENAME
> >   , @LASTNAME=LASTNAME, @NICKNAME=NICKNAME, @SURNAME=SURNAME,
> > @SUFFIXNAME=SUFFIXNAME, @HIWCONTACTTYPE=CONTACTTYPE
> >   FROM DEVDTA.CONTACTIMPORT WHERE ID=@COUNTER;
> >
> > Thanks in advance.
> >
> >
> >
> > -- 
> > This is the Midrange Systems Technical Discussion
> > (MIDRANGE-L) mailing list
> > To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
>
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.