Please proof my SQL Select Statement RSS

• • Reply
  

  Cincinnati_L...

  Member

  11 Points

  25 Posts

  Please proof my SQL Select Statement

  Jul 21, 2010 05:09 PM|LINK

  Here's my SQL Statement

  
  

  strSelect = "Select lastname, firstname, midinitial AS NameFull, casefind from"
  strSelect = strSelect & " (SELECT tabVeteran.lastname, tabVeteran.firstname, tabVeteran.midinitial, tabVeteran.casefind"
  strSelect = strSelect & " FROM tabVeteran"
  strSelect = strSelect & " WHERE tabVeteran.lastname Like " & strSearch
  strSelect = strSelect & " UNION"
  strSelect = strSelect & " SELECT tabVeteran.lastname, tabVeteran.firstname, tabVeteran.midinitial, tabVeteran.casefind"
  strSelect = strSelect & " FROM tabVeteran"
  strSelect = strSelect & " WHERE tabVeteran.firstname Like " & strSearch & " ORDER BY NameFull ASC);"

  
  Unfortunately, I get the following error message:

  Message="The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

  
  

  Thank you
  

  SQL 2008 EE select statement

  regards,
  -Cincinnati Lin :)
  • Moved by anas on Jul 21, 2010 07:06 PM
• • Reply
  

  jackym

  Participant

  872 Points

  141 Posts

  Re: Please proof my SQL Select Statement

  Jul 21, 2010 07:19 PM|LINK

  1. strSelect = "Select lastname, firstname, midinitial AS NameFull, casefind from"  
  2. strSelect = strSelect & " (SELECT tabVeteran.lastname, tabVeteran.firstname, tabVeteran.midinitial, tabVeteran.casefind"  
  3. strSelect = strSelect & " FROM tabVeteran"  
  4. strSelect = strSelect & " WHERE tabVeteran.lastname Like " & strSearch  
  5. strSelect = strSelect & " UNION"  
  6. strSelect = strSelect & " SELECT tabVeteran.lastname, tabVeteran.firstname, tabVeteran.midinitial, tabVeteran.casefind"  
  7. strSelect = strSelect & " FROM tabVeteran"  
  8. strSelect = strSelect & " WHERE tabVeteran.firstname Like " & strSearch & ") as t ORDER BY NameFull ASC"  

  
  

  Here you go. the change is at the line 08

  sql

  Please don't forget to mark post as answer
  • Edited by jackym on Jul 21, 2010 07:20 PM
  • Edited by jackym on Jul 21, 2010 07:21 PM
  • Edited by jackym on Jul 21, 2010 07:21 PM
  • Marked as answer by cincinnati_lin on Jul 22, 2010 10:06 AM
• • Reply
  

  snop222

  Member

  360 Points

  81 Posts

  Re: Please proof my SQL Select Statement

  Jul 22, 2010 02:17 AM|LINK

  I think the problem is the "ORDER BY NameFull ASC". Because the "NameFull" is not refered in sub-SELECT clause. It should be "ORDER BY midinitial ASC"

  In your SELECT statement, it do "UNION" for the same tabel only for different "WHERE". So, actually you could use single SELECT, such as :

  strSelect = "SELECT lastname, firstname, midinitial AS NameFull, casefind from"
  strSelect = strSelect & " FROM tabVeteran"
  strSelect = strSelect & " WHERE lastname Like " & strSearch
  strSelect = strSelect & " OR firstname Like " & strSearch
  strSelect = strSelect & " ORDER BY midinitial ASC;"

  
  If you want to "repeat" the rows which both matches these two "WHERE" codition, then you should use "UNION ALL". Such as : 

  strSelect = "Select lastname, firstname, midinitial AS NameFull, casefind from"  
  strSelect = strSelect & " (SELECT lastname, firstname, midinitial, casefind"  
  strSelect = strSelect & " FROM tabVeteran"  
  strSelect = strSelect & " WHERE lastname Like " & strSearch   
  strSelect = strSelect & " UNION ALL"  
  strSelect = strSelect & " SELECT lastname, firstname, midinitial, casefind"  
  strSelect = strSelect & " FROM tabVeteran"  
  strSelect = strSelect & " WHERE firstname Like " & strSearch & " ORDER BY midinitial ASC);"  
  

  
   

  • Edited by snop222 on Jul 22, 2010 02:18 AM
  • Edited by snop222 on Jul 22, 2010 02:20 AM
  • Edited by snop222 on Jul 22, 2010 02:21 AM
  • Edited by snop222 on Jul 22, 2010 02:25 AM
  • Marked as answer by cincinnati_lin on Jul 22, 2010 10:06 AM
• • Reply
  

  Naom

  All-Star

  36004 Points

  7901 Posts

  Re: Please proof my SQL Select Statement

  Jul 22, 2010 04:28 AM|LINK

  Remove ORDER BY NameFull ASC from the derived table.

  Your code should be

  
  

  select coalesce(FirstName + ' ','') + coalesce(MidInitial + ' ','') + coalesce(LastName,'') as NameFull, CaseFind
  from (select ... UNION select ..) X ORDER BY NameFull -- take a look at X alias for derived table

  
  
  

  
  

  Beware of bugs in the above code; I have only proved it correct, not tried it.
  (Donald Knuth)
  
  Visit my blog
  
  Microsoft Community Contributor 2011-12
  • Edited by Naom on Jul 22, 2010 04:28 AM
  • Marked as answer by cincinnati_lin on Jul 22, 2010 10:06 AM