|
midrange.com
Thank you'se Eric, Colin, and Rob.
I looked at nodename, all I got from it was the name of our as/400 and
only when I used the logical (index), not the physical file.
I don't have time to spend on this, but in my ignorance I was hoping there
might be some (scalar?) palatable/quick "keyword" like below for this--
SELECT code, id, seq, startpos, LIBNAME_THAT_FILE_IS_IN() AS Library
FROM sf
or perhaps
SELECT code, id, seq, startpos, TABLE_CONTAINER() AS Library
FROM sf
I do appreciate the help.
John B.
And, hey... I cracked open the online SQL programming manual and read a bit
:)
----------------------------------------------------------------------
message: 1
date: Tue, 17 Feb 2004 10:03:49 -0600
from: "DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>
subject: RE: SQL question - Library that file resides in as a column
in SE LECT stmt output.
You could construct the SQL statement dynamically......
Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-898-7863 or ext. 1863
-----Original Message-----
From: Rusling, John B. (Alliance) [mailto:jbrusling@xxxxxxxxxxxxxxx]
Sent: Tuesday, February 17, 2004 9:49 AM
To: 'rpg400-l@xxxxxxxxxxxx'
Subject: SQL question - Library that file resides in as a column in
SELECT stmt output.
I have a SELECT statement, looks like this -
SELECT code, id, seq, startpos
FROM sf
....+....1....+....2....+....3....+.
Code ID Sequence Start Position
SR10 IT 0 1
SR10 IT 0 36
SR10 IT 0 37
SR10 IT 0 38
SR10 IT 0 39
SR10 IT 0 40
******** End of data ********
I'd like the library that the file (sf) resides in to be a column in
the output of the SELECT statement.
....+....1....+....2....+....3....+....4....+....5....+
Code ID Sequence Start Position Library
SR10 IT 0 1 MYLIB
SR10 IT 0 36 MYLIB
SR10 IT 0 37 MYLIB
SR10 IT 0 38 MYLIB
SR10 IT 0 39 MYLIB
SR10 IT 0 40 MYLIB
******** End of data ********
Is there a Function/Column keyword or something/someway to do this?
Reason for this is -
I have an file that resides in multiple libraries.
(identical file, same fields etc.)
I'd like to check some of the field values for validity (in all the
libraries the file exists.)
I thought I might use "UNION" to put them all together and test them all at
once but this wouldn't do me any good w/o the "originating" library.
tfahmf John B.
_______________________________________________
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.
------------------------------
message: 2
date: Tue, 17 Feb 2004 16:09:43 -0000
from: Colin Williams <colin.williams@xxxxxxxxxxxx>
subject: RE: SQL question - Library that file resides in as a column
in SE LECT stmt output.
Or create an external UDF in RPG to pick up the library
Cheers
Colin.W
-----Original Message-----
From: DeLong, Eric [mailto:EDeLong@xxxxxxxxxxxxxxx]
Sent: 17 February 2004 16:04
To: 'RPG programming on the AS400 / iSeries'
Subject: RE: SQL question - Library that file resides in as a column in SE
LECT stmt output.
You could construct the SQL statement dynamically......
Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-898-7863 or ext. 1863
-----Original Message-----
From: Rusling, John B. (Alliance) [mailto:jbrusling@xxxxxxxxxxxxxxx]
Sent: Tuesday, February 17, 2004 9:49 AM
To: 'rpg400-l@xxxxxxxxxxxx'
Subject: SQL question - Library that file resides in as a column in SELECT
stmt output.
I have a SELECT statement, looks like this -
SELECT code, id, seq, startpos
FROM sf
....+....1....+....2....+....3....+.
Code ID Sequence Start Position
SR10 IT 0 1
SR10 IT 0 36
SR10 IT 0 37
SR10 IT 0 38
SR10 IT 0 39
SR10 IT 0 40
******** End of data ********
I'd like the library that the file (sf) resides in to be a column in the
output of the SELECT statement.
....+....1....+....2....+....3....+....4....+....5....+
Code ID Sequence Start Position Library
SR10 IT 0 1 MYLIB
SR10 IT 0 36 MYLIB
SR10 IT 0 37 MYLIB
SR10 IT 0 38 MYLIB
SR10 IT 0 39 MYLIB
SR10 IT 0 40 MYLIB
******** End of data ********
Is there a Function/Column keyword or something/someway to do this?
Reason for this is -
I have an file that resides in multiple libraries.
(identical file, same fields etc.)
I'd like to check some of the field values for validity (in all the
libraries the file exists.)
I thought I might use "UNION" to put them all together and test them all at
once but this wouldn't do me any good w/o the "originating" library.
tfahmf John B.
_______________________________________________
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.
This e-mail has been sent by a company of Bertram Group Ltd, whose
registered office is The Nest, Rosary Road Norwich NR1 1TF.
This message, and any attachments, are intended solely for the addressee and
may contain privileged or confidential information. If you are not the
intended recipient, any disclosure, copying, distribution or any action
taken or omitted to be taken in reliance on it, is prohibited and may be
unlawful. If you believe that you have received this email in error, please
contact the sender immediately. Opinions, conclusions and statements of
intent in this e-mail are those of the sender and will not bind a Bertram
Group Ltd company unless confirmed in writing by a director independently of
this message.
Although we have taken steps to ensure that this email and any attachments
are free from any virus, we advise that in keeping with good computing
practice the recipient should ensure they are actually virus free.
------------------------------
message: 3
date: Tue, 17 Feb 2004 11:32:03 -0500
from: rob@xxxxxxxxx
subject: RE: SQL question - Library that file resides in as a column
in SE LECT stmt output.
You could do a UDF. For more details check out:
http://faq.midrange.com/index.pl?_highlightWords=udf&file=185
SELECT LOC, COMP, current schema FROM aaakey
....+....1....+....2....+....3....+..
My, Loc CO CURRENT SCHEMA
#
81.001 81 *LIBL
81.002 81 *LIBL
a 0 *LIBL
01.999 1 *LIBL
******** End of data ********
Didn't have any luck with the following:
SELECT LOC, COMP, nodename(x) FROM aaakey x
....+....1....+....2....+....3....+....4.
My, Loc CO NODENAME ( X )
#
81.001 81
81.002 81
a 0
01.999 1
******** End of data ********
Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
------------------------------
message: 4
date: Tue, 17 Feb 2004 12:02:21 -0500
from: rob@xxxxxxxxx
subject: RE: SQL question - Library that file resides in as a column
in SE LECT stmt output.
Now, thinking of performance, (isn't that always the last thing we do in
sql? :-)), you wish to consider not using a UDF, nor a SQL BIF on every
row. Perhaps at first cycle time or some such animal you could determine
that library name.
For example:
D aaakey e ds extname(aaakey)
C/EXEC SQL
C+ Set Option
C+ Naming = *Sys,
C+ Commit = *None,
C+ UsrPrf = *User,
C+ DynUsrPrf = *User,
C+ Datfmt = *iso,
C+ CloSqlCsr = *EndMod
C/END-EXEC
C/EXEC SQL
C+ Declare C1 cursor for
C+ SELECT LOC, COMP
C+ FROM aaakey
C/END-EXEC
C/EXEC SQL
C+ Open C1
C/END-EXEC
/free
LibName=%subst(sqlerm:x:y);
/end-free
// your fetch loop construct here...
C/EXEC SQL
C+ Fetch C1 into :loc, :comp
C/END-EXEC
C/EXEC SQL
C+ Close C1
C/END-EXEC
/free
*inlr=*on;
return;
/end-free
Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.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.
