ORACLE SQL
I have this simple query I scripted, it runs but the I'm getting all kind of dates, and not just the ones over 20120201, your thoughts please.
SELECT productno, testid, testdate, testresult
FROM ProductTest
WHERE testdate > 20120201 AND
testid IN ('blue','red','violet','ye
TESTRESULT IN('TRUE', 'FALSE')
GROUP BY productno, testid, testdate, testresult
HAVING COUNT (*) > 10
Thx
SELECT productno, testid, testdate, testresult
FROM ProductTest
WHERE testdate > 20120201 AND
testid IN ('blue','red','violet','ye
TESTRESULT IN('TRUE', 'FALSE')
GROUP BY productno, testid, testdate, testresult
HAVING COUNT (*) > 10
Thx
try:
SELECT productno, testid, testdate, testresult
FROM ProductTest
WHERE testdate > to_date(20120201, 'yyyy/mm/dd') AND
testid IN ('blue','red','violet','ye
TESTRESULT IN('TRUE', 'FALSE')
GROUP BY productno, testid, testdate, testresult
HAVING COUNT (*) > 10
SELECT productno, testid, testdate, testresult
FROM ProductTest
WHERE testdate > to_date(20120201, 'yyyy/mm/dd') AND
testid IN ('blue','red','violet','ye
TESTRESULT IN('TRUE', 'FALSE')
GROUP BY productno, testid, testdate, testresult
HAVING COUNT (*) > 10
Also, what is the intent of "HAVING COUNT(*) > 10" when you're not selecting count(*)?
ASKER
The testdate is stored in the table as a "number", I can convert it where/when need be, but for this exercise I'm leaving it as it is, the end results here will be part of a Crystal Report that uses SQL Expressions and those SQL expressions don't work well with date comparisons, only number types will do. That's the reason I've not converted it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
To answer 'awking00', the reason is that, I want a basic count of all the products that met the criteria in the "Where" clause.
Then shouldn't you also select count(*) in your query?
awking, you don't need it in the select to include it in the where or having . .. he's just restricting the result set to those that have a count > 10, he doesn't actually need to pull the data back.
CodeDigger:
Awaiting response to my earlier post.
CodeDigger:
Awaiting response to my earlier post.
ASKER
The requestor changed their mind and the request was pulled off, but the points you brought up are valid and I'll keep in mind in my next sql query ventures.
Many Thanks
Many Thanks
if you want dates, you should use date types
please post table structure and sample data that illustrates the problem