Can anyone tell me how I would do a subquery in a from clause? I have mostly an Oracle backround but I've worked with SQL Server some and very little with Sybase, but I never touched Informix until just a few days ago. I see documentation for doing it through a select or a where but nothing for a from. I have a query written that would work fine if it were oracle but Informix just returns "A syntax error has occurred. (-201) (#-201)".
Here's the query I'm trying to run:
Code:
select
a.item, a.id,
sum(a.field1) as TotalField1,
sum(a.field2) as TotalField2,
sum(a.field3) as TotalField3,
sum(a.field4) as TotalField4,
sum(a.field5) as TotalField5
from
(
select
trim(s.item_name) as item, t.id, t.row_date, t.starttime,
sum(t.field1) as field1,
sum(t.field2) as field2,
sum(t.field3) as field3,
sum(t.field4) as field4,
max(t.field5) as field5
from
table1 t
inner join table2 s on t.id = trim(s.value) and s.item_type = 'name'
where
t.id >= 42000
and t.id < 43000
group by
s.item_name, t.id, t.row_date, t.starttime
) a
group by
item, id
order by
item
I realize there are flaws with the underlying database design but unfortunately it's not my design and I can't change anything at that level. I just have to write something to report from it. I also don't have direct access to the database, but instead I just have access to some read only views. It's quite restricting.
I'll be very grateful for any assistance at all.
dBforums
>
Subqueries
04-24-07, 17:19
Linear Mode
