Home » SQL & PL/SQL » SQL & PL/SQL » Query help.... (Oracle 10g)
Query help.... [message #379422] Tue, 06 January 2009 06:58 Go to next message
squash junkie
Messages: 5
Registered: January 2009
Junior Member
Hi,

I am having some problems creating a query to produce the results that I need. The data consists of sales transaction data in one table, and currency conversion rates in another
Sales Data:
Customer qty price Currency Id Day Of Year
Cust A 10 100 Eur 1
Cust A 10 90 Eur 2
Cust A 10 65 Eur 3
Cust A 10 45 Eur 4
Cust A 10 90 Eur 5
Cust A 10 100 Eur 6
Cust A 10 95 Eur 7
Cust A 10 105 Eur 8
Cust A 10 110 Eur 9

Currency Data:
Currency ID Rate Day Of Year
Eur 1.45 1
Eur 1.44 2
Eur 1.4 3
Eur 1.54 4

What I need to do is match the sales and currency data so that they can be converted using the correct rates. Using the example above, for the first four days of the year is no problem, what I need for the remaining sales transactions is for the latest exchange rate to be used, i.e. sales transactions on day 1 use a rate of 1.45, day 2 a rate of 1.44, day 3 a rate of 1.40, day 4 a rate of 1.54, days 5,6,7,8 & 9 to use a rate of 1.54, as that is the latest rate to be entered.
I am pretty sure I need to use a sub-query to get this going, but am a little stuck on the syntax. What makes it a little more complex is that several currencies are used (not just euros as per my example data)

Thx for the help
Re: Query help.... [message #379425 is a reply to message #379422] Tue, 06 January 2009 07:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Post a Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Query help.... [message #379426 is a reply to message #379422] Tue, 06 January 2009 07:14 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
A simple equi join between the two tables on Currency Id and Day Of Year should work.
Re: Query help.... [message #379428 is a reply to message #379426] Tue, 06 January 2009 07:19 Go to previous messageGo to next message
squash junkie
Messages: 5
Registered: January 2009
Junior Member
That's currently what I have in my query. The data returned however will then only match sales transactions on days of the year which have an exchange rate. I also need to add currency rates (the latest added) to future sales transactions where there is currently no daily rate entered.

Re: Query help.... [message #379430 is a reply to message #379428] Tue, 06 January 2009 07:22 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
OK, that will then swing it to an Outer join.

PS: it will help if you can post the query with sample data (create table + insert statements).
Re: Query help.... [message #379431 is a reply to message #379428] Tue, 06 January 2009 07:22 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link and try to build your query based on this concept.

http://www.oracle-developer.net/display.php?id=312

Regards

Raj
Re: Query help.... [message #379432 is a reply to message #379431] Tue, 06 January 2009 07:29 Go to previous messageGo to next message
squash junkie
Messages: 5
Registered: January 2009
Junior Member
OK- here is what I currently have, but I am getting a "single row sub-query returns more than one row" error. I didn't post the code initially as I though there might be a better (or easier) way to get the result.

SELECT
rpt_view.v_wkly_run_eu.iso_week, 
rpt_view.v_wkly_run_eu.activity_date, 
rpt_view.v_wkly_run_eu.tot_nat_curr, 
rpt_view.v_wkly_run_eu.trans_currency_id, 
rpt_view.v_wkly_run_eu.DAY_OF_YEAR, 
NVL(rpt_view.v_curr_cal_usd.conv_rate, 
(
SELECT rpt_view.v_curr_cal_usd.conv_rate FROM rpt_view.v_curr_cal_usd 
WHERE rpt_view.v_curr_cal_usd.DAY_OF_YEAR = 
(SELECT MAX(rpt_view.v_curr_cal_usd.DAY_OF_YEAR) FROM rpt_view.v_curr_cal_usd)
)
) CONV_RATE,
NVL(rpt_view.v_curr_cal_usd.from_currency, 
(
SELECT rpt_view.v_curr_cal_usd.from_currency  FROM rpt_view.v_curr_cal_usd 
WHERE rpt_view.v_curr_cal_usd.DAY_OF_YEAR = 
(SELECT MAX(rpt_view.v_curr_cal_usd.DAY_OF_YEAR) FROM rpt_view.v_curr_cal_usd)
)
) FROM_CURRENCY
FROM
rpt_view.v_wkly_run_eu,
rpt_view.v_curr_cal_usd
WHERE
rpt_view.v_wkly_run_eu.DAY_OF_YEAR = rpt_view.v_curr_cal_usd.DAY_OF_YEAR(+)
AND rpt_view.v_curr_cal_usd.from_currency= rpt_view.v_wkly_run_eu.trans_currency_id

[Updated on: Tue, 06 January 2009 07:35] by Moderator

Report message to a moderator

Re: Query help.... [message #379446 is a reply to message #379432] Tue, 06 January 2009 09:07 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
A slightly easier to read version of your code:
SELECT v_wkly_run_eu.iso_week, 
       v_wkly_run_eu.activity_date, 
       v_wkly_run_eu.tot_nat_curr, 
       v_wkly_run_eu.trans_currency_id, 
       v_wkly_run_eu.DAY_OF_YEAR, 
       NVL(v_curr_cal_usd.conv_rate, 
           (SELECT v_curr_cal_usd.conv_rate 
            FROM v_curr_cal_usd 
            WHERE v_curr_cal_usd.DAY_OF_YEAR = (SELECT MAX(v_curr_cal_usd.DAY_OF_YEAR) 
                                               FROM v_curr_cal_usd))) CONV_RATE,
       NVL(v_curr_cal_usd.from_currency, 
           (SELECT v_curr_cal_usd.from_currency  
            FROM v_curr_cal_usd 
            WHERE v_curr_cal_usd.DAY_OF_YEAR = (SELECT MAX(v_curr_cal_usd.DAY_OF_YEAR) 
                                                FROM v_curr_cal_usd))) FROM_CURRENCY
FROM v_wkly_run_eu, v_curr_cal_usd
WHERE v_wkly_run_eu.DAY_OF_YEAR = v_curr_cal_usd.DAY_OF_YEAR(+)
AND v_curr_cal_usd.from_currency = v_wkly_run_eu.trans_currency_id

I've removed the schema name and indented the code.

Couple of problems.
1) Your outer join is incomplete. You need the (+) next to v_curr_cal_usd.from_currency as well.
2) Don't you need to use currency_id in the sub-queries as well?
Re: Query help.... [message #379447 is a reply to message #379446] Tue, 06 January 2009 09:11 Go to previous messageGo to next message
squash junkie
Messages: 5
Registered: January 2009
Junior Member
cookiemonster - thx for that. I had spotted the problem with the join, but am struggling as to how to include the currency_id in the subqueries.... Confused that must be what is throwing the error when I run the query (or not??)
Any thoughts?
Re: Query help.... [message #379449 is a reply to message #379422] Tue, 06 January 2009 09:28 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Yes that's almost certainly what's causing your error.

You need to alias the tables i.e.
FROM v_wkly_run_eu run, v_curr_cal_usd curr


Then you can reference the value from those in your subqueries:
(SELECT conv_rate 
FROM v_curr_cal_usd 
WHERE DAY_OF_YEAR = <select max day of year for currency>
WHERE currency_id = run.currency_id)


That's what's known as a correlated sub-query.

EDIT: fixed typo.

[Updated on: Tue, 06 January 2009 09:28]

Report message to a moderator

icon14.gif  Re: Query help.... [message #379451 is a reply to message #379449] Tue, 06 January 2009 09:57 Go to previous message
squash junkie
Messages: 5
Registered: January 2009
Junior Member
thx a lot cookiemonster... Smile

That worked a treat!
Previous Topic: How to write a script to call a procedure after checking the highest ID..??
Next Topic: create table
Goto Forum:
  


Current Time: Sun Dec 11 04:23:41 CST 2016

Total time taken to generate the page: 0.07133 seconds