Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2012
    Posts
    8

    Unanswered: create view query

    I guess my 11g knowledge is slipping. I have 2 tables. Each has a KEYS column that I can join on. Table A has the details and table B has the reference user(s). I need a view that is joined on the KEYS column and there is always at least 3 results coming from table B. Here is my view:

    CREATE OR REPLACE VIEW FSA_COTTON_2012_SVW
    AS SELECT A.OBJECTID,
    A.SHAPE,
    A.KEYS,
    A.ADMNCOUNTY,
    A.ADMNSTATE,
    A.CALCACRES,
    A.CLUNBR,
    A.FARMNBR,
    A.TRACTNBR,
    B.ID,
    B.COMMONNAME, B.PRODTYPE, B.RPTACRES
    FROM FSA_COTTON_2012 A, FSA_PRODUCERS_2012 B
    WHERE A.KEYS = B.KEYS;

    As is, it returns only the first matching record from B where I need all the columns with that KEYS returned.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Since we don't have your tables or data, we can not compile, run or test posted code.
    It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
    It would be helpful if you provided DML (INSERT INTO ...) for test data.
    It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2012
    Posts
    8

    DDL for the 2 tables

    Create table "sde"."fsa_cotton_2012"
    ( "objectid" number(*,0) not null enable,
    "statecd" nvarchar2(2),
    "countycd" nvarchar2(3),
    "tractnbr" number(7,0),
    "farmnbr" number(7,0),
    "clunbr" number(7,0),
    "calcacres" number(13,2),
    "admnstate" nvarchar2(2),
    "admncounty" nvarchar2(3),
    "keys" nvarchar2(24),
    "cropcd" nvarchar2(254),
    "rptacres" number(18,5),
    "shape" "sde"."st_geometry"
    );

    CREATE TABLE "SDE"."FSA_PRODUCERS_2012"
    ( "OBJECTID" NUMBER(*,0) NOT NULL ENABLE,
    "COMMONNAME" NVARCHAR2(254),
    "ID" NVARCHAR2(254),
    "PRODTYPE" NVARCHAR2(254),
    "RPTACRES" NUMBER(20,5),
    "KEYS" NVARCHAR2(254)
    )

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    double quote marks should be avoided in Oracle

    >"keys" nvarchar2(24),
    column above must ALWAYS be referenced as "keys"; including double quote marks
    >"KEYS" NVARCHAR2(254)

    why different sizes across the two tables?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Aug 2012
    Posts
    8
    Good question. The table was created from an MS Access export into Oracle using ArcCatalog from ESRI. (The first table is Spatial).

  6. #6
    Join Date
    Aug 2012
    Posts
    8
    Sample data to enter for FSA_COTTON_2012 :

    OBJECTID 1,
    STATECD 48,
    COUNTYCD 225,
    TRACTNBR 2657,
    FARMNBR 2441,
    CLUNBR 22,
    CALCACRES 76.28,
    ADMNSTATE 48,
    ADMNCOUNTY 001,
    KEYS 48001000244100026570022,
    CROPCD 0021,
    RPTACRES 76.3

  7. #7
    Join Date
    Aug 2012
    Posts
    8
    For the PRODUCERS_2012 table:

    CommonName Id ProdType RptAcres Keys
    ADAM K MASON 449698695 OT 76.3 48001000244100026570022
    ALLANA N MASON 644224180 OT 76.3 48001000244100026570022
    JOHN BILLINGSLEY 450374224 OT 76.3 48001000244100026570022
    SOUTHERN PINES 582033680 OW 76.3 48001000244100026570022
    TIMOTHY A WOELFEL 455277709 OP 76.3 48001000244100026570022

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It would be helpful if you provided DML (INSERT INTO ...) for test data.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Aug 2012
    Posts
    8
    no problem. Just a sec

  10. #10
    Join Date
    Aug 2012
    Posts
    8
    Create table fsa_cotton_2012_test
    ( OBJECTID number(*,0) not null enable,
    STATECD nvarchar2(2),
    COUNTYCD nvarchar2(3),
    TRACTNBR number(7,0),
    FARMNBR number(7,0),
    CLUNBR number(7,0),
    CALCACRES number(13,2),
    ADMNSTATE nvarchar2(2),
    ADMNCOUNTY nvarchar2(3),
    KEYS nvarchar2(24),
    CROPCD nvarchar2(254),
    RPTACRES number(18,5)
    );


    CREATE TABLE FSA_PRODUCERS_2012_test
    ( OBJECTID NUMBER(*,0) NOT NULL ENABLE,
    COMMONNAME NVARCHAR2(254),
    ID NVARCHAR2(254),
    PRODTYPE NVARCHAR2(254),
    RPTACRES NUMBER(20,5),
    KEYS NVARCHAR2(24)
    );

    Insert into fsa_cotton_2012_test values(
    1,'48','225',2657,2441,22,76.28,'48','001','480010 00244100026570022','0021',76.3);

    Insert into fsa_producers_2012_test values(
    1,'ADAM K MASON','449698695','OT',76.3,'48001000244100026570 022');

    Insert into fsa_producers_2012_test values(
    2,'ALLANA N MASON','644224180','OT',76.3,'48001000244100026570 022');

    Insert into fsa_producers_2012_test values(
    3,'JOHN BILLINGSLEY','450374224','OT',76.3,'48001000244100 026570022');

    Insert into fsa_producers_2012_test values(
    4,'SOUTHERN PINES','582033680','OW',76.3,'48001000244100026570 022');

    Insert into fsa_producers_2012_test values(
    5,'TIMOTHY A WOELFEL','455277709','OP',76.3,'480010002441000265 70022');

  11. #11
    Join Date
    Aug 2012
    Posts
    8
    Nevermind. Sorry to waste your time. It appears to be an error in my spatial software when the query is generated, it is designed to only get 1 row back.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •