Re: Select mytable name from mytable?

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.