-3

I am very new to SQL and don't understand why this query is not working. I was hoping someone could give me a hand. Thank you in advance. 

SELECT COUNT (Results.Output)
FROM RESULTS
WHERE Results.Output <> 'Simulated' AND Results.Output <> '-' AND 
      Results.Det = 'ASBACM_SZ OR Results.Det = 'ASBACM AND Result.Detdate >= ? 
      AND Result.Detdate <= ?
Aaron
  • 29
  • 6
  • Kindly post also whats the sql error when you run this query. – XxXk5XxX Sep 10 '14 at 09:43
  • 3
    "*not working*" is not a valid error message in any DBMS I know. Please add the **exact** error message. And you should also state which DBMS you are using (Postgres? Oracle?). –  Sep 10 '14 at 09:47
  • 2
    @a_horse_with_no_name it's a new feature of SQL Server 2014 Enterprise. You can `SET OBFUSCATED_ERR_MSGS ON` so that you don't give away any security details by providing any meaningful information in your error message. ;) – DeanOC Sep 10 '14 at 09:50
  • Do you hate' Not Working' as much as me? http://meta.stackoverflow.com/questions/271089/recognise-not-working-and-suggest-improvement-to-user – Nick.Mc Sep 10 '14 at 10:19

2 Answers2

2

Try adding some parentheses and add the missing quotes:

SELECT COUNT(Results.[Output]) FROM Results
WHERE Results.[Output] <> 'Simulated'
    AND Results.[Output] <> '-'
    AND (
        Results.Det = 'ASBACM_SZ'
            OR
        Results.Det = 'ASBACM'
    )
    AND Result.Detdate >= ?
    AND Result.Detdate <= ?
Robby Cornelissen
  • 91,784
  • 22
  • 134
  • 156
  • additionally, if you are really new with SQL you need to change the value of '?' to a specific date for this to be able to run. But if you are using this query in some programming language, '?' is a parameter which is another topic that I think you need to know. – jomsk1e Sep 10 '14 at 09:49
  • Hello, Thanks for you help. The error I am getting is "Location of error in the SQL statement is: 15 [Parsing Expression (Column 1 in the SELECT clause)] – Aaron Sep 10 '14 at 09:56
  • I've updated my answer. The reason for the error is that `OUTPUT` is a reserved keyword in Advantage. Could you add the `advantage-database-server` tag to your question? – Robby Cornelissen Sep 10 '14 at 10:12
1

Single quotes need to be closed (you're missing some closing quotes) and operators often need to be grouped when you have a mix of 'and' and 'or'.

You might try this:

select  count (*)
from    results
where   results.[output] <> 'Simulated'
    and results.[output] <> '-'
    and (results.det = 'ASBACM_SZ' or results.det = 'ASBACM') --group the 'or'
    and result.detdate >= 'somedate'
    and result.detdate <= 'otherdate'

An abbreviated form:

select  count (*)
from    results
where   results.[output] not in ('Simulated', '-')
    and results.det in ('ASBACM_SZ', 'ASBACM')
    and result.detdate between 'somedate' and 'otherdate'
DvS
  • 1,025
  • 6
  • 11