Home » SQL & PL/SQL » SQL & PL/SQL » Can you guyz check this code for me plz !!!
Can you guyz check this code for me plz !!! [message #238520] Fri, 18 May 2007 07:16 Go to next message
Cuong
Messages: 12
Registered: May 2007
Junior Member
i have a date-time table. PK is time_key. i tried to put purch_date = time_key and ship_date = time_key to DWPURCHASES table. The problem is even though the purch_date and ship_date are different , the same time_key are inserted. i dont know what is wrong with this code below ... plz help me


INSERT INTO DWPURCHASES(product_key, customer_key, time_key, cost_price, sell_price, purchase_price, shipping_charge, ship_key)
SELECT p.product_key, c.customer_key, t.time_key, usp.cost_price, usp.sell_price, uspu.purchase_price, uspu.shipping_charge, t.time_key
FROM DWPRODUCT p, DWCUSTOMER c, DWTIME t, USPURCHASES uspu, USPRODUCT usp, USCUSTOMER uc
WHERE usp.product_id = p.product_id
AND c.customer_id = uc.customer_id
--AND uc.tfn = c.tfn
AND uspu.product_id = usp.product_id
AND uspu.customer_id = uc.customer_id
AND uspu.purch_date = t.ddate
AND t.ddate = uspu.ship_date;

Re: Can you guyz check this code for me plz !!! [message #238561 is a reply to message #238520] Fri, 18 May 2007 09:29 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi,

You are using time key in both cases, so you might want to change one?

INSERT INTO DWPURCHASES(product_key, customer_key, time_key, cost_price, sell_price, purchase_price, shipping_charge, ship_key)
SELECT p.product_key, c.customer_key, t.time_key, usp.cost_price, usp.sell_price, uspu.purchase_price, uspu.shipping_charge, t.time_key
Re: Can you guyz check this code for me plz !!! [message #238572 is a reply to message #238520] Fri, 18 May 2007 09:52 Go to previous messageGo to next message
Cuong
Messages: 12
Registered: May 2007
Junior Member
so what should i change to ?
both refer to the date-time table so they may store the time_key which is it's PK.
thank for replying
Re: Can you guyz check this code for me plz !!! [message #238574 is a reply to message #238520] Fri, 18 May 2007 10:13 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
I can't really help with that as I don't know your system or your data.

What is the purpose of DWTIME and why would you expect it to be able to put 2 different values into DWPURCHASES? Should you be getting the dates you require from the other tables?
Re: Can you guyz check this code for me plz !!! [message #238575 is a reply to message #238574] Fri, 18 May 2007 10:37 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Maybe I see what's going on:
INSERT INTO dwpurchases
  (product_key
  ,customer_key
  ,time_key
  ,cost_price
  ,sell_price
  ,purchase_price
  ,shipping_charge
  ,ship_key)
  SELECT p.product_key
        ,c.customer_key
        ,t.time_key
        ,usp.cost_price
        ,usp.sell_price
        ,uspu.purchase_price
        ,uspu.shipping_charge
        ,t.time_key
  FROM   dwproduct   p
        ,dwcustomer  c
        ,dwtime      t
        ,uspurchases uspu
        ,usproduct   usp
        ,uscustomer  uc
  WHERE  usp.product_id = p.product_id
  AND    c.customer_id = uc.customer_id
        --AND uc.tfn = c.tfn
  AND    uspu.product_id = usp.product_id
  AND    uspu.customer_id = uc.customer_id
  AND    uspu.purch_date = t.ddate -- let's say purch_date is 01-JAN-1007
  AND    t.ddate = uspu.ship_date; -- let's say ship_date is 05-JAN-2007


If you want to insert the id's for those different dates, then you have to join to the same time table twice, like:
INSERT INTO dwpurchases
  (...)
  SELECT ...
        ,t.time_key
        ...
        ,t2.time_key
  FROM   ...
        ,dwtime      t
        ,dwtime      t2
        ...
  WHERE  ...
  AND    uspu.purch_date = t.ddate
  AND    t2.ddate = uspu.ship_date;
Re: Can you guyz check this code for me plz !!! [message #238656 is a reply to message #238520] Fri, 18 May 2007 17:55 Go to previous messageGo to next message
Cuong
Messages: 12
Registered: May 2007
Junior Member
yah i got it now
thank alot guyz
Re: Can you guyz check this code for me plz !!! [message #238658 is a reply to message #238520] Fri, 18 May 2007 18:52 Go to previous messageGo to next message
Cuong
Messages: 12
Registered: May 2007
Junior Member
... wait, your code seems correct but somehow it gave the same time_key again for both column. anything are wrong in my code ?
Re: Can you guyz check this code for me plz !!! [message #238684 is a reply to message #238520] Sat, 19 May 2007 00:33 Go to previous messageGo to next message
Cuong
Messages: 12
Registered: May 2007
Junior Member
can anyone help me plz??
Re: Can you guyz check this code for me plz !!! [message #238688 is a reply to message #238684] Sat, 19 May 2007 01:36 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Can you post the exact code you're using? And one or two rows where the output it not as you would expect?

[Updated on: Sat, 19 May 2007 01:37]

Report message to a moderator

Re: Can you guyz check this code for me plz !!! [message #238710 is a reply to message #238520] Sat, 19 May 2007 08:56 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
Cuong,
my car is not working
please tell me how to make my car go
Thanks
Re: Can you guyz check this code for me plz !!! [message #238825 is a reply to message #238520] Sun, 20 May 2007 05:20 Go to previous messageGo to next message
Cuong
Messages: 12
Registered: May 2007
Junior Member
@ skooman : i used the code you gave me.
- about the output, supposedly 1 of the 2 rows output should be different with the other. I dont know which one is incorrect but i think it doesnt matter since i just need them to be different.
thank for replying me.
Re: Can you guyz check this code for me plz !!! [message #238844 is a reply to message #238825] Sun, 20 May 2007 09:35 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Sorry, I don't understand your last post. Do you still have a question/problem or does it (sort of) work now?
Re: Can you guyz check this code for me plz !!! [message #238847 is a reply to message #238520] Sun, 20 May 2007 09:42 Go to previous messageGo to next message
Cuong
Messages: 12
Registered: May 2007
Junior Member
I meant i got what you mean here:

INSERT INTO dwpurchases
(...)
SELECT ...
,t.time_key
...
,t2.time_key
FROM ...
,dwtime t
,dwtime t2
...
WHERE ...
AND uspu.purch_date = t.ddate
AND t2.ddate = uspu.ship_date;

The code above seems correct but when i tried to apply it into my code it gives the same time_key into 2 columns time_key and ship_key.
Re: Can you guyz check this code for me plz !!! [message #238849 is a reply to message #238847] Sun, 20 May 2007 09:58 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Cuong wrote on Sun, 20 May 2007 10:12
... it gives the same time_key into 2 columns time_key and ship_key.


While uspu.purch_date and uspu.ship_date are not the same you mean? That's not possible. Please post the exact code you used and an sample of the output.

Re: Can you guyz check this code for me plz !!! [message #238857 is a reply to message #238520] Sun, 20 May 2007 10:40 Go to previous messageGo to next message
Cuong
Messages: 12
Registered: May 2007
Junior Member
here is the out put
PRODUCT_KEY /CUSTOMER_KEY /TIME_KEY /COST_PRICE SELL_PRICE /PURCHASE_PRICE /SHIPPING_CHARGE /SHIP_KEY
29 / 160 /10000693 /15.67 / 24.99 / 24.99 / 2.95 /10000693
147 /114 /10000695 /45.56 / 72.87 / 72.87 /4.5 /10000695
142 /142 /10000697 /8.9 /15.67 / 15.67 / 2.95 /10000697


and here is the code im using

INSERT INTO DWPURCHASES(product_key, customer_key, time_key, cost_price, sell_price, purchase_price, shipping_charge, ship_key)
SELECT p.product_key, c.customer_key, t.time_key, usp.cost_price, usp.sell_price, uspu.purchase_price, uspu.shipping_charge, t2.time_key
FROM DWPRODUCT p, DWCUSTOMER c, DWTIME t, DWTIME t2, USPURCHASES uspu, USPRODUCT usp, USCUSTOMER uc
WHERE usp.product_id = p.product_id
AND c.customer_id = uc.customer_id
--AND uc.tfn = c.tfn
AND uspu.product_id = usp.product_id
AND uspu.customer_id = uc.customer_id
AND uspu.purch_date = t.ddate
AND uspu.ship_date = t2.ddate;


as you see, the time_key and the ship_key is the same even tho the uspu.purch_date and uspu.ship_date are different
Re: Can you guyz check this code for me plz !!! [message #239047 is a reply to message #238520] Mon, 21 May 2007 06:04 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
The code looks fine. Can you post the data you are using, USPURCHASES and DWTIME, that relate to the output you showed?
Re: Can you guyz check this code for me plz !!! [message #239611 is a reply to message #239047] Tue, 22 May 2007 14:25 Go to previous messageGo to next message
Cuong
Messages: 12
Registered: May 2007
Junior Member
DWPURCHASE containt time_key and ship_key, they both refer to DWTIME.time_key column.
Re: Can you guyz check this code for me plz !!! [message #239695 is a reply to message #238520] Wed, 23 May 2007 02:09 Go to previous message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Not the fields they contain - the data that is in the tables?

You are showing the result you are getting, but the query you have WILL return two different values if they are different in the first place.

Although you are linking on two different dates you are returning times - you need to show us that those times are different for the dates you are using - so you need to show us the data you are using. You don't have to show everything if you don't want to - just give some create table scripts and some insert statements so people can help.
Previous Topic: Statement Ignored 'Error Message occured while calling for RPAD'
Next Topic: how to find out column names ?
Goto Forum:
  


Current Time: Sat Dec 03 18:16:55 CST 2016

Total time taken to generate the page: 0.09541 seconds