Create table script 2

[tmcneil]

I have three scripts, two of which are joined to create a summary table.

The 1st script:

create table hmda_hud_fha_limit_04_1 as (select tract_id, count(objectid) total
from HMDA_HUD_FHA_LIMIT_04
where ln_type = '1' and
      proptype = '1' and
      purpose = '1' and
      own_occ = '1' and
      action = '1' and
      lien_flg = '1'
group by tract_id);

2nd script:

create table hmda_hud_fha_limit_04_2 as (select tract_id, count(objectid) high_cost
from HMDA_HUD_FHA_LIMIT_04
where ln_type = '1' and
      proptype = '1' and
      purpose = '1' and
      own_occ = '1' and
      action = '1' and
      lien_flg = '1' and
      rate_spr > 2.99
group by tract_id);

This is the table result that I would like:

create table hmda_tractsum_04 as (select A.tract_id, A.total, B.high_cost
from HMDA_HUD_FHA_LIMIT_04_1 A,
     HMDA_HUD_FHA_LIMIT_04_2 B
where A.tract_id = B.tract_id(+));

Any ideas on how to combine the 1st two scripts to get the output of the 3rd script.

Thanks,
Todd

 

[jaxtell]

Todd,
You just want to run one script to create your hmda_tractsum_04 table?

CREATE TABLE hmda_tractsum_04 AS (SELECT a.tract_id, a.total, b.high_cost FROM (SELECT tract_id, COUNT(objectid) total
FROM hmda_hud_fha_limit_04
WHERE ln_type = '1' AND
      proptype = '1' AND
      purpose = '1' AND
      own_occ = '1' AND
      action = '1' AND
      lien_flg = '1'
GROUP BY tract_id) a,
(SELECT tract_id, COUNT(objectid) high_cost
FROM hmda_hud_fha_limit_04
WHERE ln_type = '1' AND
      proptype = '1' AND
      purpose = '1' AND
      own_occ = '1' AND
      action = '1' AND
      lien_flg = '1' AND
      rate_spr > 2.99
GROUP BY tract_id) b
WHERE a.tract_id = b.tract_id(+));

 

[tmcneil]

jaxtell,

Yes! I'll try out your code. First, I may have missed some steps prior to this one. Some of the info. is not looking right. So, I need to retrace my steps to see if I missed anything.

Thanks,
Todd

 

[tmcneil]

Jaxtell,

Worked like a charm. Thanks!

Todd

 

[tmcneil]

I have another create table script that I have been using for about a day or so and it was working. Now, I use it this morning and it does not work.

Here is the script:

CREATE TABLE HMDA_HUD_FHA_LIMIT_06 as (SELECT A.OBJECTID,
        A.RESP_ID,
        A.AGENCY,
        A.LN_TYPE,
        A.PROPTYPE,
        A.PURPOSE,
        A.OWN_OCC,
        A.LN_AMNT,
        A.PRE_APPR,
        A.ACTION,
        A.CBSA,
        A.STATEFP||A.CNTY CNTY_FIPS,
        A.STATEFP||A.CNTY||A.TRACT TRACT_ID,
        A.BOETHNIC,
        A.COETHNIC,
        A.BO_RACE1,
        A.BO_RACE2,
        A.BO_RACE3,
        A.BO_RACE4,
        A.BO_RACE5,
        A.CO_RACE1,
        A.CO_RACE2,
        A.CO_RACE3,
        A.CO_RACE4,
        A.CO_RACE5,
        A.BO_SEX,
        A.CO_SEX,
        A.ANN_INC,
        A.PUR_TYPE,
        A.RATE_SPR,
        A.HOEPAFLG,
        A.LIEN_FLG,
        A.SEQ_NO,
        A.P2004FLG,
        B.FHA_LIMIT_1UNIT,
        B.FHA_LIMIT_2UNIT,
        B.FHA_LIMIT_3UNIT,
        B.FHA_LIMIT_4UNIT
FROM HMDA_2006 A,
     HUD_FHA_LIMIT_06 B
WHERE B.GEOID = A.STATEFP||A.CNTY(+));

When executed, I get this error:

WHERE B.GEOID = A.STATEFP||A.CNTY(+)
              *
ERROR at line 41:
ORA-01417: a table may be outer joined to at most one other table

I'm not sure what's wrong with it, but it looks ok to me.

Todd

 

[jaxtell]

I don't think it makes sense to have an outer join on one column of the table and not the other. I think it should be both, or neither.

 

[jimirvine]

try using ansi syntax:
FROM HMDA_2006 A
left join
HUD_FHA_LIMIT_06 B
ON B.GEOID = A.STATEFP||A.CNTY;

 

[Dagon]

This seems to work:

select * from
(select 'a' as j, 'b' as k from dual where 1=2) d1,
(select 'ab' as k from dual) d2
where d2.k = (d1.J (+)||d1.K (+))