RE: [sqr-users] sub-query question - solved!

[Amy Eldridge <emgeac@muohio.edu>]

You're the man, Don!

You are right.  This query runs:

begin-select
spriden_id                               &id
(select pebempl_pidm from pebempl
where pebempl_pidm = spriden_pidm)  &pidm

     print &id (+1,1)
     print &pidm (,30)

from spriden
end-select

Now I know!

Thanks for all who replied and especially Don for the answer.

A.

At 05:40 PM 8/9/2005, Don Mellen wrote:
>Amy,
>
>When SQR is looking at your code, it will consider the first line that
>begins with a "from" as the from for the begin-select statement.
>
>This is also a problem for how you format lines with a "get" from an array
>within a begin-select.
>
>Just make sure that the "from" has something before it, like ...
>
>begin-select
>sysdate                             &now=date
>(select sysdate - 1 from dual
>                         ,dual d3
>          where 1 = 1
>            and 2 = 2             ) &yest=date
>sysdate - 3                        &three_days_ago=date
>
>   show 'Now        ' &now            edit 'MM/DD/YYYY HH24:MI:SS'
>   show 'Yesterday  ' &yest           edit 'MM/DD/YYYY HH24:MI:SS'
>   show '3 days ago ' &three_days_ago edit 'MM/DD/YYYY HH24:MI:SS'
>
>from dual d1
>where 1 = 1
>end-select
>
>HTH,
>Don
>
>
>On Tue, 9 Aug 2005, Amy Eldridge wrote:
>
> > 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
> >
>
>-----------------------------------------------------------------------
>Donald Mellen  | Ray Ontko & Co. - Richmond, IN - http://www.ontko.com/
>donm@ontko.com |  "In the beginning, there was nothing, which exploded"
>
>
>
>_______________________________________________
>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