Home » SQL & PL/SQL » SQL & PL/SQL » SQL Problem (Oracle 9.2.0.1)
SQL Problem [message #365279] Thu, 11 December 2008 05:14 Go to next message
deb.b
Messages: 44
Registered: December 2008
Member

DESC PRODUCTS
PROD_ID
CUSTOMER_ID
LOCATION_ID
UPD_DT


DESC CUSTOMER_INFO
[code]
CUSTOMER_ID
CUSTOMER_NAME
CUSTOMER_ADDRESS
[code]

DESC LOCATIONS
LOCATION_ID
LOCATION_NAME




From the above table data populate the data into a new table that are <30 days old (based on upd_dt) after every 24 hrs

The new table structure is like below
DESC NEW_TABLE
PROD_ID,
CUSTOMER_ID,
CUSTOMER_NAME,
LOCATION_ID,
location_name,
LOCATIONWISE_PRODUCT_COUNT ( location wise how many products)
UPD_DT 

--one product_id can be associated with mulitple locations (location_id)

Insert into new_table 
select a.PROD_ID,a.CUSTOMER_ID,c.CUSTOMER_NAME,a.LOCATION_ID,l.location_name,a.UPD_DT 
from PRODUCTS a,CUSTOMER_INFO c,LOCATIONS l where  a.customer_id=c.customer_id
and a.location_id=l.location_id and a.UPD_DT < SYSDATE - 30

But can't be able to insert LOCATIONWISE_PRODUCT_COUNT.I need to enter the counts based on (group by location_id,product_id).
Re: SQL Problem [message #365291 is a reply to message #365279] Thu, 11 December 2008 05:41 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear

No 1.

values are not enough to insert in new table. Total columns in your table are 7 while you are selecting 6 columns in you query.

No 2.

Please provide some test data to check it.


Best Regards.
Muhammad Asif Malik.

Re: SQL Problem [message #365304 is a reply to message #365291] Thu, 11 December 2008 06:24 Go to previous messageGo to next message
deb.b
Messages: 44
Registered: December 2008
Member
mamalik wrote on Thu, 11 December 2008 05:41
Dear

No 1.

values are not enough to insert in new table. Total columns in your table are 7 while you are selecting 6 columns in you query.

No 2.

Please provide some test data to check it.


Best Regards.
Muhammad Asif Malik.



mamalik,

Seems that you didnt get time to read what I wrote below in my previous post.

I am not finding way to insert data to column LOCATIONWISE_PRODUCT_COUNT
Re: SQL Problem [message #365317 is a reply to message #365279] Thu, 11 December 2008 07:19 Go to previous messageGo to next message
deb.b
Messages: 44
Registered: December 2008
Member
I have modified the query into
Insert into new_table 
 SELECT a.PROD_ID,
  a.CUSTOMER_ID  ,
  c.CUSTOMER_NAME,
  a.LOCATION_ID  ,
  l.location_name,
 /* Modified to get count based on (GROUP BY p.location,p.product_id )*/  
(SELECT COUNT(*)
     FROM PRODUCTS p
    WHERE p.location = l.location
  AND p.product_id   = a.producr_id
  GROUP BY p.location,p.product_id 
  ) AS LOCATIONWISE_PRODUCT_COUNT,
  a.UPD_DT
   FROM PRODUCTS a,
  CUSTOMER_INFO c ,
  LOCATIONS l
  WHERE a.customer_id=c.customer_id
AND a.location_id    =l.location_id
AND a.UPD_DT         < SYSDATE - 30


Please let me know if I am wrong here.

[Updated on: Thu, 11 December 2008 07:21]

Report message to a moderator

Re: SQL Problem [message #365335 is a reply to message #365279] Thu, 11 December 2008 09:39 Go to previous messageGo to next message
deb.b
Messages: 44
Registered: December 2008
Member
Is there anyone to provide suggestion atleast!! If any information required let me know...

Re: SQL Problem [message #365338 is a reply to message #365279] Thu, 11 December 2008 09:52 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
I just wouldn't have a LOCATIONWISE_PRODUCT_COUNT column at all - summary columns are almost always more hassle than they're worth.
You can calculate the value on the fly when you need it.
In fact this looks like a summary table, in which case you'd probably be better off looking at materialised views.

Otherwise your SQL looks like it should work - have you actually tried it?

On a side note - When posting table definations, a simple copy and paste of describe from SQL+ helps. That way we get the datatypes as well.
It also helps if you tell us what the primary keys are.

[Updated on: Thu, 11 December 2008 09:54]

Report message to a moderator

Re: SQL Problem [message #365340 is a reply to message #365338] Thu, 11 December 2008 10:04 Go to previous message
deb.b
Messages: 44
Registered: December 2008
Member
Thanks for the prompt response!

[Updated on: Thu, 11 December 2008 11:10]

Report message to a moderator

Previous Topic: DBMS_STATS package
Next Topic: Continuing the process after an EXCEPTION
Goto Forum:
  


Current Time: Sun Dec 04 02:42:08 CST 2016

Total time taken to generate the page: 0.07912 seconds