Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: problem with create view....

    Hi,
    I've this table (3000 record): AX
    cod.................name..............area
    01...................abc..................10
    02...................def...................20
    anc-01.............aaa...................15
    axy-02.............xxx...................40
    ast-04..............tttt....................100

    and table NS
    cod................city
    01
    02
    04

    I'd like to create a view that get from AX and NS with join between cod.

    I tried this
    CREATE OR REPLACE VIEW PT_TEST ( CITY,
    NAME, cod, AREA
    ) AS (select ns.city,ax.name, sum(area)
    from ax, ns
    WHERE substr(ax.COD,2)=ns.cod
    group by ns.city,ax.name)


    but is incorrect because I haven't avery record of AX.

    How can I resolve it?

    Thanks
    Raf

  2. #2
    Join Date
    Jul 2002
    Posts
    227
    sorry,
    WHERE substr(ax.COD,4)=ns.cod

  3. #3
    Join Date
    Jan 2004
    Location
    Hyderabad, India
    Posts
    37
    You want to see the records of AX for which exact match is there in NS table??

    Mention the result which you are expecting...
    Regards
    Suneel

  4. #4
    Join Date
    Jul 2002
    Posts
    227
    Originally posted by suneel.kumar
    You want to see the records of AX for which exact match is there in NS table??

    Mention the result which you are expecting...
    in view PT_TEST I'd like to see any record that match with number code of AX

    i.e. PT_TEST:
    cod
    01
    02
    03
    04
    ...


    Raf

  5. #5
    Join Date
    Nov 2003
    Posts
    87
    SQL> CREATE OR REPLACE VIEW
    2 PT_TEST AS
    3 (select ns.city,ax.name, sum(area)as sarea
    4 from ax, ns
    5 WHERE substr(ax.COD,5)=ns.cod
    6 group by ns.city,ax.name)
    7 /

    View created.

    SQL> select * from PT_TEST;

    CITY NAME SAREA
    -------------------- -------------------- ----------
    ffd axxx 40
    ddfd aaa 15
    dfdfd ttt 1000

  6. #6
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    You have asked for two different things, first you mentioned "every record in AX", then "you said only the matchs". Which one is it?

    Maybe it doesn't matter. Have you tried just running
    "select substr(ax.COD,4) from AX" ? If you do that I think you will see your problem. Hint: you are missing all the records that do not have a "dash" but the dash is not the problem.
    NOTE: Please disregard the label "Senior Member".

Posting Permissions

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