This discussion is archived
6 Replies Latest reply: May 14, 2013 9:28 PM by sufiyan RSS

Trying to understand a few things.

sufiyan Newbie
Currently Being Moderated
Hi all,

I am just trying to get a better understanding on these things.

Suppose this is my simple code:

Select
Account_no,
Open_date,
Count (*)
OVER (PARTITION BY Account_no) as Account_Count
From
Accounts_Table

Data is something like this

Account_no. Open_date. Account_Count
123. 01/01/2013. 2
123. 01/01/2013. 2
1234. 01/01/2013. 1
12345. 01/01/2013. 2
12345. 01/01/2013. 2

As far as I understand, the above code is telling me the number of accounts per date? If so, why are there duplicates?


My second concern is with this code below:
Select
Tablea.ColumA
Tableb.colum12
Tablec.colum15
From
Tablea left outer join tableb on tablea.columA=tableb.columA
Left outer join tablec on tablea.columA=tablec.columA

Is the above join same as:
Tablea.columA = tableb.columA (+)
Tablea.columA = tablec.columA (+)

What im a little confused with is when joining tables using the syntax like "left outer join"
How do you know which table is left outer joined when you have multiple tables? I was reading the reference guide but it showed an example with only two tables.


Im sorry for putting these concerns together but I thought why create two threads. Thank you gentlemen.
From
  • 1. Re: Trying to understand a few things.
    rp0428 Guru
    Currently Being Moderated
    >
    As far as I understand, the above code is telling me the number of accounts per date? If so, why are there duplicates?
    >
    Analytic functions do NOT reduce the number of rows in the result set.

    >
    My second concern is with this code below:
    Select
    Tablea.ColumA
    Tableb.colum12
    Tablec.colum15
    From
    Tablea left outer join tableb on tablea.columA=tableb.columA
    Left outer join tablec on tablea.columA=tablec.columA

    Is the above join same as:
    Tablea.columA = tableb.columA (+)
    Tablea.columA = tablec.columA (+)
    >
    Yes.
    'Tablea left outer join . . .' means you want ALL rows from 'Tablea' whether there is a matching row or not.

    When you use the other syntax with the '+' you put the '+' on the table that needs to have rows 'added' if there is not a match. So the '+' on 'tableb' means 'if there is no 'joined' row from 'tableb' make up a row consisting of NULLs.
    >
    What im a little confused with is when joining tables using the syntax like "left outer join"
    How do you know which table is left outer joined when you have multiple tables?
    >
    The table you mention before the 'left outer join' clause. You said this
    Tablea left outer join tableb
    So 'Tablea' is the 'left' table.
    And then you have this code
    Left outer join tablec 
    What table did you provide before that 'Left'? The same 'Tablea'; you never mentioned a different table.
  • 2. Re: Trying to understand a few things.
    sufiyan Newbie
    Currently Being Moderated
    rp0428 wrote:
    >
    As far as I understand, the above code is telling me the number of accounts per date? If so, why are there duplicates?
    >
    Analytic functions do NOT reduce the number of rows in the result set.
    So in my table the account number must be stored twice for it to appear twice in my analytic function, correct?
    >
    My second concern is with this code below:
    Select
    Tablea.ColumA
    Tableb.colum12
    Tablec.colum15
    From
    Tablea left outer join tableb on tablea.columA=tableb.columA
    Left outer join tablec on tablea.columA=tablec.columA

    Is the above join same as:
    Tablea.columA = tableb.columA (+)
    Tablea.columA = tablec.columA (+)
    >
    Yes.
    'Tablea left outer join . . .' means you want ALL rows from 'Tablea' whether there is a matching row or not.

    When you use the other syntax with the '+' you put the '+' on the table that needs to have rows 'added' if there is not a match. So the '+' on 'tableb' means 'if there is no 'joined' row from 'tableb' make up a row consisting of NULLs.
    >
    What im a little confused with is when joining tables using the syntax like "left outer join"
    How do you know which table is left outer joined when you have multiple tables?
    >
    The table you mention before the 'left outer join' clause. You said this
    Tablea left outer join tableb
    So 'Tablea' is the 'left' table.
    And then you have this code
    Left outer join tablec 
    What table did you provide before that 'Left'? The same 'Tablea'; you never mentioned a different table.
    So i could have had this as well?
    From
    TableA LEFT OUTER JOIN TABLEB ON TABLEA.COLUMN_1 = TABLEB.COLUMN_1
    
    TABLEC LEFT OUTER JOIN TABLEA.COLUMN_1 = TABLEC.COLUMN_1
    Is that correct?

    Edited by: sufiyan on May 14, 2013 3:22 PM

    Edited by: sufiyan on May 14, 2013 3:22 PM
  • 3. Re: Trying to understand a few things.
    sufiyan Newbie
    Currently Being Moderated
    Also what if my code was like this?
    Select
    Account_no,
    Open_date,
    Count (*)
    OVER (PARTITION BY Open_date) as Account_Count
    From
    Accounts_Table
    How would my output be different?


    --Please ignore this question. Thanks

    Edited by: sufiyan on May 14, 2013 3:37 PM
  • 4. Re: Trying to understand a few things.
    rp0428 Guru
    Currently Being Moderated
    >
    So in my table the account number must be stored twice for it to appear twice in my analytic function, correct?
    >
    You didn't provide your table data so we don't know what it is.

    This query has duplicate counts for 'deptno = 20'
    select deptno, ename, count(*) over (partition by deptno) from emp
    where deptno = 20
    
    DEPTNO     ENAME     COUNT(*)OVER(PARTITIONBYDEPTNO)
    20     SMITH     5
    20     JONES     5
    20     SCOTT     5
    20     ADAMS     5
    20     FORD     5
    In the EMP table there are multiple rows (5) for 'deptno = 20' and since analytic functions do NOT remove rows there are five rows in the result. So the count is duplicated.
  • 5. Re: Trying to understand a few things.
    davidp 2 Pro
    Currently Being Moderated
    >
    So in my table the account number must be stored twice for it to appear twice in my analytic function, correct?
    >
    Each row in the output is a single row in Accounts_Table, with a count of the number of rows with the same account no as the current row. There are two rows for account_no 123 so Account_Count is 2 for each of them. There is only one row for account_no so its account_count is 1.
    If you only want one row per account, with the number of rows for that account:
    Select
    Account_no,
    MIN(Open_date),
    Count (*) Account_Count
    From Accounts_Table
    group by  Account_No
    If you want the number of accounts opened on each day:
    Select
    trunc(Open_date) opening_date,
    Count (distinct account_no) Account_Count
    From Accounts_Table
    group by trunc(Open_date)
    order by opening_date
    The ORDER BY is optional - without it the results will come out in an undefined order.

    >
    So i could have had this as well?
    From
    TableA LEFT OUTER JOIN TABLEB ON TABLEA.COLUMN_1 = TABLEB.COLUMN_1
    TABLEC LEFT OUTER JOIN TABLEA.COLUMN_1 = TABLEC.COLUMN_1
    Is that correct?
    >
    It is at least missing commas, and probably not what you wanted. The join clauses work left to right, and "LEFT OUTER JOIN" and "RIGHT OUTER JOIN" keep every row from the names side of the phrase and generating null rows for the other side so
    TableA LEFT OUTER JOIN TableB ON TABLEA.COLUMN_1 = TABLEB.COLUMN_1 
    LEFT OUTER JOIN TableC ON tableC.column3 = tableA.column_3 AND tableC.column_4 = tableB.column_2
    RIGHT OUTER JOIN TableD on TableD.column_1 = TableA.column_5
    will join TableA to TableB, keeping every row from table A,
    then join the result to TableC, keeping every row from the first join.
    Then join the result to table D, keeping every row from table D (and generating null rows for the TableA/B/C join result)
    You can add brackets to change the order of joining.

    Hope this helps
    David
  • 6. Re: Trying to understand a few things.
    sufiyan Newbie
    Currently Being Moderated
    Thank you so much fellas.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points