|
Marco, Given the example of use of the IN function, you CAN do what you want by making the selection column a compound value. This of course assumes that the results of the header query is saved in an outfile before running the detail query. Select * from d where where item = 'XX' and MODEL||ID in (select model||id from HdrQryTbl) However, you stated that there are no temp tables, so I'm not sure if HdrQryTbl even exists as a persistent table at the time you run the query.... If the Header query did not save its results to a table, or if you want both statements to be executed independently of each other, then you'll have to embed the header query string into the detail query... with h1 as (___insert_the_SELECT_string_generated_for_Header____) Select * from d inner join h1 on (d.model=h1.model and d.id=h1.id) Where item = 'XX' hth, Eric DeLong Sally Beauty Company MIS-Project Manager (BSG) 940-297-2863 or ext. 1863 -----Original Message----- From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Marco Facchinetti Sent: Thursday, November 10, 2005 7:24 AM To: Midrange Systems Technical Discussion Subject: Re: Sql help As usual I didn't explaine myself clearly... The two programs processing the parameters just produce a complete sql statement: select * from h where customer = 'YY'... I'd like to use the header as a subquery to select from details, as explained in the manual (SQL programming concepts): SELECT EMPNO, LASTNAME, JOB FROM CORPDATA.EMPLOYEE WHERE EMPNO IN (SELECT EMPNO FROM CORPDATA.EMPPROJACT WHERE PROJNO = 'MA2100') but what I don't know is how to use TWO fileds (Model and Id) or if it's even possible using as a subqry a select *. That means I can modify the details parser but I would like to keep the headers one unchanged since is frequently updated and it's not aware of the fact that I want to use the result as a subquery. Thanks again Marco --- "Hauser, Birgitta" <Birgitta.Hauser@xxxxxxxxxxx> wrote: > Hi Marco, > 7 > I'm not quite sure what you want to achieve. > The following example shows how to join the two files: > > Select h.*, d.* > From Header h jon Detail d on h.Model = d.Model and h.Id = d.Id > Where d.item = 'XX' and .... > And h.customer = 'YY' and .... > > Birgitta > > -----Ursprüngliche Nachricht----- > Von: midrange-l-bounces+birgitta.hauser=lp-gmbh.com@xxxxxxxxxxxx > [mailto:midrange-l-bounces+birgitta.hauser=lp-gmbh.com@xxxxxxxxxxxx] Im > Auftrag von Marco Facchinetti > Gesendet: Donnerstag, 10. November 2005 13:04 > An: Midrange Systems Technical Discussion > Betreff: Sql help > > Hi group, I need a little help in sql coding. > > I have two files, and Header (H) and Details (D) with join fields Model > and > Id. > > Since the users can browse them indipendently I devoleped programs to: > > - input/validate parameters > - paramter parser to sql select > - reader program to feed pages to front end presentation > - front end programs > > on both the files. > > Now I would like to add to the detail selections the headers selections. > > The parameters parser will produce: > > Detail: select * from d where item = 'XX'.... > > Header: select * from h where customer = 'YY'... > > How can code to make detail include only the records present in header > select without writing a new parser (a change in the detail is a must but > not a copy of header paraser)? > > Has to be SQLRPGLE and without temp files. > > Thanks fro your help > Marco > > > > > __________________________________ > Yahoo! Mail - PC Magazine Editors' Choice 2005 > http://mail.yahoo.com > -- > 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. > > __________________________________ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com
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.