Forums

Forum Home

Support

Foxfire! 8+ Support

SQL generation ...

SQL generation error

5/28/2008 8:46:57 AM

patricks

16 posts

SQL generation error

running build 8.1.560
we have a data field in the orders table that is COMM, and one that is COMM_DATE, I added "Orders Comm" which is the alias for the COMM field to the data selection section. I added a filter where "Orders comm Is Like Y" (data in that field is Y, N and H). The SQL generated is as follows with it mistakenly grabbing comm_date instead of comm for the filter:

SELECT   ALL;
        Orders.DATE                    AS DATE_O,;
        Orders.ORD_NO                  AS ORD_NO_O,;
        Orders.AMOUNT,;
        Orders.COMM,;
        Orders.ST                      AS ST_O,;
        Orders.CITY                    AS CITY_O,;
        Orders.SA_NAME                 AS SA_NAME_O,;
        Orders.SA_SS                   AS SA_SS_O;
FROM   "ML-ORDER" Orders;
INTO   CURSOR JMAYOR_2;
WHERE   (ORDERS.DATE BETWEEN {^2008-05-29} AND {^2008-05-31};
     AND ORDERS.ST="CA";
     AND ORDERS.COMM_DATE="Y")

5/28/2008 8:53:16 AM

patricks

16 posts

Re: SQL generation error

I have tried changing the alias in the data definition of "Orders Comm Date" to "Orders Comm_Date" and "Orders CommDate" with no success. We also have an "orders date" field, so I dont want to run the risk of a mispicked SQL on the date field because it is used more ofthen than the Comm field.

5/28/2008 10:01:47 AM

AndrewM

149 posts

www.aksel.com

Re: SQL generation error

Hey Patrick.

To confirm , you have two data items:

Orders Comm
Orders Comm Date

When you choose Orders Comm as a filter data item, it attempts to use Orders Comm Date as the filter value.

Is that correct?

5/28/2008 10:39:25 AM

patricks

16 posts

Re: SQL generation error

Hi Andrew,

Yes, that is exactly what is happening. If I edit the SQL and correct it, it works fine, but the automatically generated SQL insists on the misplaced name.

the file is ml-order.dbf, aliased to Orders
the fields in the file are comm and comm_date, aliased to comm and comm date respectively

I have tried changing the comm date to commdate and comm_date with no success. I have even rebuilt the data dictionary (for a different reason) and it is still doing it.

5/29/2008 5:42:36 AM

AndrewM

149 posts

www.aksel.com

Re: SQL generation error

Can you please send your xxxITEM table to support@foxfirereporting.com so I can take a look at it with the words Foxfire Filter issue in the message?

I just tried to recreate this scenario using our sample data set and was unable to.

Thanks

Page 1 of 2

Support

Foxfire! 8+ Support

SQL generation ...