I am having trouble with a query. There are two tables from which I need to extract data. There are 57 columns...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
in a table called "sales" and 60 columns in table called "orders". I will briefly give the details of the table:
ORDERS -------- order No(char 8) catalog code(char 3) total-sales(number 11) net-sales(Number 11) SALES ------- order No(char 8) division code(char 2) date(char 8)(yyyymmdd)
These above columns are the ones we use in the query.
We need the information for each catalog code, the sum of total sales, net sales, and the number of orders in a particular month of a year and the same data for the same month of the previous year. The user enters "division code" & "date"(user enters only month & year)
I tried a query but it is not working. The query is
select a.catalogcode,b.catalogcode,sum(a.total-sales), sum(b.total-sales),sum(a.net-sales),sum(b.net-sales) from orders a,orders b where a.orderNo in(select orderNo from sales where divisioncode='60' and substr(date,1,6)='200103') or b.orderNo in(select orderNo from sales where divisioncode='60' and substr(date,1,6)='200003') group by a.catalogcode, b.catalogcode
If you can help me out in this regard I will be very thankful to you!
The first thing I always do in attacking a query problem is to rewrite it with pleasant indentation. Maybe it's just me, but I get lost easily unless I can quickly pinpoint the various clauses of the query.
select a.catalogcode , b.catalogcode , sum(a.total-sales) , sum(b.total-sales) , sum(a.net-sales) , sum(b.net-sales) from orders a , orders b where a.orderNo in (select orderNo from sales where divisioncode='60' and substr(date,1,6)='200103') or b.orderNo in (select orderNo from sales where divisioncode='60' and substr(date,1,6)='200003') group by a.catalogcode , b.catalogcode
The first thing I notice is that there is no join condition.
Whenever you join two tables, you need to qualify which rows you want from the so-called cross product of rows that you get as a result of the join. This is true even if, as in this case, they are two copies of the same table.
A cross product is what you get by matching each row of the first table with every row of the second table. In most cases, there's a key field in each table, and the key fields of the tables are usually compared in the join condition. In this example, you want to match the catalog code for last year's sales with the catalog code for this year's sales -- it just doesn't make sense to compare last year's sales in catalog code 27 with this year's sales in any other catalog code besides 27. If there are 100 catalog codes, there are a possible 100?*?100?=?10,000 different combinations of catalog codes, but you're only interested in the rows where last year's sales and this year's sales are for the same catalog code.
Secondly, you have a problem with the OR condition. The first part of the WHERE clause selects orders in division 60 for March 2001. The second part of the WHERE clause selects orders in division 60 for March 2000. I am quite certain that you want an AND condition. Unfortunately, since you're using OR, only one of the two WHERE clause conditions need be true in order for the entire WHERE clause to evaluate true. In other words, you get orders in division 60 for March 2001 matched with all other orders, and you get all other orders matched with orders in division 60 for March 2000. This is not a complete cross product, but still way more rows than you wanted.
We're still not out of the woods. It isn't enough that the catalog codes be the same, because if we're not careful, we'll still end up with a (portion of) a cross product.
Consider the following sample order data --
---- division 60 ---- ---- division 60 ---- ---- March 2001 ---- ---- March 2000 ---- order catalog total order catalog total no code sales no code sales 22222 ABC 5,000 77777 ABC 3,000 33333 XYZ 1,000 88888 ABC 2,000 44444 ABC 6,000
Continued in Part II...
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.