Page 1 of 3 123 LastLast
Results 1 to 15 of 34

Thread: SQL statement

  1. #1
    Join Date
    Nov 2009
    Posts
    29

    Unanswered: SQL statement

    What would the SQL SELECT statement be:

    displays all the query names contained in a database(Salina) excluding those queries that start with a "~" character?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    select my, column, list from mytable
    where acolumn not like "~*"

    MSysObjects is the table you want
    queries can be found by limiting the Type to 5
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2009
    Posts
    29

    SQL statement

    The statement that you gave me didn't work. Is that how it suppose to look?

  4. #4
    Join Date
    Jun 2009
    Posts
    6

    Re: SQL statement

    Select statement is used to select data from database.

    Syntax for selecting specific colum use:

    SELECT Columnname FROM tablename

    Syntax for selecting whole table

    SELECT * FROM tablename

  5. #5
    Join Date
    Jun 2009
    Posts
    6

    RE: SQL statement

    One more thing that i forgot to mention i.e one of the answer of your question.
    Syntax for selecting database(Salina) excluding those queries that start with a "~" character

    SELECT * FROM Salina WHERE queries<>~

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Beatlesbec View Post
    The statement that you gave me didn't work. Is that how it suppose to look?
    no I didn't 'give' you tge SQL, what I gave you was the table name, the column and value to identify whether that entry was a query

    I expected you to examine the MSysObjects table to find the name of the column containing the name of the object and find any other columns you might be interested in.

    incidentally its not good practice to retrieve all columns unless you need to, so I woudl strongly recommend that you do not use select * and instead use a comma seperated list to retrieve the columns you want. select * is lazy development, although it doesn't matter too much on this example it can be very very painful if you migrate onto larger server databases. select * may have a role when examining a table for the first time (as in this case), but not in the finished query

    thats why I normally use select my, column, list from my table suggestting you replace my, column,list with the columns you are actually interested in

    so thats....
    Code:
    select name,type from MSysObjects where Type = 5 and name not like "~*"
    which I reckon shouldn't have been an easy jump from post #2, and you will learn more by doing that bit of exploration yourself. you would have had the opportunity to work out what all the various values of TYPE are and so on. in short you would learn by doing, rather than being handed on a plate
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2009
    Posts
    29
    I tried the SQL statement that you gave me and it still didn't work.

    i did

    select name, type from MSysObjects where type =5 and name not like "~*"

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Since Healdem is offline, I copy/pasted that into an empty query and it worked perfectly, as expected. Where exactly are you trying this?
    Paul

  9. #9
    Join Date
    Nov 2009
    Posts
    29
    I'm trying this in a list box

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I tested as the row source of a listbox and it worked there as well. Maybe you should post a sample db?
    Paul

  11. #11
    Join Date
    Nov 2009
    Posts
    29
    DO you have Microsoft Office 2007? If so, maybe I can send you a snapshot of what I have?

  12. #12
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Sure, post it here and we'll take a look at it.
    Paul

  13. #13
    Join Date
    Nov 2009
    Posts
    29
    How do I post it here?

  14. #14
    Join Date
    Nov 2009
    Posts
    29
    Can I just email it to you?

  15. #15
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Clicking on Post Reply or Go Advanced will reveal a Manage Attachments button.
    Paul

Posting Permissions

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