Question

SQL query to JOIN 2 tables...

Rating7.2Very Good

I need to write a query which will be joing both the fact and dimention table by account level to get the amount which is stored by group level (detail).
I've no problem writing a query for the detail level.

account dimension table has the following columns

acct_id, grp_id, eff_dt, exp_dat, product, vechicle, dim_key


SQL Statement which produced this data:
  select *
  from dim_table
  where 1 = 1
  and grp_typ = 'A'                   -- account level flag
  and acct_id in ('0036','0021')
  and eff_dt <= date '2007-05-31'
  and exp_dt >= date '2007-05-31'

AKEY            ACCT_ID      GRP_ID      GRP_TYP      EFF_DT            EXP_DT            PRODUCT            LEGAL_RELAT_CD      VEHICLE
049            0021      0021      A      05/03/2007      08/30/2007                  ADVIS            MULTI
154            0036      0036      A      05/03/2007      08/30/2007      MORTGNMA      DISC            FIXED


SQL Statement which produced this data:
  select AKEY akey,acct_id,grp_id,grp_typ,eff_dt,exp_dt,product,legal_relat_cd,vehicle
  from dim_table
  where 1 = 1
  and grp_typ = 'S'                    -- group level flag
  and acct_id in ('0036','0021')
  and eff_dt <= date '2007-05-31'
  and exp_dt >= date '2007-05-31'
  order by acct_id


AKEY            ACCT_ID      GRP_ID      GRP_TYP      EFF_DT            EXP_DT            PRODUCT            LEGAL_RELAT_CD      VEHICLE
94849            0021      002101      S      05/03/2007      08/30/2007      CORPBOND      ADVIS            FIXED
94850            0021      002102      S      05/03/2007      08/30/2007      EQINCEXT      ADVIS            EQUITY
94851            0021      002103      S      05/03/2007      08/30/2007      CASHSUB            ADVIS            CASH
44849            0036      003601      S      05/03/2007      08/30/2007      MORTGNMA      DISC            FIXED
44850            0036      003602      S      05/03/2007      07/02/2007      MORTGNMA      DISC            FIXED


fact table has the following columns

dim_key,amount

SQL Statement which produced this data:
  select  dim.acct_id,dim.grp_id,af.EFF_DT, af.AKEY,  af.amount
  from  fact_table af ,dim_table dim
  where 1 = 1
  and af.eff_dt = date '2007-05-31'
  and af.AKEY = dim.AKEY
  and dim.acct_id in ('0036','0021')
  and dim.grp_typ = 'S'
  order by dim.acct_id asc

ACCT_ID      GRP_ID      EFF_DT             AKEY            AMOUNT
0021      002101      05/31/2007      94849            160.11
0021      002102      05/31/2007      94850            330.67
0021      002103      05/31/2007      94851            190.93
0036      003601      05/31/2007      44849            205.65
0036      003602      05/31/2007      44850            262.54

1) amount are availble only by grp level (not account level)
2) there will be no match to the fact table by account level key



Output by account level:
-----------------------

