OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[solved] Another SQL parameter query question

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
edwardrussia
General User
General User


Joined: 21 Oct 2010
Posts: 29

PostPosted: Wed Mar 09, 2011 12:00 pm    Post subject: [solved] Another SQL parameter query question Reply with quote

I had a problem about getting syntax errors. That was solved by the kind people on this forum. Now I've got a slightly different problem with the same query.

I've got one list box which gives the text parameter ":GRAMMAR" - this works fine, and changing the text box selection makes changes in the results [due to the first line in the query below]. All the other parameters are Boolean, and are decided by check boxes. However, all the rest of the query seems to have no effect on the results - changing check boxes does not change the results.

I tried making a query in direct SQL edit mode, and the query worked (if I substituted real values for the parameters). Changing values changed the results as I wanted.

I wonder if there is a special way to deal with Boolean parameters, or if I've missed something else.

If you can help me, I will be very grateful!

Quote:
SELECT * FROM "QueryGrammar"
WHERE ((:GRAMMAR="Grammar code") OR (:GRAMMAR IS NULL))

AND(
((:BEGINNER=TRUE) AND ("Beginner"=TRUE))
OR((:ELEMENTARY=TRUE) AND ("Elementary"=TRUE))
OR((:PREINT=TRUE) AND ("Pre-Intermediate"=TRUE))
OR((:INTERMEDIATE=TRUE) AND ("Intermediate"=TRUE))
OR((:UPPERINT=TRUE) AND ("Upper-Intermediate"=TRUE))
OR((:ADVANCED=TRUE) AND ("Advanced"=TRUE))
OR((:PROFICIENCY=TRUE) AND ("Proficiency"=TRUE))

OR(
(:BEGINNER!=TRUE)
AND(:ELEMENTARY!=TRUE)
AND(:PREINT!=TRUE)
AND(:INTERMEDIATE!=TRUE)
AND(:UPPERINT!=TRUE)
AND(:ADVANCED!=TRUE)
AND(:PROFICIENCY!=TRUE)
)
)


Last edited by edwardrussia on Thu Mar 10, 2011 12:36 pm; edited 1 time in total
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Mar 09, 2011 12:30 pm    Post subject: Reply with quote

http://user.services.openoffice.org/en/forum/download/file.php?id=8622
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
edwardrussia
General User
General User


Joined: 21 Oct 2010
Posts: 29

PostPosted: Wed Mar 09, 2011 1:26 pm    Post subject: Reply with quote

Thanks for the reply.

I have seen the file you attached. In fact, that was the file which showed me how to do what I wanted. However, it's not working for me yet.

In the example file, what is the purpose of the Query qFilterSet? I do not have such a query in my database. Is it important?

If I substitute "TRUE" for the relevant parameters in my SQL, the query works as I hope.

I would be most grateful for more ideas.

Is there a way to show the values of the parameters (e.g. by PRINTing them)
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Mar 09, 2011 2:01 pm    Post subject: Reply with quote

http://wiki.services.openoffice.org/wiki/Database
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Mar 10, 2011 12:55 am    Post subject: Reply with quote

Sorry, I forgot: http://dl.dropbox.com/u/10552709/FilterExamples.zip
My above linked boolean form is what the FilterExamples call "Power Filtering"
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
edwardrussia
General User
General User


Joined: 21 Oct 2010
Posts: 29

PostPosted: Thu Mar 10, 2011 2:34 am    Post subject: Reply with quote

Thanks for the link. I've looked at the link before, and I have made my form do what the examples do. However, I need something very slightly different (i.e. when the boxes are unchecked, they are ignored - and don't need to be false).

I think I've worked out where the problem is, and I wonder if you could help me, as googling has not helped so far

When a parameter is Boolean, should I use = or IS?

For example:

(:ELEMENTARY=TRUE)

or

(:ELEMENTARY IS TRUE)

and also

(:ELEMENTARY!=TRUE)

or

(:ELEMENTARY IS NOT TRUE)




I believe some of the predicates in my query are returning "FALSE" when they should be returning "TRUE", and probably because I've got = where there should be IS.

Thank you for your help.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Thu Mar 10, 2011 8:14 am    Post subject: Reply with quote

edwardrussia:

First, a Boolean Field can have three values:
  1. TRUE
  2. FALSE
  3. NULL ( this means UNDEFINED )
I would like to offer a suggestion.

Question: Why should any Boolean field have a value OTHER then . . . TRUE or FALSE ? ? ?
If it were me . . . I would first UPDATE all of the CURRENT values in the database table so ALL NULL values are replaced with FALSE . This way, you only have TRUE or FALSE in your current table.

For example ( change "MyTalbeName" to the name of your table . . . and . . . "MyBooleanField" to the name of the field in your Table ). This will make the CURRENT VALUES in your field either TRUE or FALSE ( no NULLS for CURRENT VALUES). Do this for each BOOLEAN FIELD:

From the Menu: Tools -> SQL...

Code:
UPDATE "MyTableName" SET "MyBooleanField" = COALASE("MyBoolenField", FALSE);


Next . . . to DEFAULT the BOOLEAN field to a value of FALSE when NOT explicitly entered ( so it would not be NULL or UNDEFINED ) . . . just use the following syntax, for each BOOLEAN FIELD in your table:

Code:
ALTER TABLE "MyTableName" ALTER COLUMN "MyBooleanField" SET DEFAULT FALSE;


Explanation: The above will say . . . when a row is created, if you do NOT assign a specific value to the field . . . it will DEFAULT to FALSE ( not NULL or UNDEFINED ).

Code:
ALTER TABLE "MyTableName" ALTER COLUMN "MyBooleanField" SET NOT NULL;


Explanation: The above SQL means, that for the above field, a value of NULL is not allowed. Smile

Now, your SQL when using the Base Parser . . .

Code:
WHERE "MyBooleanField" = :Enter_TRUE_OR_FALSE

This will PROMPT and expect a value of EITHER
  1. FALSE
  2. TRUE
  3. 0
  4. 1
This way, you NEVER have to check for NULL, only the values of TRUE and FALSE. Smile

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
edwardrussia
General User
General User


Joined: 21 Oct 2010
Posts: 29

PostPosted: Thu Mar 10, 2011 12:35 pm    Post subject: Reply with quote

Thanks for all your help.

I've finally worked out what was wrong. It seems that direct references to fields will accept values of TRUE or FALSE, whilst references to parameters only accept 0 or 1.

So

Doesn't work: SELECT * FROM "QueryGrammar" WHERE ((:BEGINNER=TRUE) AND ("Beginner"=TRUE))

Does work: SELECT * FROM "QueryGrammar" WHERE ((:BEGINNER=1) AND ("Beginner"=TRUE))

A bit weird really.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group