Hi sKwa,
Thanks for the information provided, I was able to succesfully test the first_value Analytic Function separately.
However I ended up adding the following (to the original sql in the previous thread):
inner join (select cpp.owning_acct_no,cpp.serv_no, cpp.product_code ,MAX(cpp.inst_date) as dt
from cis_prod.cst_customer_product cpp
group by cpp.owning_acct_no,cpp.serv_no,cpp.product_code) c
on c.owning_acct_no = cp.owning_acct_no
AND c.SERV_NO = cp.SERV_NO
AND c.PRODUCT_CODE = cp.PRODUCT_CODE
AND c.dt = cp.INST_DATE
This seemed to eliminate the duplicates.
I did do some tests with the first_value though..
e.g.
For the first test, I added the following to the original
inner join (select distinct cpp.product_code,cpp.serv_no, cpp.owning_acct_no, FIRST_VALUE(cpp.inst_date)
OVER (PARTITION BY cpp.owning_acct_no, cpp.serv_no, cpp.product_code ORDER BY cpp.inst_date DESC)
from cis_prod.cst_customer_product as cpp) c
on c.owning_acct_no = cp.owning_acct_no
AND c.SERV_NO = cp.SERV_NO
AND c.PRODUCT_CODE = cp.PRODUCT_CODE
AND c.INST_DATE = cp.INST_DATE
However I got the error c.INST_DATE does not exist.. and I didn't know how to give "FIRST_VALUE(cpp.inst_date)" a name
For the second test, I changed the top of the sql to:
select cp.product_code, sp.product_description, cp.unique_serv_id, cp.serv_no,
cp.owning_acct_no, cp.personal_rental, cp.cust_prod_no,
FIRST_VALUE(cp.inst_date)
OVER (PARTITION BY cp.owning_acct_no, cp.serv_no, cp.product_code ORDER BY cp.inst_date DESC), cp.price_var_exist, cp.cust_prod_status,
nm.first_name, nm.surname, nm.company_name,
fpr.fpr_bill_year, fpr.fpr_bill_month, fpr.fpr_rental_charge
This gave me the correct inst_date, but it duplicated lines as follows:
105036590000 4440270 8641 4/9/2009 W ....
105036590000 4440270 8641 4/9/2009 Z ....
Not sure if this was how you meant for it to be used.
Best Regards!
Question on 'Left outer join'
Moderator: NorbertKrupa
Re: Question on 'Left outer join'
Hi dazz!
Why in inner join sub-query you used in distinct while in the "top" did not?
Also you can define range to work for analytic functions.
PS As you defined a problem you need FIST_VALUE function.
May be I'm wrong, i'm not expert, but it's a forum may be someone else will help you.
What gives you this query?
Analytic functions on each record returns value, think how you can eliminate duplicates.[...]This gave me the correct inst_date, but it duplicated lines as follows:...
Why in inner join sub-query you used in distinct while in the "top" did not?
Also you can define range to work for analytic functions.
PS As you defined a problem you need FIST_VALUE function.
May be I'm wrong, i'm not expert, but it's a forum may be someone else will help you.
What gives you this query?
Code: Select all
SELECT distinct
cp.product_code,
sp.product_description,
cp.unique_serv_id,
cp.serv_no,
cp.owning_acct_no,
cp.personal_rental,
cp.cust_prod_no,
FIRST_VALUE(cp.inst_date) OVER (PARTITION BY cp.owning_acct_no, cp.serv_no, cp.product_code ORDER BY cp.inst_date DESC),
cp.price_var_exist,
cp.cust_prod_status,
nm.first_name,
nm.surname,
nm.company_name,
fpr.fpr_bill_year,
fpr.fpr_bill_month,
fpr.fpr_rental_charge
FROM cis_prod.cst_customer_product AS cp
LEFT OUTER JOIN
cis_prod.cst_name AS nm
ON cp.owning_acct_no = nm.acct_no
LEFT OUTER JOIN
cis_prod.sys_product AS sp
ON cp.product_code = sp.product_code
LEFT OUTER JOIN
(SELECT *
FROM cis_prod.cst_full_period_rentals AS fp
WHERE fp.fpr_bill_year = '2012'
AND fp.fpr_bill_month = '02') AS fpr
ON cp.owning_acct_no = fpr.fpr_owning_acct_no
AND cp.cust_prod_no = fpr.fpr_cust_prod_no
AND cp.product_code = fpr.fpr_product_code
WHERE (cp.product_code BETWEEN 8640 AND 8649
OR cp.product_code BETWEEN 8660 AND 8669)
AND nm.stop_time_stamp = '31-DEC-2000';
Re: Question on 'Left outer join'
Hi dazz!
I finally understand your query. You need one more join as filter.
Example:
I finally understand your query. You need one more join as filter.
Example:
- Let "LeftJoinQuestionTable" be as follow (LeftJoinQuestionTable its your question query with joins):
Code: Select all
create table LeftJoinQuestionTable ( col1 varchar, -- owning_acct_no col2 varchar, -- serv_no col3 varchar, -- product_code col4 varchar, -- personal_rental col5 varchar, -- unique_serv_id date date );
- Data (here 4 partitions by col1, col2, col3):
Code: Select all
copy LeftJoinQuestionTable from stdin direct delimiter ','; acc#1,sev#1,prod#1,person A,uniq 1,2000-01-01 acc#1,sev#1,prod#1,person B,uniq 2,2000-01-02 acc#1,sev#1,prod#1,person C,uniq 3,2000-01-03 acc#2,sev#1,prod#1,person D,uniq 4,2001-03-21 acc#2,sev#1,prod#1,person E,uniq 5,2001-02-02 acc#2,sev#1,prod#1,person F,uniq 6,2001-01-03 acc#1,sev#2,prod#2,person G,uniq 7,2010-01-01 acc#1,sev#2,prod#2,person H,uniq 8,2011-01-02 acc#1,sev#2,prod#2,person I,uniq 9,2009-01-03 acc#1,sev#2,prod#1,person J,uniq a,2002-01-01 acc#1,sev#2,prod#1,person K,uniq b,2002-01-02 acc#1,sev#2,prod#1,person L,uniq c,2003-01-03 \.
- Since I have no "CP" table I will take dates from "LeftJoinQuestionTable" (may be you will require to select all columns from partition too and use them for join. my data is simple and each date is unique too in real data only you know)
And now sub-query filter:
Code: Select all
SELECT DISTINCT First_value("date") OVER ( PARTITION BY col1, col2, col3 ORDER BY "date" DESC) AS 'date' FROM LeftJoinQuestionTable; test_db=> \e date ------------ 2000-01-03 2001-03-21 2003-01-03 2011-01-02 (4 rows)
Output:Code: Select all
SELECT col1, col2, col3, col4, col5, Dates."date" FROM LeftJoinQuestionTable INNER JOIN (SELECT DISTINCT First_value("date") OVER ( PARTITION BY col1, col2, col3 ORDER BY "date" DESC) AS 'date' FROM LeftJoinQuestionTable) AS Dates ON leftjoinquestiontable.date = Dates.date;
Code: Select all
col1 | col2 | col3 | col4 | col5 | date -------+-------+--------+----------+--------+------------ acc#1 | sev#1 | prod#1 | person C | uniq 3 | 2000-01-03 acc#1 | sev#2 | prod#1 | person L | uniq c | 2003-01-03 acc#1 | sev#2 | prod#2 | person H | uniq 8 | 2011-01-02 acc#2 | sev#1 | prod#1 | person D | uniq 4 | 2001-03-21
Code: Select all
SELECT DISTINCT Max("date")
OVER (
PARTITION BY col1, col2, col3
ORDER BY "date"
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 'date'
FROM LeftJoinQuestionTable;
date
------------
2000-01-03
2001-03-21
2003-01-03
2011-01-02
Re: Question on 'Left outer join'
Hi skwa!
Thanks for all the info on First_value! Its great to see this working the way I wanted it to. (..I didnt even realise you posted this, but just came back to thank you for all the help already given)
Thanks again!
Thanks for all the info on First_value! Its great to see this working the way I wanted it to. (..I didnt even realise you posted this, but just came back to thank you for all the help already given)
Thanks again!