Sylvain Lafontaine
3/18/2007 11:54:00 PM
First, you should include only the relevant columns that are necessary to
the understand of your problem. Second, if you want people to perform
tests, you should include the necessary sql statements to both create the
tables and inserts the data.
In your case and without making any test, I would say that the most likely
explanation for the value of TQ00338 not being inserted is simply because
it's not part of the FMSREQ table or if it's part of it, then there is no
matching record in the table FMSPRD for the commun PRODVER key. This could
be easily verified by executing the following query:
«
select ptf.ptfid, req.type, req.reqptfid, ptf.prodver,
req.status, ptf.cumdate,
prd.fmid,
ptf.vandesc, 'Y', 'O'
FROM FMSREQ REQ INNER JOIN
FMSPTF PTF INNER JOIN
FMSPRD PRD ON PTF.PRODVER = PRD.PRODVER ON PTF.PTFID =
REQ.PTFID
where ptf.ptfid in (select distinct reqptfid from #temprch)
and ptf.ptfid not in (select distinct ptfid from #temprch)
»
right after the WHILE loop. If this query returns 0 row, then no wonder
that no more row are inserted.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"rsjrny" <russlilley@yahoo.com> wrote in message
news:1174248646.243109.233770@l77g2000hsb.googlegroups.com...
>I need help creating a while loop that inserts records into a
> temporary table
> this while should continue until all records matching column 3 have
> been added
>
> The insert above the while loop seeds the temporary table.
> Then the loop selects all distinct values from column 3 that do not
> exist in column 1
>
> my rows selected are
>
> (7 row(s) affected) 1st Insert
> (11 row(s) affected) insert within while loop
> (0 row(s) affected) second execution of while
> (18 row(s) affected)
>
>
>
> ============================================================================
> These are selected by the first insert:
>
>
> column1 column3
> ------- - ------- -------- --- -------------------
> TQ00571 P TQ00354 LMSSV020 OK 2001-07-12 00:00:00 LMSS200
> INTERMITTENT ABEND S0C4 AFTER TQ00553 Y I
> TQ00571 P TQ00370 LMSSV020 OK 2001-07-12 00:00:00 LMSS200
> INTERMITTENT ABEND S0C4 AFTER TQ00553 Y I
> TQ00571 P TQ00417 LMSSV020 OK 2001-07-12 00:00:00 LMSS200
> INTERMITTENT ABEND S0C4 AFTER TQ00553 Y I
> TQ00571 P TQ00550 LMSSV020 OK 2001-07-12 00:00:00 LMSS200
> INTERMITTENT ABEND S0C4 AFTER TQ00553 Y I
> TQ00571 P TQ00553 LMSSV020 OK 2001-07-12 00:00:00 LMSS200
> INTERMITTENT ABEND S0C4 AFTER TQ00553 Y I
> TQ00571 S RQ00074 LMSSV020 OK 2001-07-12 00:00:00 LMSS200
> INTERMITTENT ABEND S0C4 AFTER TQ00553 Y I
> TQ00571 S TQ00421 LMSSV020 OK 2001-07-12 00:00:00 LMSS200
> INTERMITTENT ABEND S0C4 AFTER TQ00553 Y I
>
> Then the While Loop should insert into the table all records that
> match
> column 3 (and it does)
> Now I want the loop to process the table again and find all records
> matching column 3 that do not already exist in column 1. It finds
> zero rows and
> stops but column 3 has value TQ00338 that does not exist in column 1
> so the
> records for TQ00338 should be inserted
>
>
> TQ00354 P TQ00338 LMSSV020 OK 2000-08-28 00:00:00 LMSS200 LMSSIPLR
> JOB GETS - IEFC607I JOB HAS NO STEPS - ALSO LMRK501 Y O
> TQ00370 P TQ00338 LMSSV020 OK 2000-09-27 00:00:00 LMSS200 MINOR
> EDITORIAL CORRECTIONS TO MESSAGE TEXTS Y O
> TQ00370 P TQ00354 LMSSV020 OK 2000-09-27 00:00:00 LMSS200 MINOR
> EDITORIAL CORRECTIONS TO MESSAGE TEXTS Y O
> TQ00417 P TQ00354 LMSSV020 OK 2000-11-13 00:00:00 LMSS200 NO SAMPLE
> JCL PROVIDED FOR $SDBIPL ENVIRONMENT REPORTING Y O
> TQ00417 P TQ00370 LMSSV020 OK 2000-11-13 00:00:00 LMSS200 NO SAMPLE
> JCL PROVIDED FOR $SDBIPL ENVIRONMENT REPORTING Y O
> TQ00421 P TQ00354 LMSSV020 OK 2000-11-13 00:00:00 LMSS200 MESSAGES
> LMRK50154E - ~REPLACE~ IMPOSSIBLE; CURRENT ENVIRONM Y O
> TQ00421 P TQ00370 LMSSV020 OK 2000-11-13 00:00:00 LMSS200 MESSAGES
> LMRK50154E - ~REPLACE~ IMPOSSIBLE; CURRENT ENVIRONM Y O
> TQ00421 P TQ00417 LMSSV020 OK 2000-11-13 00:00:00 LMSS200 MESSAGES
> LMRK50154E - ~REPLACE~ IMPOSSIBLE; CURRENT ENVIRONM Y O
> TQ00550 P TQ00370 LMSSV020 OK 2001-07-12 00:00:00 LMSS200 FORCE
> CAUSES ORPHANED QMS ENTITIES Y O
> TQ00553 P TQ00370 LMSSV020 OK 2001-07-12 00:00:00 LMSS200
> SERVICEABILITY ENHANCEMENTS Y O
> TQ00553 P TQ00550 LMSSV020 OK 2001-07-12 00:00:00 LMSS200
> SERVICEABILITY ENHANCEMENTS Y O
>
>
>
> Code:
>
>
> create table #temprch
> (ptfid char(7),
> type char(1),
> reqptfid char(7),
> prodver char(8),
> status char(3),
> cumdate smalldatetime,
> fmid char(7),
> vandesc char(60),
> selected char(1),
> inpout char(1)
> )
> Insert into #temprch
> select ptf.ptfid, req.type, req.reqptfid, ptf.prodver,
> req.status, ptf.cumdate,
> prd.fmid,
> ptf.vandesc, 'Y', 'I'
> FROM FMSREQ REQ INNER JOIN
> FMSPTF PTF INNER JOIN
> FMSPRD PRD ON PTF.PRODVER = PRD.PRODVER ON PTF.PTFID =
> REQ.PTFID
> where ptf.ptfid = @P1
>
> While @@ROWCOUNT > 0
> begin
>
> insert into #temprch (ptfid, type, reqptfid, prodver, status,
> cumdate,
> fmid, vandesc, selected, inpout)
> select ptf.ptfid, req.type, req.reqptfid, ptf.prodver,
> req.status, ptf.cumdate,
> prd.fmid,
> ptf.vandesc, 'Y', 'O'
> FROM FMSREQ REQ INNER JOIN
> FMSPTF PTF INNER JOIN
> FMSPRD PRD ON PTF.PRODVER = PRD.PRODVER ON PTF.PTFID =
> REQ.PTFID
>
> where ptf.ptfid in (select distinct reqptfid from #temprch)
> and ptf.ptfid not in (select distinct ptfid from #temprch)
>
> END
>
>
> select *
> from #temprch
> order by ptfid
>