Solved

ORACLE SQL

Posted on 2012-04-04
9
188 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:codedigger
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 73

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
 

Author Comment

by:codedigger
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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:codedigger
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:codedigger
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

867 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now