[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, 21 Sep 2005 14:01:21 -0400
Delivery-date: Wed, 21 Sep 2005 13:05:05 -0500
In-reply-to: <CC5702C624CD4B4399F971F17C1E2E350DB4D6@itomae2km09.AD.QINTRA.COM>
List-id: "This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions." <sqr-users.sqrug.org>
References: <CC5702C624CD4B4399F971F17C1E2E350DB4D6@itomae2km09.AD.QINTRA.COM>

This is an old question and the problem was solved with help from Don Mellen.

The query would work with or without the comma though.  That was not the 
problem.  Thanks.

A.

At 01:54 PM 9/21/2005, Turner, Ivan wrote:
>Did you take the comma out?
>
>begin-select
>spriden_id,<--------------
>
>-----Original Message-----
>From: sqr-users-bounces+ivan.turner=qwest.com@sqrug.org
>[mailto:sqr-users-bounces+ivan.turner=qwest.com@sqrug.org] On Behalf Of
>Amy Eldridge
>Sent: Wednesday, August 10, 2005 7:14 AM
>To: sqr-users@sqrug.org
>Subject: RE: [sqr-users] sub-query question
>
>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
>
>_______________________________________________
>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: "Turner, Ivan" <Ivan.Turner@qwest.com>

Prev by Date: [sqr-users] Question about TOP and SQR
Next by Date: RE: [sqr-users] FTP -s help needed
Previous by thread: RE: [sqr-users] sub-query question
Next by thread: [sqr-users] How will the sqr get the values ?
Indexes: [ Thread] [ Author] [ Date]
SQR-USERS List and Archive Info
SQRUG Home Page