gkick wrote:The filtertable works perfectly, but the query for the report does not, no error just shows all records.
Any ideas what I am missing ?
Select
"MY_TABLE".*,
DECODE("MY_TABLE"."MY_BOOLEAN_COLUMN", TRUE, 'Yes', FALSE, 'No', 'All') as "MY_TRISTATE_COLUMN"
From "MY_TABLE"
HSQL 2.X Documentation - General Functions wrote:
DECODE
DECODE( <value expr main>, <value expr match 1>, <value expr result 1> [...,] [, <value expr default>] )
DECODE takes at least 3 arguments. The <value expr main> is compared with <value expr match 1> and if it matches, <value expr result 1> is returned. If there are additional pairs of <value expr match n> and <value expr result n>, comparison is repeated until a match is found the result is returned. If no match is found, the <value expr default> is returned if it is specified, otherwise NULL is returned. The type of the return value is a combination of the types of the <value expr result ... > arguments. (HyperSQL)
SELECT "qryOneOrOther"."lbList" "Client", "tblFilter"."pid", "tblProject"."projectType" "Project Type", "tblProject"."projectName" "Project Name", "tblProject"."projAddress" "Project Location", "tblProject"."pcity" "City", "tblProject"."pregion" "Region", "tblwbsc"."pcid", "tblwbsc"."cwbs" "Component", "tblpdetail"."item" "Item", "tblFilter"."Complete", "tblpdetail"."per" "Unit", "tblpdetail"."x_units" "Quantity", "tblpdetail"."labour" "Labour", "tblpdetail"."material" "Material", "tblpdetail"."rental" "Rental", "tblpdetail"."fees" "Fees", "tblpdetail"."fixed" "FC", "tblpdetail"."other" "Other", ( COALESCE ( "rental", 0 ) + COALESCE ( "labour", 0 ) + COALESCE ( "material", 0 ) + COALESCE ( "fees", 0 ) + COALESCE ( "other", 0 ) ) * "x_units" "SubTotal", "tblClient"."title" "Title", "cfname" || ' ' || "clname" "Client", "tblClient"."tel" "Telephone", "tblClient"."associate" "Associate", "tblpdetail"."dtid", "tblProject"."cid", "tblwbsc"."owner", "tblFilter"."Complete" FROM "tblwbsc" "tblwbsc", "tblProject" "tblProject", "tblpdetail" "tblpdetail", "tblClient" "tblClient", "qryOneOrOther" "qryOneOrOther", "tblFilter" "tblFilter" WHERE "tblwbsc"."pid" = "tblProject"."pid" AND "tblpdetail"."pcid" = "tblwbsc"."pcid" AND "tblProject"."cid" = "tblClient"."cid" AND "tblClient"."cid" = "qryOneOrOther"."cid" ORDER BY "tblpdetail"."dtid" ASC
SELECT <lots of columns>
FROM <several joined tables>, "Filter"
WHERE "Filter"."ID"=0 // allways limit the row set to one row
AND "Filter"."IntFilter" = "Table"."IntField" // Condition A returns True if the 2 integers are equal
AND ("Filter"."BoolFilter" = "Table"."BoolColumn" OR "Filter"."BoolFilter" IS NULL)
// Condition B returns True if the 2 boolean values match or if no boolean filter value is given
AND "Filter"."IntFilter" = "Table"."IntField" // Condition A returns True if the 2 integers are equal
AND "Filter"."BoolFilter" = "Table"."BoolColumn"
OR "Filter"."BoolFilter" IS NULL
gkick wrote:Firstly -How do you keep the indentation in queries as I always end up with unreadable monster queries hard to debug. Start off with indentation but its not saved ?
Secondly do the explicit joins in the subquery need to be all of the same name ?
The only examples showing how to do this all use some dummy table. However I recall some discussion of how to store the selection of box1 to a variable and use this variable as part of the datasource for box2
How do you keep the indentation in queries?
do the explicit joins in the subquery need to be all of the same name?
how to store the selection of box1 to a variable and use this variable as part of the datasource for box2
chrisb wrote:one last thing the query 'qF_FilterTable_tData' uses sub queries & explicit joins to performs the act of filtration, this enhances performance.
Global myListBoxSelection as Integer
Sub Main
'store a value in the global variable.
myListBoxSelection = box1Value
End Sub
Villeroy wrote
A mistake on your filter form is that the refresh button is attached to the filtering parent form
Edit: Sorry, the first upload was pointing to the wrong macro. |
So you would need multiples of maintainance forms to keep the boxes synchronised ?
Users browsing this forum: No registered users and 2 guests