Full outer join not working between two tables and multiple conditions on Oracle SQL in PL/SQL Developer

Question

I need help with a Join NOT WORKING in PL/SQL Developer under Oracle SQL. I have a table VALUES which have multiple columns. Its structure is:

CYCLE|PERIOD|FEE|CITY|SERVICE_NAME|CLIENT_TYPE|VALUE

what I want to do is to add another column new to VALUE, and name it as PREVIOUS_VALUE. It will contain the field VALUE for the row which have the same values in CYCLE, FEE, CITY, SERVICE_NAME and CLIENT_TYPE, the only difference is that it belong to a PERIOD' = PERIOD - 1. In other words, I want to add the value of the previuos period next to the VALUE in the table.

My strategy was the following: 1) Create a view VV_VIEW which contains the same fields as the table VALUES, created as following:

CREATE OR REPLACE VIEW VV_VIEW as SELECT CYCLE, PERIOD,
PREVIOUS_PERIOD(PERIOD) AS PREV_PERIOD, FEE, CITY, SERVICE_NAME, 
CLIENT_TYPE, VALUE FROM VALUES.

2) Do a FULL OUTER JOIN on VV_VIEW AND VALUES selectin all the columns from VV_VIEW and ADDING IT the column VALUE from the table VALUES with the condition that VALUES.PERIOD = VV_VIEW.PREVIOUS_PERIOD and also VALUES.CYCLE = VV_VIEW.CYCLE, VALUES.FEE = VV_VIEW.FEE, VALUES.CITY = VV_VIEW.CITY, etc. The exact query looks like this:

CREATE OR REPLACE VIEW FINAL_VIEW AS SELECT VV_VIEW.CYCLE, 
VV_VIEW.PERIOD, VV_VIEW.PREV_PERIOD, VV_VIEW.FEE, VV_VIEW.CITY, 
VV_VIEW.SERVICE_NAME, VV_VIEW.CLIENT_TYPE, VALUES.VALUE AS PREV_VALUE
FROM VV_VIEW FULL OUTER JOIN VALUES 
ON VV_VIEW.PREV_PERIOD = VALUES.PERIOD AND VV_VIEW.FEE = VALUES.FEE AND
VV_VIEW.CITY = VALUES.CITY AND VV_VIEW.SERVICE_NAME = 
VALUES.SERVICE_NAME AND
VV_VIEW.CLIENT_TYPE = VALUES.CLIENT_TYPE;

So that the VALUE from the previous period will appear next to the actual VALUE in the new view.

The problem is, FULL OUTER JOIN does not seem to work. When I use RIGHT OUTER JOIN I get the values in the PREV_VALUE COLUMN, but the other columns are NULL. If I use LEFT OUTER JOIN I get all the columns with their respective valur, except for PREV_VALUE, which remains NULL. With the FULL OUTER JOIN I get results similars to those of using LEFT JOIN.

Could you please tell me what am I doing wrong?

What I want, in simple terms is something like this:

** TABLE VALUES = ORIGINAL TABLE

CYCLE|PERIOD|FEE|CITY|SERVICE_NAME|CLIENT_TYPE|VALUE

03 |201404|0.5|CTTA|TELEPHONE_SM| PREMIUM   | 3000

03 |201405|0.7|CMFE|TELEPHONE_MM| PREMIUM   | 2000

04 |201312|0.2|CTDA|TELEPHONE_SM| STANDARD  | 500

.....

03 |201403|0.5|CTTA|TELEPHONE_SM| PREMIUM   | 9000

03 |201404|0.7|CMFE|TELEPHONE_MM| PREMIUM   | 8000

.....

03 |201402|0.5|CTTA|TELEPHONE_SM| PREMIUM   | 1000

03 |201403|0.7|CMFE|TELEPHONE_MM| PREMIUM   | 2000

.....

and get this new view:

***FINAL_VIEW

CYCLE|PERIOD|PREV_PERIOD|FEE|CITY|SERVICE_NAME|CLIENT_TYPE|VALUE | PREV_VALUE

03 |201404  | 201403    |0.5|CTTA|TELEPHONE_SM| PREMIUM   | 3000 | 9000

03 |201405  | 201404    |0.7|CMFE|TELEPHONE_MM| PREMIUM   | 2000 | 8000

04 |201312  | 201403    |0.2|CTDA|TELEPHONE_SM| STANDARD  | 500  | ....

.....

03 |201403  | 201402    |0.5|CTTA|TELEPHONE_SM| PREMIUM   | 9000 | 1000

