0

I am working on a piece of SQL for a IBM U2 Rocket database. It's not a flavour of db platform I'm familiar with.

I do not have direct access to this database: I can only access it by composing statements in the calling code and testing it from there. That makes it kind of awkward to figure out where the problem is when there are syntax errors.

I'm trying to compose a query which has a condiational on a dervied column. As far as I can tell from what I've read about the database, this ought to work:

SELECT a.*
FROM (
    SELECT dp.NAME
        ,dp.Code
        ,dp.BusinessType + ts.BusinessType AS bType
    FROM dataPoints dp
    LEFT OUTER JOIN trialSuppliers ts
    WHERE ts.AccountStatus = 'A'
    ) a
WHERE a.bType LIKE '%cho%'

However, it throws this error:

Died in UCI::SQLExecDirect() with SQLSTATE 37000, Native error:0 [IBM][SQL Client][UNIDATA]

If you just run the inner query, it works fine. Trying to use any kind of inner select statement causes it to throw the same error, i.e. this:

SELECT *
FROM (
    SELECT dp.NAME
        ,dp.Code
        ,dp.BusinessType + ts.BusinessType AS bType
    FROM dataPoints dp
    LEFT OUTER JOIN trialSuppliers ts
    WHERE ts.AccountStatus = 'A'
    )

Still fails.

What's the correct syntax to be able to filter my query by the derived column?

Bob Tway
  • 9,301
  • 17
  • 80
  • 162
  • Just filter on your derived column in your inner `WHERE` statement `WHERE ts.AccountStatus = 'A' AND bType LIKE '%cho%'` (I'm not familiar with Rocket, but I assume this is legal). In the event that that doesn't work then surely: `WHERE ts.AccountStatus = 'A' AND dp.BusinessType + ts.BusinessType LIKE '%cho%'` would work fine. – JNevill Aug 07 '17 at 14:01
  • @JNevill That's where I started: neither appear to be valid syntax. – Bob Tway Aug 07 '17 at 14:24
  • I am not a UniData user but in its half cousin UniVerse the "from clause" has to be a table. You have to do sub-queries in the where clause. I would do what you want to do adding a couple of I-descriptors in the dictionary. I have often found the limited SQL compliance of the U2 products to be somewhat of a hindrance, but when your metadata is external to and separate from your data you are going to lose a bit of structured query sanity. – Van Amburg Sep 21 '17 at 17:31

1 Answers1

0

Just doing some clean-up, did the last comment resolve the issue?

I am not a UniData user but in its half cousin UniVerse the "from clause" has to be a table. You have to do sub-queries in the where clause. I would do what you want to do adding a couple of I-descriptors in the dictionary. I have often found the limited SQL compliance of the U2 products to be somewhat of a hindrance, but when your metadata is external to and separate from your data you are going to lose a bit of structured query sanity. – Van Amburg Sep 21 '17 at 17:31

Mike
  • 194
  • 11