RE: Sql help

["DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>]

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