Forums

Forum Home

Support

Foxfire! 8+ Support

query question...

query question

5/20/2008 8:36:27 PM

patricks

16 posts

query question

Hi there,

I still am not proficient enough with foxfire and foxpro to know how to express the query that I am trying to run using it. I know how to do it in postgres, for example. What I am looking for is a list of names from one table of everyone with an order in another table, but only if they dont have an order after a given date.

For example, I would do something like this in postgres:

select ml.name, ml.id from ml where ml.id = ml_order.id and ml_order.date < '04/01/2008' and ml.id not in (select ml.id from ml_order where ml_order.date >= '04/01/2008')

If someone could point me in the right direction, or give me an example of how to add in the sub query in fox fire I would be very thankful..

5/21/2008 3:34:03 AM

AndrewM

149 posts

www.aksel.com

Re: query question

This is an example of a subquery in Foxfire! and it can be a little tricky to set up.

What you need to do is create a Filter Only Data Item. Create a data item named Customers Not Used and click to the Filter tab.

Check the option at the top to be Filter Only.

In the Where condition specify

ml.id NOT IN (select ml.id from ml_order)

(I'm assuming that ML and ML_ORDER are your data item alias names in this case)

When you create your request to show these items, add the new filter Customers Not Used as a filter and set it to Yes.

When you run your report, Foxfire! will perform the subquery to provide you with results.

Does this help?

5/21/2008 1:51:08 PM

patricks

16 posts

Re: query question

AndrewM wrote
This is an example of a subquery in Foxfire! and it can be a little tricky to set up.
What you need to do is create a Filter Only Data Item. Create a data item named Customers Not Used and click to the Filter tab.
Check the option at the top to be Filter Only.
In the Where condition specify
ml.id NOT IN (select ml.id from ml_order)
(I'm assuming that ML and ML_ORDER are your data item alias names in this case)
When you create your request to show these items, add the new filter Customers Not Used as a filter and set it to Yes.
When you run your report, Foxfire! will perform the subquery to provide you with results.
Does this help?

It helps a bit, it shows me where I need to work on understanding how Foxfire does things, I think I need to become more proficient with the filters section to understand the instructions you are giving. Also for exampe you mention the filters tab, there isnt one where I think you are talking about, its a button to load a different dialog, but I assume thats the area you meant. Once I get a bit more understanding of how things are working I think this will give me the solution I need, if I have more problems I will come pester you again.

Thank you for your rapid reply :)

5/22/2008 3:47:57 AM

AndrewM

149 posts

www.aksel.com

Re: query question

Hi Patrick,

Sorry I wasn't clear. If you simply right-click on a Data Item in a Request and choose Edit, you don't get the full dictionary editing facility. Instead go directly to the Data Dictionary, find the item and click on the Filter button ( what I called a "tab" - go figure).

The only thing this DOESN'T give you is the ability to specify that date that you had mentioned so you could say any customer who didn't have sales since xx. I'll get you a further example of how to do that shortly.

Page 1 of 1

Support

Foxfire! 8+ Support

query question...