Re: [sqr-users] SQR SQL Sub-Selects

["George Jansen" <GJANSEN@aflcio.org>]

The use of in-line views certainly works in SQR, however there are
problems with the SQL

1. You aren't select amt1,2,or 3 out of your in-line view, just acct
and record_type

2. You aren't naming the expression as "record_type"

3. You can order by 1,2,..,n, but you have to group by the actual
columns or expressions selected. (Unless Informix will let you--Oracle
certainly won't.)

4. In any case, you don't need to group in the in-line view, inner
select or whatever you're calling it


>>> Kent.Cassidy@LibertyMutual.com 03/11/04 09:10AM >>>
Hi All,

I have the following SQL that I would like to use in SQR.  This goes
against an Informix database.

select
        acct,
        record_type,
        sum(amt1),
        sum(amt2),
        sum(amt3)
from
(
        select
                acct,
                case when amt1 > 0 and amt1 < 5000 then "record_type1
else then "record_type2" end,
                case when status = "open" and amt1 > 0 then 1 else 0
end,
                case when status = "open" and amt2 > 0 then 1 else 0
end,
                case when status = "open" and amt3 > 0 then 1 else 0
end
        from table1
        where (where clause)
        group by 1, 2
) as temp1

group by 1, 2
order by 1
;

Does anyone know if this is possible in SQR?

Thank you,
Kent

_______________________________________________
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