× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



i've done this type of thing before (except with member names instead of
file names but the same thing would work) create the output table with an
additional field, do your insert with blanks in the column for the table
name, then do an update on the file name OR add the file name to the
insert. i generally pass the member name (or in your case file name) into
the program, use dynamic SQL to build the insert string and use EXECUTE
IMMEDIATE. that way you don't have to change the program every year.
since this sounds like a once a year type thing dynamic SQL would be the
way to go IMO


Thanks,
Tommy Holden



From: rob@xxxxxxxxx
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 03/16/2010 07:14 AM
Subject: Re: Select mytable name from mytable?
Sent by: midrange-l-bounces@xxxxxxxxxxxx



The obvious response is that you'll have to change the query in a years
time anyway, that is, if the table name is changing. For example, if the
query is:
select ...
from tbl2010

and next year it's:
select ...
from tbl2011

Then that's a change to the query.

While it would might work to your problem to have
select %filename, ...
from tbl2010
Think of how this would work in multiple table selections
select %filename(tbl2010), ...
from tbl2010 join tblmaster...
Granted, it would be easier with
select %filename(a), ...
from tbl2010 a join tblmaster b ...

I'll suggest that, since you have to change the query anyway to change the

table name it wouldn't be so hard to change this:
select "TBL2010" as tablename, ...
from tbl2010
with a search and replace. But if you are talking about a Query/400
object and you just want them to change the table involved and not have to

step down to the derived fields I can see your issue. If that's the case,

have them print the "definition" and the table involved will be in there.
I had an auditor that insisted on this so that she could tell that you
didn't omit records that she was interested in. And thank you not for
digging up foul memories of that woman.

I don't think you can do anything like
with T1 as (select system_table_name from systables where
system_table_name =? and system_table_schema='MYLIB')
select t1.system_table_name, a.region,a.sales
from t1.system_table_name a

by preparing that statement and executing it. I flat out don't think you
can use a variable for the "from".

Rob Berendt

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.