AKEY            ACCT_ID      GRP_ID      GRP_TYP      EFF_DT            EXP_DT            PRODUCT            LEGAL_RELAT_CD      VEHICLE      AMOUNT
049            0021      0021      A      05/03/2007      08/30/2007                  ADVIS            MULTI   681.71
154            0036      0036      A      05/03/2007      08/30/2007      MORTGNMA      DISC            FIXED      468.19


  1. Can I do a SQL query join on an Oracle db and a l…
    I have Actual Technologies ODBC driver configured to allow FMP 10 to query an Oracle enterprise database. I have turned on ODBC/JDBC sharing in FMP and configured the SequeLink driver on OS X 10.6 with the FMP file open on the same local machine. The DSN connection to FMP tests successfully, but when I setup an Execute SQL script query, I am pro…
  2. Need Help writing SQL Join or Union
    Hi. I'm trying to figure out the most efficient way to write a SQL query to pull the total number of refunds and total dollar value of refunds based on a promotionId within a database. Here are the tables I'm trying to get data from with only relevant columns included: PROMOTIONS Promotion Id (INT, Primary Key) Promotion Name (VarChar2) …
  3. SQL help - INNER JOIN to a select statement that…
    Hi EE, The SQL below IS giving me the results that i am looking for. I am coming from a Microsoft Access background, an am used to using a gui... so i need some ORACLE sql help, pls: a) this sql is joining a table to itself (STUDENT ATTRIBUTE table) because i'm looking for students 1) who are 4th year medical stude…
  4. Sql help - LEFT JOIN
    Hi EE, In a related EE post, I received guidance on how to set up an INNER JOIN - joining the same table to itself. At the time, my situation was: I'd like to query transfer students (TR) who are 2nd year medical students (M2) So was looking for students who had a TR row and an M2 row. So an equi join worked. here is th…
  5. Table Join
    I am trying to find records in which addr1, addr2, city, state, zipcode having count > 2 If there are any null values(for addr2,city,state..etc) returned for some columns by B the query below does not return any records : the first join returns data where a.ca_line_1_addr = b.ca_line_1_addr but when I add sec…

Solution

Sign up now to view this solution! It's quick, easy, and secure to subscribe. We will return you to this solution, unlocked, when you’re done.

About this Solution
View This Solution - Start Your 30 Day Free Trial
Zone
General
Tags
Oracle 10.x SQL
Participating Experts
1
Solution Grade
B

Replies

 
by: Kdo

All comments and solutions are available to Premium Service Members only. Sign up to view the solution to this question. Already a member? Log in to view this solution.

 
by: sventhan

All comments and solutions are available to Premium Service Members only. Sign up to view the solution to this question. Already a member? Log in to view this solution.

 
by: Kdo

All comments and solutions are available to Premium Service Members only. Sign up to view the solution to this question. Already a member? Log in to view this solution.

 
by: sventhan

All comments and solutions are available to Premium Service Members only. Sign up to view the solution to this question. Already a member? Log in to view this solution.

 
by: Kdo

All comments and solutions are available to Premium Service Members only. Sign up to view the solution to this question. Already a member? Log in to view this solution.

 
by: sventhan

All comments and solutions are available to Premium Service Members only. Sign up to view the solution to this question. Already a member? Log in to view this solution.

 
by:

All comments and solutions are available to Premium Service Members only. Sign up to view the solution to this question. Already a member? Log in to view this solution.

 
by: sventhan

All comments and solutions are available to Premium Service Members only. Sign up to view the solution to this question. Already a member? Log in to view this solution.

Stay Connected

Oracle Around the Web

The latest Oracle news, tips, and tricks

Oracle Unveils Netra SPARC T3 Servers

New Carrier-Grade Blade and Rackmount Servers Deliver Better Performance, Density and Power Efficiency for the Communications Industry ...

read more

Oracle fixes 21 flaws in Java SE, Java for Business

Oracle is fixing 21 flaws in its Java SE and Java for Business products in its February critical patch update issued this week ...

read more

Obama’s Silicon Valley “Tech Supper”

Why was he there? Who wasn’t invited and why? What did they talk about? Why was he really there? These are the questions being debated ...

read more

Featured Oracle Experts

slightwv

slightwv has answered more than 2,000 Oracle questions and oversees database management within the U.S. Government. Slightwv

sdstuber

A regular presenter at the Collaborate conference, Oracle developer sdstuber is a top expert in PL/SQL and Oracle 9, 10 and 11. sdstuber

Testimonials

What users are saying about our experts

"The Oracle gurus provide answers, solutions and valuable workarounds"

User: schwertner

"Wow! Excellent, valuable explanation. Hats off to you"

User: aseshu70

"Solution worked fantastic and performs excellent! Thanks heaps"

User: klausbrandlhuber

Top Experts

  1. slightwv

    2,000

    0 points yesterday

    Profile

BrowseBecome an Expert