Re: SQL For Dummies :-)

From: Arjan van Bentem <avbentem_at_DONT-YOU-DAREdds.nl>
Date: Sun, 18 Oct 1998 12:04:10 +0200
Message-ID: <70ceen$jkj$1@pascal.a2000.nl>

Eric Jodoin wrote
> Do you know How I could get a Table That would do This:

Ehhh, what exactly is your question?

>How Could I turn it into a result Like This
>BOB 2/1/1998 10.00 2/2/1998 10.00
2/3/1998

You'd need to write a function for that:

    create function invoiceInfo( pClientID in number)     return varchar2
    as

      vResult varchar2(1024);
      cursor cInvoices( pID number )
      is
        select *
        from invoice
        where clientid = pID;
    begin
      for rInvoice in cInvoices( pClientID )
      loop
        vResult := vResult
            || to_char( rInvoice.date, 'MM/DD/YYYY  ' )
            || to_char( rInvoice.amount, '999,999,990.00  ' );
      end loop;
      return vResult;

    end invoiceInfo;
    /

Now, use this function in the select:

    select name, invoiceInfo( clientid )     from client
    where clientid = 1;

Arjan. Received on Sun Oct 18 1998 - 05:04:10 CDT