Re: SQL help

[rob@xxxxxxxxx]

This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
Test file ROB/ARLEEN:
R ARLEENR
  MYKEY         10A
  MYDATA        10A

SELECT * FROM ROB/ARLEEN

MYKEY       MYDATA
1           test
2           test
1           test

Now I compile this logical:
                            UNIQUE
R ARLEENR                   PFILE(ARLEEN)
K MYKEY

It created the LF but added no members, because of the duplicates.
Attempts to ADDLFM fail.

So I removed the duplicate record.  Then I ADDLFM.  Then I saved the
logical into a save file and deleted the logical.  I added the duplicate
back.  Then I restored the logical with
RSTOBJ OBJ(ARLEENL) SAVLIB(ROB) DEV(*SAVF) OBJTYPE(*FILE) SAVF(ROB/ROB)
There were no warning messages in the joblog, just:
File ARLEENL in library ROB restored.
1 objects restored from ROB to ROB.

Now even though DSPFD FILE(ROB/ARLEENL) says  UNIQUE Yes,   I can still
run runqry qryfile(rob/arleenl) and get
MYKEY       MYDATA
1           test
2           test
1           test

But RGZPFM FILE(ROB/ARLEEN) KEYFILE(ARLEENL ARLEENL) will fail with
various messages regarding duplicate keys.

Is this a bug?

Just one more reason why I like keys on my physical files.

Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin




Arleen Rissner
04/23/2002 11:15 AM


        To:     Rob Berendt/DEKKO@DEKKO
        cc:     Mark Plank/DEKKO@DEKKO
        Fax to:
        Subject:        SQL help

I'm (Mark P.) helping Arleen reset a field in a file and am trying to run
the following SQL command:
RUNSQL REQUEST('UPDATE CLIHMIF/IIML01 AS A
                      SET ICLAS = (SELECT ICLAS FROM ARLEEN/IIML01 AS B
                             WHERE B.IPROD = A.IPROD)')

It is failing  and giving the following message:
 Token / was not valid. Valid tokens: , FROM INTO.
 RUN QUERY command failed with SQLCODE -104.
 RUN QUERY command ended due to error.
 STRQMQRY command failed.
 Syntax error in SQL statement. See previous messages for details.

We reran the SQL and took out a library and had this result.
RUNSQL REQUEST('UPDATE IIML01 AS A
             SET ICLAS = (SELECT ICLAS FROM ARLEEN/IIML01 AS B
                   WHERE B.IPROD = A.IPROD)')
Unique fanout join failed.
Number of records retrieved is not correct.
Result of SELECT more than one row.
RUN QUERY command failed with SQLCODE -811.
RUN QUERY command ended due to error.
STRQMQRY command failed.
Syntax error in SQL statement. See previous messages for details.

 Any ideas?  The statement is based on one I've used successfully in the
past.  IIML01 is based on IIM (in libraries CLIHMIF and ARLEEN) and is
keyed by IPROD.