6 Replies Latest reply: May 14, 2013 11:28 PM by sufiyan RSS

    Trying to understand a few things.

    sufiyan
      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
          >
          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
            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
              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
                >
                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
                  >
                  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
                    Thank you so much fellas.