Re: [sqr-users] Subselect

[Rebecca.Barilleaux@anthem.com]

Christopher,

You can do this exactly the way you have it coded below, but in the "ORDER
BY" use the ordinal position of the field you want to sort by.  ORDER BY
doesn't recognize the aliases (like &ID).  Try this:

begin-select
A.DATE
(select sum(amount) from TABLE_X where NAME = 'FRED') &ID
      do other_stuff
FROM TABLE_X A
WHERE A.blah = whatever
ORDER BY A.DATE, 2

Thanks,
Rebecca


>Date: Wed, 7 Jul 2004 16:18:07 -0500
>From: CAllo@hinda.com
>Subject: Re: [sqr-users] Subselect
>To: <sqr-users@sqrug.org>
>Message-ID:
<OF2BAFF65A.C46800A1-ON86256ECA.00748677-86256ECA.00750411@hinda.com>
>Content-Type: text/plain; charset=US-ASCII
>
>Thanks a bunch.  I have tried it and it works as shown, however, the
>subselect I am trying to do is a "sum" and it looks like SQR doesn't like
>that.
>i.e.
>begin-select
>A.DATE
>(select sum(amount) from TABLE_X where NAME = 'FRED') &ID
>
>By the way, the reason I'm trying to do this is so that I can ORDER BY
>A.DATE, &ID.
>
>I know I could call separate procedures to go and get this information,
but
>then I am not able to sort the data unless I build some sort of temp table
>or views.  I  wanted to be able to just return everything I need in this
>one select and sort accordingly and then print.
>
>Any further thoughts?
>
>Christopher Allo
>PeopleSoft Specialist
>x-3348





CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, 
is for the sole use of the intended recipient(s) and may contain confidential
and privileged information or otherwise protected by law.  Any unauthorized
review, use, disclosure or distribution is prohibited.  If you are not the
intended recipient, please contact the sender by reply e-mail and destroy
all copies of the original message.

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