Subscribe to this thread
Home - General / All posts - User entered parameter values for a 9 query - is it possible?
Graeme

990 post(s)
#22-Nov-23 04:28

In M8 we can write a parameter query and when run, the user can enter the desired value(s) to direct the query results. From the M9 queries topic we're directed to Execute for parameter query examples. I have a table of user data including an address field which I can successfully query to generate a points drawing by modifying the SQL code in this illuminating contribution from Dimitri in this thread. An additional user field is "Date Sold" and for periodic updating purposes I'd like to be able to present the user with a dialogue box per M8 parameter query, in which they can enter a "sold date" after which the results will be restricted. I can do this inside the query by

 FROM [list] where [DATE SOLD] > #01/02/2023#;

The M9 parameter query examples suggest it is necessary to hard code the desired values into the queries calling the main query, as my hopeful "blank value" ## for a user entered value cannot be parsed

 EXECUTE WITH (@datesold datetime > ##) [q];

Am I missing something, or should this be a feature request?

Graeme

990 post(s)
#22-Nov-23 06:05

I Noticed "unexpected" results when entering English format date into the where clause

Where [Date Sold] > #day/month/year#

Even though the table column [Date Sold] was styled as "en-AU" - Australian English, the query returned results based on a US format reading of the entered date. Several threads have discussed the date format in M8 and M9 here and here for example. Will we be able use none-US style datetime formats in queries at some future point?

Sloots

676 post(s)
#23-Nov-23 10:19

If this is your query (named [Query]):

SELECT * FROM [Table] WHERE [date sold] > @datesold

then this executes the query with a parameter:

EXECUTE WITH (@datesold DATETIME = #1-1-2002#) [Query]


http://www.mppng.nl/manifold/pointlabeler

Graeme

990 post(s)
#24-Nov-23 05:40

Yes, thanks. I was just hoping there might be some way I could invoke a dialogue box to pop up for the user to populate per M8 parameter query behaviour, rather than directly edit the M9 query with values, but all good, will work around it.

Dimitri


7,400 post(s)
#24-Nov-23 06:28

Consider using a script to pop open the dialog, acquire the user-supplied parameter, and then execute the query from within the script.

Dimitri


7,400 post(s)
#24-Nov-23 06:52

Forgot to mention... here's the link in the API doc for how to launch a query from within a script: https://manifold.net/doc/api/scripts-net.html#database_run

nooramila2 post(s)
#12-Dec-23 09:54

Yes, it's possible to use a parameter query in M9 for user input. Use a parameter.

Ex...

@DateSold

, in your query

DECLARE @DateSold datetime

-- Code to get user input for @DateSold

SELECT * FROM [list] WHERE [DATE SOLD] > @DateSold;

Sloots

676 post(s)
#13-Dec-23 07:15

DECLARE is not part of the M9 SQL syntax. Use VALUE instead.


http://www.mppng.nl/manifold/pointlabeler

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.