Professional Web Applications Themes

sql more intelligent - Oracle Server

hi all if i have a table with name char(30) group char(30) the name refers to 'schema' names, can i circulate through this table and union the results of other tables across more than one schema for example say we have the following data in this table name group fred high thomas low john high mark medium so i first to get the schema's i am interested in "select name from thistable where group = 'high'" - no i want to do this select firstschema.result+secondschema.result from commontable; now in this example firstschema would be fred, and secondschema would be john, ...

  1. #1

    Default sql more intelligent

    hi all

    if i have a table with

    name char(30)
    group char(30)

    the name refers to 'schema' names, can i circulate through this table
    and union the results of other tables across more than one schema

    for example say we have the following data in this table

    name group
    fred high
    thomas low
    john high
    mark medium

    so i first to get the schema's i am interested in "select name from
    thistable where group = 'high'" - no i want to do this select
    firstschema.result+secondschema.result from commontable;

    now in this example firstschema would be fred, and secondschema would be
    john, bearing in mind that we dont always have a static number of names
    returned from the first select, how do a union the results together?

    thanks in advance for your assistance
    paul
    --
    Your favorite stores, helpful shopping tools and great gift ideas.
    Experience the convenience of buying online with ShopNetscape!
    [url]http://shopnow.netscape.com/[/url]

    Paul Guest

  2. #2

    Default Re: sql more intelligent

    Paul wrote:
    > hi all
    >
    > if i have a table with
    >
    > name char(30)
    > group char(30)
    >
    > the name refers to 'schema' names, can i circulate through this table
    > and union the results of other tables across more than one schema
    >
    > for example say we have the following data in this table
    >
    > name group
    > fred high
    > thomas low
    > john high
    > mark medium
    >
    > so i first to get the schema's i am interested in "select name from
    > thistable where group = 'high'" - no i want to do this select
    > firstschema.result+secondschema.result from commontable;
    >
    > now in this example firstschema would be fred, and secondschema would be
    > john, bearing in mind that we dont always have a static number of names
    > returned from the first select, how do a union the results together?
    You can do this by writing a PL/SQL procedure. Do the first select
    ("select name from thistable where group = 'high'") and put the result
    into a cursor. Then iterate through this cursor while building the
    dynamic SQL statement for the second statement ("select
    firstschema.result+secondschema.result from commontable"). Then execute
    the dynamic SQL and return the result.

    Cheers,
    Dave




    >
    > thanks in advance for your assistance
    > paul
    Dave Hau Guest

  3. #3

    Default Re: sql more intelligent

    thanks Dave, so how do you use cursors? can you give me a quick example,
    thanks in advance

    Dave Hau wrote:
    > Paul wrote:
    >
    >> hi all
    >>
    >> if i have a table with
    >>
    >> name char(30)
    >> group char(30)
    >>
    >> the name refers to 'schema' names, can i circulate through this table
    >> and union the results of other tables across more than one schema
    >>
    >> for example say we have the following data in this table
    >>
    >> name group
    >> fred high
    >> thomas low
    >> john high
    >> mark medium
    >>
    >> so i first to get the schema's i am interested in "select name from
    >> thistable where group = 'high'" - no i want to do this select
    >> firstschema.result+secondschema.result from commontable;
    >>
    >> now in this example firstschema would be fred, and secondschema would
    >> be john, bearing in mind that we dont always have a static number of
    >> names returned from the first select, how do a union the results
    >> together?
    >
    >
    > You can do this by writing a PL/SQL procedure. Do the first select
    > ("select name from thistable where group = 'high'") and put the result
    > into a cursor. Then iterate through this cursor while building the
    > dynamic SQL statement for the second statement ("select
    > firstschema.result+secondschema.result from commontable"). Then execute
    > the dynamic SQL and return the result.
    >
    > Cheers,
    > Dave
    >
    >
    >
    >
    >
    >>
    >> thanks in advance for your assistance
    >> paul
    >
    >

    --
    Your favorite stores, helpful shopping tools and great gift ideas.
    Experience the convenience of buying online with ShopNetscape!
    [url]http://shopnow.netscape.com/[/url]

    Paul Guest

  4. #4

    Default Re: sql more intelligent

    You use cursors like so:


    create or replace procedure my_procedure( ... )
    as
    CURSOR c1 IS SELECT name FROM thistable WHERE group = 'high';
    my_rec c1%rowtype;
    begin
    open c1;
    loop
    fetch c1 into my_rec;
    exit when c1%NOTFOUND;
    ...
    end loop;
    end;


    Cheers,
    Dave



    Paul wrote:
    > thanks Dave, so how do you use cursors? can you give me a quick example,
    > thanks in advance
    >
    > Dave Hau wrote:
    >
    >> Paul wrote:
    >>
    >>> hi all
    >>>
    >>> if i have a table with
    >>>
    >>> name char(30)
    >>> group char(30)
    >>>
    >>> the name refers to 'schema' names, can i circulate through this table
    >>> and union the results of other tables across more than one schema
    >>>
    >>> for example say we have the following data in this table
    >>>
    >>> name group
    >>> fred high
    >>> thomas low
    >>> john high
    >>> mark medium
    >>>
    >>> so i first to get the schema's i am interested in "select name from
    >>> thistable where group = 'high'" - no i want to do this select
    >>> firstschema.result+secondschema.result from commontable;
    >>>
    >>> now in this example firstschema would be fred, and secondschema would
    >>> be john, bearing in mind that we dont always have a static number of
    >>> names returned from the first select, how do a union the results
    >>> together?
    >>
    >>
    >>
    >> You can do this by writing a PL/SQL procedure. Do the first select
    >> ("select name from thistable where group = 'high'") and put the result
    >> into a cursor. Then iterate through this cursor while building the
    >> dynamic SQL statement for the second statement ("select
    >> firstschema.result+secondschema.result from commontable"). Then
    >> execute the dynamic SQL and return the result.
    >>
    >> Cheers,
    >> Dave
    >>
    >>
    >>
    >>
    >>
    >>>
    >>> thanks in advance for your assistance
    >>> paul
    >>
    >>
    >>
    >
    >
    Dave Hau Guest

Similar Threads

  1. Intelligent PDF Splitting
    By Fraser_Edward_Crozier@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 3
    Last Post: September 5th, 09:32 PM
  2. Intelligent directory indexing
    By nodata in forum PERL Modules
    Replies: 2
    Last Post: June 12th, 09:32 PM
  3. can everyone help me? about intelligent miner
    By ludongyan in forum IBM DB2
    Replies: 4
    Last Post: September 11th, 12:57 AM
  4. Intelligent ISDN TA
    By someone in forum Linux Setup, Configuration & Administration
    Replies: 0
    Last Post: July 19th, 02:43 PM
  5. Intelligent Agent
    By Bobby Singh in forum Oracle Server
    Replies: 0
    Last Post: December 6th, 09:50 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139