subquery must have an alias

Question

I have this query I have written in oracle that returns an error saying: subquery must have an alias any help please i need to convert it to PostgreSQL so the query will executed in order to create a pipeline in elasticserver.

Answer 1

I guess it is quite self explanatory - why didn't you already do that?

... from
                (
                  select flowid_,nbrecords_,externalid_,upper(substr(externalid_,44,4)) fn,upper(substr(externalid_,48,1)) sfn,
                          status_,creationdate_, 
                          upper(substr(externalid_,position('/' in externalid) -1)+1))  ffn
                          from broker_flowin 
                          where INPUTDEVICE_DEVICEID_='INSTRUMENTPRICE_IN'
                          and to_javadate(creationdate_)>:sql_last_value
                ) x
                  ^
                  |
               alias

In PostreSQL, that might look like ) as x (but - as you mentioned Oracle - in Oracle subquery (or a table) can have an alias, but you can't use the as keyword there).

---

As of converting the whole code to PostgreSQL, first of all - I don't use that database so I'll try my best but can't promise anything. Besides, I'm not sure that code you posted actually is Oracle.

A few comments:

• there's no TO_JAVADATE function in Oracle (at least, as far as I can tell)
• /* ${cinstall} */ is just a comment; that's not a hint (if you meant to use it; maybe it means something in PostgreSQL)
• instead of instr function, PostgreSQL uses position
• PostgreSQL has decode function, but its purpose is different from the one in Oracle. Use case expression instead

Therefore, that code (converted to PostgreSQL) might look like this:

SELECT /* ${cinstall}  */     --> in Oracle, this is just a comment
    flowid_ AS pk_,
    CASE
        WHEN position('AFB' in ffn) > 0    THEN
            'AFB'
        WHEN position('CAMDIV' in ffn) > 0 THEN
            'FX'
        WHEN position('AC_I' in ffn) > 0   THEN
            'Indexes'
        WHEN position('AC_RF' in ffn) > 0  THEN
            'Fixed Income'
        WHEN position('AC_RV' in ffn) > 0  THEN
            'Variable Income'
        ELSE
            'Unknown'
    END AS priceinterfacename,
    --
    case when fn = 'AFS_' then 'AFS'
         when fn = 'CAMD' then 'FX'
         when fn = 'AC_I' then 'Indexes'
         when fn = 'AC_R' then 
            case when sfn = 'F' then 'Fixed Income'
                 else 'Variable Income'
            end
         else fn
    end as priceinterfacenameold,
    status_,
    creationdate_,
    nbrecords_,
    externalid_,
    ffn
FROM
    (
        SELECT
            flowid_,
            nbrecords_,
            externalid_,
            upper(substr(externalid_, 44, 4)) fn,
            upper(substr(externalid_, 48, 1)) sfn,
            status_,
            creationdate_,
            upper(substr(externalid_, position('/' in externalid) + 1)) ffn
        FROM
            broker_flowin
        WHERE
                inputdevice_deviceid_ = 'INSTRUMENTPRICE_IN'
            AND to_javadate(creationdate_) > :sql_last_value      --> no to_javadate in Oracle, AFAIK
    ) x
ORDER BY
    creationdate_ DESC