RE: ** SQL Insert Question **

[Ian Scotson <IanS@xxxxxxxxxxxx>]

Thks,

It sort of got me further, and gave me what i wanted,
but not what actually i needed ( there's a song in there somewhere..:-))

I needed to do a SETLL type of thing to only match a
header record - Read equal on partial key.

I only need to select/insert one CUSPD record for every
distinct 1st key ( of 5 keys total) in ARTMS.

The below gave me the correct no of key records, eg:
690 records out of 4800 total in ARTMS00F

SELECT  distinct zcdke1
FROM IAN.CUSPD00F a, pilotshr.ARTMS00F b
         WHERE    BRECTY = ZRECTY AND
                  BCDKE1 = ZCDKE1

CUDPD00F has about 1480 records, and i want to
write those that match the 690 from above.

In other words, end up with 690 records of * from CUSPD00F

I tried one way ( using IN ) and ended up with >60000 records
( and still going..) using.

select *
from ian.cuspd00f
where bcdke1 in

above the subselect.
====================

Tried below

SELECT DISTINCT b.zcdke1, a.BPRICE,a.BDISC1,
a.BDISC2,b.ZRECTY
 FROM ian.CUSPD00F a,
pilotshr.ARTMS00F b
 WHERE a.BRECTY = b.ZRECTY AND a.BCDKE1 = b.ZCDKE1

And gets me somewhere, but im not sure if DISTINCT only operates
on b.zcdke1, or ALL selected fields.

Or i am still way off??

Any ideas??


Ian.




-----Original Message-----
From: Finucci Domenico [mailto:Domenico.Finucci@Fiditalia.it]
Sent: Thursday, 9 May 2002 18:28
To: 'midrange-l@midrange.com'
Subject: R: ** SQL Insert Question **


Hello Ian. Try
INSERT INTO IAN.CUSPD00F2
SELECT a.* FROM IAN.CUSPD00F a, IAN.ARTMS00F2 b
         WHERE    BCDKE1 = ZCDKE1 AND
                      BCDKE2 = ZCDKE2 AND
                      BCDKE3 = ZCDKE3 AND
                      BCDKE4 = ZCDKE4 AND
                      BCDKE5 = ZCDKE5

where "a.*" means 'all the field of CUSPDF00F table'

Sincerely

Domenico Finucci
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.