Re: [sqr-users] Use with-clause in begin-select Statement

["Don Mellen" <donm@ontko.com>]

If you need to use the "WITH" clause, you can try something like...

begin-select
<columns>

   <sqr code>

from (WITH
        MY_SQL_DEF1 AS
          (Select /*+ materialize */
            <any_columns>
           From <table>),
        MY_SQL_DEF2 AS
          (Select /*+ materialize */
            <any_columns>
           FROM MY_SQL_DEF1)
      SELECT
        <any_columns>
      FROM MY_SQL_DEF2
     )
end-select

HTH,
Don



On Tue, Apr 1, 2008 at 1:47 AM, Hug Marcel (KSFH 344) <
marcel.hug@credit-suisse.com> wrote:

> Hi,
>
> does not work, unfortunately. Will probably put the whole def-clause into
> a
> variable and use this in an inline select.
>
> Marcel
>
>
> Marcel Hug
> Credit Suisse
> Information Technology
> Solution Engineering, KSFH 344
> Oracle | Hyperion Lead Ing. FN Reports
> Zollstrasse 20/36
> CH-8005 Zürich
> Tel: +41 1 334 74 20
> mailto:marcel.hug@credit-suisse.com
> http://www.credit-suisse.com
>
>  -----Original Message-----
> From: sqr-users-bounces+marcel.hug=credit-suisse.com@sqrug.org [mailto:
> sqr-users-bounces+marcel.hug <sqr-users-bounces%2Bmarcel.hug>=
> credit-suisse.com@sqrug.org] On Behalf Of franck.masson
> Sent: Tuesday, April 01, 2008 12:44 AM
> To: This list is for discussion about the SQR database reporting
> languagefrom Hyperion Solutions.
> Subject: RE: [sqr-users] Use with-clause in begin-select Statement
>
> have you tried something like this
>
> begin-sql
> WITH
> MY_SQL_DEF1 AS (Select
>     <any_columns> From <table>
> ),
> MY_SQL_DEF2 AS (Select
>     <any_columns>
>  FROM
>     MY_SQL_DEF1
> );
> end-sql
>
> and then:
> let $my_select='MY_SQL_DEF1'
> begin-select
> SELECT
>     <any_columns>
> FROM
>  [$my_select]
> end-select
>
>
>
> > Message du 01/04/08 00:28
> > De : "Paul Hoyte"
> > A : "'This list is for discussion about the SQR database
> reportinglanguage  from Hyperion Solutions.'"
> > Copie à :
> > Objet : RE: [sqr-users] Use with-clause in begin-select Statement
> >
> > Hyperion SQR 8.5 and earlier does not support this functionality. We
> wanted
> > to use this as well and when we called support they were not even aware
> of
> > the Oracle function. Don't know about SQR9 yet though.
> >
> >
> > Paul Hoyte
> > (248) 752-6970
> > pa_hoyte@si-proservices.com
> >
> > -----Original Message-----
> > From: sqr-users-bounces+pi_hoyte=si-proservices.com@sqrug.org
> > [mailto:sqr-users-bounces+pi_hoyte <sqr-users-bounces%2Bpi_hoyte>=
> si-proservices.com@sqrug.org] On Behalf
> > Of Hug Marcel (KSFH 344)
> > Sent: Monday, March 31, 2008 10:56 AM
> > To: sqr-users@sqrug.org
> > Subject: [sqr-users] Use with-clause in begin-select Statement
> >
> > Hi,
> >
> > we want to use the WITH Oracle statement to make reuse of sql
> definitions.
> > E.g.:
> >
> > begin-select
> > WITH
> > MY_SQL_DEF1 AS (Select
> > From
> > ),
> > MY_SQL_DEF2 AS (Select
> >
> > FROM
> > MY_SQL_DEF1
> > )
> > end-select
> >
> > and then:
> >
> > begin-select
> > SELECT
> >
> > FROM
> > MY_SQL_DEF2
> > end-select
> >
> > How is this possible? Or do we have a begin-sql statement?
> >
> > Thx - Marcel
> >
> > Marcel Hug
> > Credit Suisse
> > Information Technology
> > Solution Engineering, KSFH 344
> > Oracle | Hyperion Lead Ing. FN Reports
> > Zollstrasse 20/36
> > CH-8005 Zürich
> > Tel: +41 1 334 74 20
> > mailto:marcel.hug@credit-suisse.com
> > http://www.credit-suisse.com
> >
> > _______________________________________________
> > sqr-users mailing list
> > sqr-users@sqrug.org
> > http://www.sqrug.org/mailman/listinfo/sqr-users
> >
> >
> >
> > _______________________________________________
> > sqr-users mailing list
> > sqr-users@sqrug.org
> > http://www.sqrug.org/mailman/listinfo/sqr-users
> >
> >
>
>  Créez votre adresse électronique prénom.nom@laposte.net
>  1 Go d'espace de stockage, anti-spam et anti-virus intégrés.
>
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org
> http://www.sqrug.org/mailman/listinfo/sqr-users
>
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org
> http://www.sqrug.org/mailman/listinfo/sqr-users
>



-- 
-----------------------------------------------------------------------
Donald Mellen | Ray Ontko & Co. - Richmond, IN - http://www.ontko.com/
donm@ontko.com | "In the beginning, there was nothing, which exploded"
(Terry Pratchett)

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users