RE: [sqr-users] sub-query question

["Alexander, Steve" <Steven.Alexander@sanjoseca.gov>]

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