[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index] [Date Index] [Thread Index]
[SQR-USERS Info] [SQRUG Home Page]

RE: [sqr-users] sub-query question

Subject: RE: [sqr-users] sub-query question
From: Amy Eldridge <emgeac@muohio.edu>
Date: Wed, 10 Aug 2005 07:13:33 -0400
Delivery-date: Wed, 10 Aug 2005 06:14:33 -0500
In-reply-to: <62AFC819CCF3D611827A00065B3F798106BA8D18@sj-exchange.ci.sj.ca.us>
List-id: "This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions." <sqr-users.sqrug.org>
References: <62AFC819CCF3D611827A00065B3F798106BA8D18@sj-exchange.ci.sj.ca.us>

Sorry, I guess I did not make it clear.

I know there are MANY alternatives to the sub-query, but what I'm really 
wanting to know is if wrapping the sub-query will work.

But thanks.

A.

At 05:45 PM 8/9/2005, Alexander, Steve wrote:
>Database optimizers often promote subqueries to joins.  In this case,
>
>begin-select
>a.spriden_id
>b.pebempl_pidm &pidm
>
>  from spriden a, pebempl b
>where b.pebempl_pidm = a.spriden_pidm
>end-select
>
>Alternately, you could try:
>
>begin-select
>a.spriden_id
>b.pebempl_pidm &pidm
>
>  from spriden a,
>(select pebempl_pidm
>from pebempl b
>where b.pebempl_pidm = a.spriden_pidm)
>end-select
>
>-----Original Message-----
>From: Roger.Harris@certegy.com [mailto:Roger.Harris@certegy.com]
>Sent: Tuesday, August 09, 2005 1:22 PM
>To: This list is for discussion about the SQR database reporting
>languagefrom Hyperion Solutions.
>Subject: RE: [sqr-users] sub-query question
>
>
>
>Ok, just sent the same suggestion. Here is another. Have you left the comma
>after spriden_id?
>
>
>
>|---------+---------------------------------------------------->
>|         |           "Amy Eldridge" <emgeac@muohio.edu>       |
>|         |           Sent by:                                 |
>|         |           sqr-users-bounces+roger.harris=certegy.co|
>|         |           m@sqrug.org                              |
>|         |                                                    |
>|         |                                                    |
>|         |           08/09/2005 04:16 PM                      |
>|         |           Please respond to "This list is for      |
>|         |           discussion about the SQR database        |
>|         |           reporting  language from Hyperion        |
>|         |           Solutions."                              |
>|         |                                                    |
>|---------+---------------------------------------------------->
>
> >---------------------------------------------------------------------------
>-------------------|
>   |
>|
>   |        To:      sqr-users@sqrug.org
>|
>   |        cc:
>|
>   |        Subject: RE: [sqr-users] sub-query question
>|
>
> >---------------------------------------------------------------------------
>-------------------|
>
>
>
>Indenting does not help - sorry, I should have noted that in the original
>email.
>
>As I wrote, I know there are many alternatives to the sub-query (including
>calling a procedure) but what I'm really wanting to know is if wrapping the
>
>sub-query will work.
>
>Thanks.
>
>a.
>
>At 04:07 PM 8/9/2005, Bob Stone wrote:
> >dont line it up on the lefthand side.  It thinks that each thing on the
> >left hand side is a new column/expression.  ident.  if that doesn't work,
> >you can just use a subquery inside the table (nest a procedure).
> >
> >begin-select
> >spriden_id,
> >(select pebempl_pidm
> >         from pebempl
> >         where pebempl_pidm = spriden_pidm)  &pidm
> >
> >from spriden
> >end-select
> >
> >-------------------------nest procedure
> >begin-select
> >spriden_id
> >   let $spriden_id = &spriden_id
> >   do get-peb-id( $spriden_id, $peb_id)
> >
> >from spriden
> >end-select
> >
> >!procedure GET-PEB-ID:  used to get the pebid.
> >begin-procedure get-peb-id($sprid_in , :$peb_id_out)
> >begin-select
> >select
> >pebempl_pidm
> >   let $peb_id_out = &pebempl_pidm
> >from pebempl
> >where pebempl_pidm = sprid_in
> >end-select
> >end-proceddure
> >
> >
> >
> >-----Original Message-----
> >From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
> >[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org]On Behalf Of Amy
> >Eldridge
> >Sent: Tuesday, August 09, 2005 3:00 PM
> >To: sqr-users@sqrug.org
> >Subject: [sqr-users] sub-query question
> >
> >
> >
> >This is a fake query to illustrate my question.
> >
> >I'm using a sub-query like this:
> >
> >begin-select
> >spriden_id,
> >(select pebempl_pidm from pebempl where pebempl_pidm = spriden_pidm)
>&pidm
> >
> >from spriden
> >end-select
> >
> >but I want to wrap the sub-query like this:
> >
> >begin-select
> >spriden_id,
> >(select pebempl_pidm
> >from pebempl
> >where pebempl_pidm = spriden_pidm)  &pidm
> >
> >from spriden
> >end-select
> >Why wrap it, you ask?  My sub-query is really long and I don't want to
>make
> >it all one line stretching out to infinity.  In addition, SQR will only
> >allow a line to be so long and my sub-query one big line exceeds
> >that.  Thus, I want to wrap it.
> >
> >When writing the above as a SQL query, wrapping is not a problem but when
>I
> >try to wrap it in SQR, though, it is giving me an error:
> >
> >Error on line 14:
> >     (SQR 3727) SQL expression not ended, perhaps parentheses not
>balanced.
> >from pebempl
> >
> >It does not like that return after the "select pebempl_pidm".
> >
> >Yes, I can use a separate procedure instead of the sub-query - I have lots
> >of alternatives so don't pass alternative suggestions along, please.
> >
> >I'm just curious if wrapping the subquery is possible.
> >
> >Thanks in advance.
> >
> >Amy Eldridge
> >Systems Analyst
> >Miami University (nope, the one in Ohio)
> >
> >_______________________________________________
> >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
>
>
>_______________________________________________
>sqr-users mailing list
>sqr-users@sqrug.org
>http://www.sqrug.org/mailman/listinfo/sqr-users
>
>
>
>
>
>
>
>
>
>----------------------------------------------------------------------------
>--
>This message contains information from Certegy, Inc which may be
>confidential and privileged.  If you are not an intended recipient, please
>refrain from any disclosure, copying, distribution or use of this
>information and note that such actions are prohibited.  If you have received
>this transmission in error, please notify by e:mail postmaster@certegy.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


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

References:
- RE: [sqr-users] sub-query question
  - From: "Alexander, Steve" <Steven.Alexander@sanjoseca.gov>

Prev by Date: RE: [sqr-users] sub-query question
Next by Date: RE: [sqr-users] sub-query question - solved!
Previous by thread: RE: [sqr-users] sub-query question
Next by thread: RE: [sqr-users] sub-query question
Indexes: [ Thread] [ Author] [ Date]
SQR-USERS List and Archive Info
SQRUG Home Page