Solved

ORACLE SQL

Posted on 2012-04-04
9
197 Views
Last Modified: 2012-04-09
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
0
Comment
Question by:Roberto Madro R.
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 37807621
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
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 37807647
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
 
LVL 32

Expert Comment

by:awking00
ID: 37807667
Also, what is the intent of "HAVING COUNT(*) > 10" when you're not selecting count(*)?
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:Roberto Madro R.
ID: 37807671
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
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 37807685
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
 

Author Comment

by:Roberto Madro R.
ID: 37807711
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
 
LVL 32

Expert Comment

by:awking00
ID: 37807732
Then shouldn't you also select count(*) in your query?
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 37808073
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
 

Author Closing Comment

by:Roberto Madro R.
ID: 37824578
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

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question