03 |201404  | 201403    |0.7|CMFE|TELEPHONE_MM| PREMIUM   | 8000 | 2000 

.....

03 |201402  | 201401    |0.5|CTTA|TELEPHONE_SM| PREMIUM   | 1000 | .....
..............

I hope you get hat I'm trying to do. If you have an alternative approach to accomplish this, I would really like to hear from it. Thanks in advance.

EDIT: Fixed a typo in the query.

I'm not sure I follow specifically what you are trying to do with the view, but it sounds close to the functionality you get with the Oracle LAG() function: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions070.htm Hope that helps. — Joel, Oct 14 '15 at 19:45
I just read it. It really seems to be the case. However, I have to work with an outdated version. I will check if this operator works in my current version, in which case I would acknowledge your answer as the correct one. — addictedtohaskell, Oct 14 '15 at 19:49
OK. I'm experimenting with the lag function. The trouble is, that the offsets are not fixed, what I mean is, for example, sometimes the data I need could be 200 rows before, and another 500 rows before. Have you ever used the lag function()? Do you know if it can support multiple variable offsets? — addictedtohaskell, Oct 14 '15 at 20:05
I use LAG() often, you can specify the offset as the second parameter: LAG(expression, offset). But usually the default offset works if you can get the right PARTITION BY clause. — Joel, Oct 14 '15 at 20:12

narendra · Accepted Answer · 2015-10-15T19:36:53.973

0

Try below, you should get your expected results, I have used "0" (third parameter in lag function ) when previous value is not present you can use null or any other value

select 
cycle,
period,
fee,
city,
service_name,
client_type,
value,
lag(period,1,0) over (partition by cycle,fee,city,service_name,client_type order by period) as prev_period,
lag(value,1,0) over (partition by cycle,fee,city,service_name,client_type order by period) as prev_value
from table1
order by cycle,fee,city,service_name,client_type,period;

2nd Query to verify for previous 3 values

select 
cycle,
period,
fee,
city,
service_name,
client_type,
value,
row_number() over (partition by cycle,fee,city,service_name,client_type order by period) as rn,
lag(value,1,0) over (partition by cycle,fee,city,service_name,client_type order by period) as prev_value1,
lag(value,2,0) over (partition by cycle,fee,city,service_name,client_type order by period) as prev_value2,
lag(value,3,0) over (partition by cycle,fee,city,service_name,client_type order by period) as prev_value3
from table1
order by cycle,fee,city,service_name,client_type,period;

edited Oct 15 '15 at 19:36

answered Oct 14 '15 at 20:33

narendra

1,278
1
7
8

I tried this. It seemed to work at the beginning but I have a little trouble. I really need to add values from 3 periods ago, so I tried using lag as you said, changing the parameter to 2 or 3 in the other cases. The trouble I have is that sometimes it skips a value and fills it with 0, instead of offsetting it. For example it moves a value a period back, in the next row 2 periods back, but then at the third row it puts a 0. Similarly, it puts a 0 in the row with a period back, but on the next two it puts the value right. It does not follow a pattern, so I think it might be an issue with lag. – addictedtohaskell Oct 15 '15 at 15:34
Difficult to understand your scenario without data, offset in lag will always refer to offset row prior to current row based on partition by clause and if that offset row for this partition clause is not present then it will show it as 0 (3rd parameter in lag). Can you order by the result set of the query (I modified the query added order by) and verify again, it should behave the same way without any discrepancy. – narendra Oct 15 '15 at 16:15
Can you take a look at this? http://bit.ly/1MErTr0 I select the 0 which should be 103.21 instead. As you see in several other cases it works well. – addictedtohaskell Oct 15 '15 at 16:29
I feel its working fine, your screenshot was missing the columns on which lag is performing partition, the select 0 need not necessarily be 103.21 but 0 if that record pertains to some other grouping. I modified the query to include the row_number which will change when group change, it will be easy to verify.. I tried to recreate your scenario but it was working fine check http://sqlfiddle.com/#!4/ae947/3 – narendra Oct 15 '15 at 19:34
You are right. With the last query I could validate it. That seemed to be the case. I only had access to select a subset of the table, not the whole, but with your query I could validate it. I will mark your answer as the right one. Thanks a lot! – addictedtohaskell Oct 15 '15 at 20:07

Full outer join not working between two tables and multiple conditions on Oracle SQL in PL/SQL Developer

1 Answers1