[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

ORACLE SQL

Posted on 2012-04-04
9
Medium Priority
?
203 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 41

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 41

Accepted Solution

by:
Kyle Abrahams earned 1500 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 41

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

649 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