Field alias problem in select statement

Post Reply
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Field alias problem in select statement

Post by barry.marcus »

In my database, I have the two tables, project and filter, defined as follows:

project fields are:
id
name

filter fields are:
id
project_id
label
PIphrase
PIterm
SIphrase
SIterm
EXphrase
EXterm

Here is the code that is causing the problem:

<$field="PIphrase" "PIterm" "SIphrase" "SIterm" "EXphrase" "EXterm">
<loop $field>
<strfmt "select project.name thisProject, label, %s thisField from filter, project where filter.id = '%s' and filter.project_id = project.id" $field $filterId>
<$qString=$ret>
<sql ROW $qString>
<!-- Do stuff with returned values for current $field here -->
</sql>
</loop>

The value of filterId is passed into the function.

The problem seems to be with the alias thisField. I get the following error on the sql statement (which is at line 89 in the code):

115 2010-10-19 09:17:54 [phrases]:89: Field thisField non-existent
115 2010-10-19 09:17:54 [phrases]:89: Field non-existent or type error in thisField
000 2010-10-19 09:17:54 [phrases]:89: SQLPrepare() failed with -1 in the function prepntexis

I suppose I could pull all of the fields for a given filter.id in a single SELECT statement rather than iterating the fields and running a separate SELECT statement for each as I am doing. I admit that this would probably even be more efficient. However, right now the code looks like this, and I'd like to understand why this generates the errors it does. I don't see what's wrong with it, but it's as if Texis is looking for a field named "thisField", when I want this to be a "constant" alias for whatever field the SQL statement happens to be pulling (so as to simplify the code within the SQL loop.

My Texis version is:

Commercial Version 5.01.1268856485 20100317 (i686-intel-winnt-64-32)

Thanks for the help.
User avatar
John
Site Admin
Posts: 2597
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

Field alias problem in select statement

Post by John »

Do you get the message once, or once for each $field?

Have you printed $qString to make sure it is correct?

You may also want to use filter.%s thisField to avoid issues if there are ambiguous field names, although I don't see that as an issue in the code you have.
John Turnbull
Thunderstone Software
User avatar
mark
Site Admin
Posts: 5513
Joined: Tue Apr 25, 2000 6:56 pm

Field alias problem in select statement

Post by mark »

Try tracesql to see the actual SQL being executed.
Post Reply