Creating a drop down menu in SQL

FarrukhZ · #1 (**permalink**) 06-27-07, 08:43

Hello Everyone,

I am currently using Pervasive.SQL V8.7 to work on a database system. This is my first time working with SQL and I'm not all the famaliar with the commands yet but I'm learning.

Right now I'm haveing a problem creating a drop down menu for one of my queries. Just to give you an idea following is the code that I have thus far.

Code:

SELECT
  WorkOrd.szID AS "ID" ,
  PersGroup.szID AS "PersGroup" ,
  WorkOrd.szDescription AS "Description" ,
  WorkOrd.nProgress AS "Progress" ,
  ListWOState.szID AS "State" ,
  ListWOState.szDescription AS "StateDescription" ,
  WorkOrd.szBatchID AS "BatchID" ,
  WorkOrd.dateScheduled AS "DateScheduled" ,
  WorkOrd.szNote AS "Note" 
FROM
  ((((WorkOrd INNER JOIN ListWOState ON WorkOrd.gWOState = ListWOState.gPK)
  INNER JOIN WorkOrd_PersHier ON WorkOrd.gPK = WorkOrd_PersHier.gWorkOrd)
  INNER JOIN PersHier ON WorkOrd_PersHier.gPersHier = PersHier.gPK)
  INNER JOIN PersGroup ON PersHier.gParent = PersGroup.gPK) 
WHERE
  (PersGroup.szID =<#Personnel Group$txt!None#>) AND
  (WorkOrd.nProgress <#Progress$PROG_!All#>) AND
  (WorkOrd.dateScheduled >= <#Date Start$DTE#>) AND
  (WorkOrd.dateScheduled <= <#Date End$DTE#>) 
ORDER BY
  WorkOrd.szID ASC

Specifically the code that I'm having a probem with is the one that I have in Bold above. Right now I have it set so that user is able to enter a txt value for the PersGroup and then I take that text value and match it with what is on the database system and display only the matching rows. To do this I have set the parameter

Code:

PersGroup.szID =<#Personnel Group$txt!None#>

Although what I would like to do is set up a drop down menu that lets the user pick a specific PersGroup from a list of all the PersGroups as well as has an ALL selection build into the drop down menu that will allow the user to select all PersGroups at the same time if necessary.

Someone who was working on this database before me accomplished this task for the nProgress column by using the code

Code:

WorkOrd.nProgress <#Progress$PROG_!All#>

So now when I run the query it gives me a drop down menu for Progress and lets me select one or all of the available progresses, depending on what I want. However, I can't seem to figure out how that command works or how I can duplicate its effect on the PersGroup column. Could someone please help me out here.

Thanks!

mirtheil · #2 (**permalink**) 06-27-07, 16:14

What environment are you using for issuing the query? The query you posted isn't in a standard PSQL SQL format.

FarrukhZ · #3 (**permalink**) 06-27-07, 16:27

Quote:

Originally Posted by mirtheil

What environment are you using for issuing the query? The query you posted isn't in a standard PSQL SQL format.

I'm not sure... how do I find that out. I am new at PSQL. I am doing this for a CMMS Database. When I go into the SQL view for the query that is the code that it gives me. I can take a screen shot of the design view too if you would like to see that.

mirtheil · #4 (**permalink**) 06-27-07, 17:24

A screenshot might help. What's "CMMS"?

FarrukhZ · #5 (**permalink**) 06-28-07, 08:21

Ok, here's a screenshot of the Database that I'm working with. This is what query that I'm trying to create looks like in design view

Then if you click the SQL button near the top let in the window above it gives me the SQL code that I pasted in my initial post. However there are certain things that can't be done in design view. Like for another query that I created I needed to write IF statements so I had to made it a SQL only query and just work with the SQL code, which is why I didn't bother putting in the design view initially.

mirtheil · #6 (**permalink**) 06-28-07, 09:09

Okay, so you are using a product called "MAINTelligence". You probably want to ask them how to create a dropdown.
Creating a dropdown menu is not a standard SQL syntax.

FarrukhZ · #7 (**permalink**) 06-28-07, 11:08

What about the creating a txt field command.

Code:

PersGroup.szID =<#Personnel Group$txt!None#>

Is that standard syntax? cuz I can create that in any query no problem but not the drop down menu.

mirtheil · #8 (**permalink**) 06-28-07, 11:18

The "<#Personnel Group$txt!None#>" syntax is specifc to the MAINTelligence program. Standard SQL syntax does not have the concept of dropdown menus or text fields. Standard SQL returns a resultset that is displayed based on the tool being used. A standard SQL statement is something like:
SELECT field1, field2, field3 FROM table WHERE field1 = 'somevalue'

FarrukhZ · #9 (**permalink**) 06-28-07, 12:01

Ok, can you still GROUP objects in standard SQL and calculate things like SUM, AVG, MIN and MAX or write CONDITIONAL or IF statements. Or would that also be something that would be also be done through the developer?

mirtheil · #10 (**permalink**) 06-28-07, 14:13

Yes, GROUP BY statements can be used to group data sets.
Here's the page from the PSQL syntax that talks about GROUP BY:
http://www.pervasive.com/library/doc...ref-04-48.html

Internet Services

Web Development

Digital Marketing

Consumer Tech