Re: Select mytable name from mytable?

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

David

One of the easiest ways to handle this is to use a QM query. You speak 
of the need to change file name each year. With a QM query you can very 
easily have a variable for the file name, or any other part of the query 
statement.

Reading this post will take more time than it takes to do it, perhaps.   <g>

First, to put in your filename, you use a constant, or literal. That's 
easy enough. It'd be the same as the file name in the file list.

So here is the source for a QM query that does this and then gives you 
all the fields from that file -

SELECT &QT&FILENAME&QT AS MYFILE, &FILENAME.* FROM &FILENAME

Ampersands (&) mark substitution variables. I use &QT for the single 
quote, since it is otherwise such a bother to work with. The other 
variable here is &FILENAME.

To create the query, you have a couple options.

1. Run STRQM if you have it, change to the library where the query 
should go, press F19 (I think - or F17) to change the mode from PROMPT 
to SQL, take option 1 to create a new query, then enter the statement 
and save it as you exit.
2. CRTSRCPF QQMQRYSRC RCDLEN(91) in your library and add a member, then 
put that statement into it. Then run CRTQMQRY against that member.

To run the query, use STRQMQRY. You set the variables there, as in this 
example-

STRQMQRY YOURQUERY SETVAR(('QT' '''') ('FILENAME' 'FILE2010'))

This ends up running this statement -

SELECT 'FILE2010' AS MYFILE, FILE2010.* FROM &FILE2010

You can create a command with a 10-character parameter for the file 
name, as a convenient way to run the query. The CPP would be a CL that 
does the STRQMQRY command.

Very simple, very clean, no embedded SQL, piece of cake, as we say here 
in the States.

HTH
Vern

David FOXWELL wrote:
> Howdy all,
>
>
> I'm running a complicated query on two tables with UNION all and I'd like the table names to show up in the result to compare them. Is it possible to have the name of the table show up in a select?
> I don't want to hard code the name as I  must save this query for use in a year's time.  The table names change each year.
>
> Thanks.
>
>