Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2007
    Posts
    10

    Unanswered: Creating a drop down menu in SQL

    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!
    Last edited by FarrukhZ; 06-27-07 at 11:31.

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    What environment are you using for issuing the query? The query you posted isn't in a standard PSQL SQL format.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Jun 2007
    Posts
    10
    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.

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    A screenshot might help. What's "CMMS"?
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  5. #5
    Join Date
    Jun 2007
    Posts
    10
    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.

  6. #6
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  7. #7
    Join Date
    Jun 2007
    Posts
    10
    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.

  8. #8
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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'
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  9. #9
    Join Date
    Jun 2007
    Posts
    10
    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?

  10. #10
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •