|
Ah-Ha That's it exactly. Thanks Rob Alan Shore NBTY, Inc (631) 244-2000 ext. 5019 AShore@xxxxxxxx rpg400-l-bounces@xxxxxxxxxxxx wrote on 03/01/2007 09:16:27 AM:
Alan, You mean like this?CREATE TABLE QTEMP/FILE01 (MYCHAR CHAR (10 ) NOT NULL WITH DEFAULT,MYNBR DEC (5 , 2) NOT NULL WITH DEFAULT) Table FILE01 created in QTEMP.CREATE TABLE QTEMP/FILE02 (MYCHAR CHAR ( 10) NOT NULL WITH DEFAULT,MYNBR DEC ( 5, 2) NOT NULL WITH DEFAULT) Table FILE02 created in QTEMP.INSERT INTO QTEMP/FILE01 VALUES('A', 1.2)1 rows inserted in FILE01 in QTEMP.INSERT INTO QTEMP/FILE02 VALUES('B', 2.3)1 rows inserted in FILE02 in QTEMP.CREATE VIEW QTEMP/FILE0102 AS(SELECT * FROM FILE01 UNION SELECT * FROM FILE02 ) View FILE0102 created in QTEMP.SELECT * FROM FILE0102....+....1....+... MYCHAR MYNBR A 1.20 B 2.30 Rob Berendt -- Group Dekko Services, LLC Dept 01.073 PO Box 2000 Dock 108 6928N 400E Kendallville, IN 46755 http://www.dekko.com Alan Shore <AlanShore@xxxxxxxx> Sent by: rpg400-l-bounces@xxxxxxxxxxxx 02/28/2007 05:13 PM Please respond to RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx> To RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx> cc Fax to Subject RE: imbedded sql sytax question The "problem" (please take note of quotes) with this answer is that there is an implicit assumption that there's a record in file1 that corresponds to a record in file2 and vice-versa. One of the good things that most mainframes had (IBM, UNIVAC, BURROUGHS, UNISYS, ICL etc) was that there was a way to pseudo concatenate like
files
together (through JCL, WFL etc) so that 2 separate, but like files,
looked
like it was in fact one larger file. Is there a way of doing this in SQL? Alan Shore NBTY, Inc (631) 244-2000 ext. 5019 AShore@xxxxxxxx rpg400-l-bounces@xxxxxxxxxxxx wrote on 02/28/2007 04:58:20 PM:Easy, suppose your select statement looks like: select a.field1, a.field2, b.field3, b.field4, date(b.charDate) asTheDatefrom thisFile A join ThatFile B on a.field1 = b.field26 where a.field13>13000 Now take that same select statement and prefix it with one line Create view mylib/myfile as ( select a.field1, a.field2, b.field3, b.field4, date(b.charDate) asTheDatefrom thisFile A join ThatFile B on a.field1 = b.field26 where a.field13>13000) Rob Berendt -- Group Dekko Services, LLC Dept 01.073 PO Box 2000 Dock 108 6928N 400E Kendallville, IN 46755 http://www.dekko.com "tim" <tim2006@xxxxxxxxxxx> Sent by: rpg400-l-bounces@xxxxxxxxxxxx 02/28/2007 04:40 PM Please respond to RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx> To "'RPG programming on the AS400 / iSeries'" <rpg400-l@xxxxxxxxxxxx> cc Fax to Subject RE: imbedded sql sytax question Ok, Can you give me an example of the CREATE VIEW? Hopefully not-hosed... -----Original Message----- From: rpg400-l-bounces@xxxxxxxxxxxx[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of rob@xxxxxxxxx Sent: Wednesday, February 28, 2007 4:34 PM To: RPG programming on the AS400 / iSeries Subject: Re: imbedded sql sytax question Do you do this kind of join a lot? If so, then, outside of yourprogram,do a CREATE VIEW and do the join in there. That will then have all the fields you might want in your datastructure to fetch into. Otherwise, you're hosed. Rob Berendt -- Group Dekko Services, LLC Dept 01.073 PO Box 2000 Dock 108 6928N 400E Kendallville, IN 46755 http://www.dekko.com "tim" <tim2006@xxxxxxxxxxx> Sent by: rpg400-l-bounces@xxxxxxxxxxxx 02/28/2007 04:25 PM Please respond to RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx> To "Rpg Forumn" <rpg400-l@xxxxxxxxxxxx> cc Fax to Subject imbedded sql sytax question Hi All, I have a select statement as follows: Select * from File1 where phonenumber = '2015551212'; This works great, but now I have another file (File2 or argument sake) that contains additional phone numbers. File1 and File2 can be joined by clientnumber. My code is as follows exec sql set option datfmt=*usa; exec sql prepare s2 from :dynstm; exec sql declare c2 cursor for s2; exec sql open c2; exec sql fetch c2 into :thefileds; dow sqlcod = 0; Is there a way for me to join these 2 files together (File1 & File2)
and
still be able to use the "thefileds" on my fetch. Hope this makes some kind of sense. Tim -- This is the RPG programming on the AS400 / iSeries (RPG400-L) mailinglistTo post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/rpg400-l or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l. -- This is the RPG programming on the AS400 / iSeries (RPG400-L) mailinglistTo post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/rpg400-l or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l. -- This is the RPG programming on the AS400 / iSeries (RPG400-L) mailinglistTo post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/rpg400-l or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l. -- This is the RPG programming on the AS400 / iSeries (RPG400-L) mailinglistTo post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/rpg400-l or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l.-- This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/rpg400-l or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l. -- This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/rpg400-l or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.