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','yellow','magenta','cyan')AND
      TESTRESULT IN('TRUE', 'FALSE')
GROUP BY productno, testid, testdate, testresult
HAVING COUNT (*) > 10


Thx
Roberto Madro R.Programmer AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
what is testdate? you're comparing it to a number.

if you want dates, you should use date types

please post table structure and sample data that illustrates the problem
0
Kyle AbrahamsSenior .Net DeveloperCommented:
try:
SELECT productno, testid, testdate, testresult
FROM ProductTest
WHERE testdate > to_date(20120201, 'yyyy/mm/dd') AND
      testid IN ('blue','red','violet','yellow','magenta','cyan') AND
      TESTRESULT IN('TRUE', 'FALSE')
GROUP BY productno, testid, testdate, testresult
HAVING COUNT (*) > 10
0
awking00Commented:
Also, what is the intent of "HAVING COUNT(*) > 10" when you're not selecting count(*)?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Roberto Madro R.Programmer AnalystAuthor Commented:
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.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
what are some of the dates that you're getting that shouldn't be?

Ensure there are no spaces in the data or anything of that nature?

If you convert both sides do you still get the same result set?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roberto Madro R.Programmer AnalystAuthor Commented:
To answer 'awking00', the reason is that, I want a basic count of all the products that met the criteria in the "Where" clause.
0
awking00Commented:
Then shouldn't you also select count(*) in your query?
0
Kyle AbrahamsSenior .Net DeveloperCommented:
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.
0
Roberto Madro R.Programmer AnalystAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.