Solved

ORACLE SQL

Posted on 2012-04-04
9
191 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.
  • 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
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.

 

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

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

Suggested Solutions

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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 recover a database from a user managed backup

772 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