RE: [sqr-users] sub-query question

[Don Mellen <donm@ontko.com>]

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