Results 1 to 2 of 2

Thread: Subqueries

  1. #1
    Join Date
    Apr 2007
    Posts
    5

    Unanswered: Subqueries

    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.

  2. #2
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    183
    Hi,

    You could test something like the follwoing:

    select * from
    table(multiset(select tabname from systables )) as t1(c1)

    This by the way will be improved in ids cheetah, where the multiset collection type is not necessary anymore.

    Greetz,

    Rob

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •