Visual FoxPro SQL Queries - Select All Records From Parameterised View

Author

Select All Records From Parameterised View

Andy Trezise

2006-01-30, 8:25 pm

How is it possible to select all the records from a parameterised view
without having to supply default values?

For example I have a view that selects orders between two given dates which
I user a filter of Between ?dStart AND ?dEnd.

If I wanted to return ALL the rowns in the table how can I do so without
setting dStart to something like 01/01/1990 and dEnd to 31/12/2099. Also how
do I account for records which have no date entered?

--------

Similarly I want to create another parameterised view based on a logical
field. How can I open the view and ingore the parameter (i.e. return all
records regardless of true or false)?

Cindy Winegarden

2006-01-30, 8:25 pm

Hi Andy,

Are your views against Fox data or are they remote views? Why not just
create additional views without the criteria? Can you describe the base
tables and post the SQL you're using?

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com

"Andy Trezise" <andy@work.com> wrote in message
news:%23e%23ZNEfJGHA
.3260@TK2MSFTNGP11.phx.gbl...
> How is it possible to select all the records from a parameterised view
> without having to supply default values?

> Similarly I want to create another parameterised view based on a logical
> field. How can I open the view and ingore the parameter (i.e. return all
> records regardless of true or false)?

Andy Trezise

2006-01-31, 3:24 am

Hi Cindy

I am accessing a MYSQL backend database - just the same as SQL server really
is suppose.

This is the SQL statement behind the view:

SELECT Deliveries.* FROM deliveries Deliveries ;

WHERE Deliveries.delivery_number BETWEEN ?nStart AND ?nFinish;

AND ( Deliveries.delivery_date BETWEEN ?dStart AND ?dFinish;

AND Deliveries.driver LIKE ( ?cDriver ) );

AND Deliveries.picked = ?lPicked;

ORDER BY Deliveries.delivery_number

i.e. it only selects delveries between a certain range, a given date and for
a specified driver.

I can set the dates to 01/01/1990 and 31/12/2099 to include the whole range
but wondered if there was another way.

The other thing was how do I tell the view to ignore the lPicked value when
I want all records regardless of whether this is true or false?

Thanks in advance.

"Cindy Winegarden" < cindy_winegarden@msn
.com> wrote in message
news:OqNOWQgJGHA.1032@TK2MSFTNGP10.phx.gbl...
> Hi Andy,
>
> Are your views against Fox data or are they remote views? Why not just
> create additional views without the criteria? Can you describe the base
> tables and post the SQL you're using?
>
>
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@msn
.com www.cindywinegarden.com
>
>
> "Andy Trezise" <andy@work.com> wrote in message
> news:%23e%23ZNEfJGHA
.3260@TK2MSFTNGP11.phx.gbl...
>
>
>

Cindy Winegarden

2006-02-02, 11:24 am

Hi Andy,

If you're not updating the view I'd build a SQL Pass-through Select
statement on the fly, adding each piece of the Where statement depending on
whether the parameters were null/blank or not.

cSelect = "Select.... "
cFrom = "From .... "
cWhere = "Where 1 = 1 "
If Not IsNull(nStart) and Not IsNull(nFinish)
cWhere = cWhere + "And Deliveries.delivery_number BETWEEN " +
Transform(nStart) + " AND " + Transform(nFinish) + " "
EndIf
If Not IsNull (dStart) And Not IsNull(dFinish
cWhere = cWhere + "AND Deliveries.delivery_date BETWEEN " + ;
Transform(dStart) + " AND " + Transform(dFinish) + " "
EndIf
*-- Add more Where conditions here

cSQL = cSelect + cFrom + cWhere

You'll need to be careful to represent the dates in text in the way that
mySQL reads them.

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com

"Andy Trezise" <andy@work.com> wrote in message
news:%23PHgYDkJGHA.2040@TK2MSFTNGP14.phx.gbl...

> SELECT Deliveries.* FROM deliveries Deliveries ; WHERE
> Deliveries.delivery_number BETWEEN ?nStart AND ?nFinish; AND (
> Deliveries.delivery_date BETWEEN ?dStart AND ?dFinish;
> AND Deliveries.driver LIKE ( ?cDriver ) ); AND Deliveries.picked =
> ?lPicked; ORDER BY Deliveries.delivery_number
>
> i.e. it only selects delveries between a certain range, a given date and
> for a specified driver.
>
> I can set the dates to 01/01/1990 and 31/12/2099 to include the whole
> range but wondered if there was another way.
>
> The other thing was how do I tell the view to ignore the lPicked value
> when I want all records regardless of whether this is true or false?

Sponsored Links