将查询从sybase iq转换为impala

5t7ly7z5 于 2021-06-26 发布在 Impala

关注(0)|答案(1)|浏览(339)

SELECT f.exch 
FROM   ( 
              SELECT 
                     CASE 
                            WHEN sourcedesk IN ('GOBUS_NY', 
                                                'GOBUS_UK', 
                                                'PETRDAES', 
                                                'PEETRAD') THEN clientid 
                            WHEN sourcedesk = 'AESLDN' 
                            AND    clientid != '' THEN clientid 
                            ELSE owner 
                     END                               AS aclient, 
                     clientid                          AS client, 
                     orderid                           AS gaotag, 
                     exchange                          AS exch, 
                     side                              AS side, 
                     securityid                        AS sym, 
                     cumulativequantity*executionprice AS value, 
                     cumulativequantity                AS qdone, 
                     fillid                            AS gaftag, 
                     executionexchangetimestamplcl     AS xstamp, 
                     executionprice                    AS lprice, 
                     executionexchangeid               AS xftag, 
                     serverid                          AS srvid, 
                     reasoncode                        AS rescode, 
                     rootfillid                        AS rgaftag, 
                     miccode                           AS miccode, 
                     mifidtradeflags                   AS mifidtradeflags, 
                     sourcedesk                        AS desk 
              FROM   fillsastextdatestring 
              WHERE  agoralocaltradedate = '190205' 
              AND    exchange = 'VEX' 
              AND    sourceservicename LIKE 'vexprod%' 
              AND    securityid LIKE '%.%' 
              UNION ALL 
              SELECT 
                     CASE 
                            WHEN sourcedesk IN ('GOBUS_NY', 
                                                'GOBUS_UK', 
                                                'PETRDAES', 
                                                'PEETRAD', 
                                                'VEXSVC', 
                                                'LLSOR') THEN clientid 
                            WHEN sourcedesk = 'AESLDN' 
                            AND    clientid != '' THEN clientid 
                            ELSE owner 
                     END                               AS aclient, 
                     clientid                          AS client, 
                     orderid                           AS gaotag, 
                     exchange                          AS exch, 
                     side                              AS side, 
                     securityid                        AS sym, 
                     cumulativequantity*executionprice AS value, 
                     cumulativequantity                AS qdone, 
                     fillid                            AS gaftag, 
                     executionexchangetimestamplcl     AS xstamp, 
                     executionprice                    AS lprice, 
                     executionexchangeid               AS xftag, 
                     serverid                          AS srvid, 
                     reasoncode                        AS rescode, 
                     rootfillid                        AS rgaftag, 
                     miccode                           AS miccode, 
                     mifidtradeflags                   AS mifidtradeflags, 
                     sourcedesk                        AS desk 
              FROM   fillsastextdatestring 
              WHERE  agoralocaltradedate = '190205' 
              AND    sourceservicename IN ('SORUK', 
                                           'PRODSorBkFlr', 
                                           'PRODFixBkFlr', 
                                           'PRODFixBkFlr1', 
                                           'PRODFixBkFlr2', 
                                           'PRODSmgBkFlr', 
                                           'ECGaia2Agora') 
              AND    owner NOT LIKE 'tree%' 
              AND    securityid LIKE '%.%' 
              UNION ALL 
              SELECT 
                     CASE 
                            WHEN sourcedesk IN ('GOBUS_NY', 
                                                'GOBUS_UK', 
                                                'PETRDAES', 
                                                'PEETRAD', 
                                                'VEXSVC', 
                                                'LLSOR') THEN clientid 
                            WHEN sourcedesk = 'AESLDN' 
                            AND    clientid != '' THEN clientid 
                            ELSE owner 
                     END                               AS aclient, 
                     clientid                          AS client, 
                     orderid                           AS gaotag, 
                     exchange                          AS exch, 
                     side                              AS side, 
                     securityid                        AS sym, 
                     cumulativequantity*executionprice AS value, 
                     cumulativequantity                AS qdone, 
                     fillid                            AS gaftag, 
                     executionexchangetimestamplcl     AS xstamp, 
                     executionprice                    AS lprice, 
                     executionexchangeid               AS xftag, 
                     serverid                          AS srvid, 
                     reasoncode                        AS rescode, 
                     rootfillid                        AS rgaftag, 
                     miccode                           AS miccode, 
                     mifidtradeflags                   AS mifidtradeflags, 
                     sourcedesk                        AS desk 
              FROM   fillsastextdatestring 
              WHERE  agoralocaltradedate = '190205' 
              AND    securityid LIKE '%.%' 
              AND    exchange = 'BENCHMKX' 
              AND    sourceservicename LIKE 'BENCHM_%' 
              UNION ALL 
              SELECT 
                     CASE 
                            WHEN sourcedesk IN ('GOBUS_NY', 
                                                'GOBUS_UK', 
                                                'PETRDAES', 
                                                'PEETRAD', 
                                                'VEXSVC', 
                                                'LLSOR') THEN clientid 
                            WHEN sourcedesk = 'AESLDN' 
                            AND    clientid != '' THEN clientid 
                            ELSE owner 
                     END                               AS aclient, 
                     clientid                          AS client, 
                     orderid                           AS gaotag, 
                     exchange                          AS exch, 
                     side                              AS side, 
                     securityid                        AS sym, 
                     cumulativequantity*executionprice AS value, 
                     cumulativequantity                AS qdone, 
                     fillid                            AS gaftag, 
                     executionexchangetimestamplcl     AS xstamp, 
                     executionprice                    AS lprice, 
                     executionexchangeid               AS xftag, 
                     serverid                          AS srvid, 
                     reasoncode                        AS rescode, 
                     rootfillid                        AS rgaftag, 
                     miccode                           AS miccode, 
                     mifidtradeflags                   AS mifidtradeflags, 
                     sourcedesk                        AS desk 
              FROM   fillsastextdatestring 
              WHERE  agoralocaltradedate = '190205' 
              AND    sourceservicename = 'ECGaia2Agora' 
              AND    owner NOT LIKE 'tree%' 
              AND    exchange IN ('BATS', 
                                  'CHIX') 
              AND    reasoncode = 'NT' 
              AND    side = 'B' 
              UNION ALL 
              SELECT 
                     CASE 
                            WHEN sourcedesk IN ('GOBUS_NY', 
                                                'GOBUS_UK', 
                                                'PETRDAES', 
                                                'PEETRAD', 
                                                'VEXSVC', 
                                                'LLSOR') THEN clientid 
                            WHEN sourcedesk = 'AESLDN' 
                            AND    clientid != '' THEN clientid 
                            ELSE owner 
                     END                               AS aclient, 
                     clientid                          AS client, 
                     orderid                           AS gaotag, 
                     exchange                          AS exch, 
                     side                              AS side, 
                     securityid                        AS sym, 
                     cumulativequantity*executionprice AS value, 
                     cumulativequantity                AS qdone, 
                     fillid                            AS gaftag, 
                     executionexchangetimestamplcl     AS xstamp, 
                     executionprice                    AS lprice, 
                     executionexchangeid               AS xftag, 
                     serverid                          AS srvid, 
                     reasoncode                        AS rescode, 
                     rootfillid                        AS rgaftag, 
                     miccode                           AS miccode, 
                     mifidtradeflags                   AS mifidtradeflags, 
                     sourcedesk                        AS desk 
              FROM   fillsastextdatestring 
              WHERE  agoralocaltradedate = '190205' 
              AND    sourceservicename LIKE 'tars_%' ) AS f, 
       ( 
              SELECT orderid             AS gaotag, 
                     serverid            AS srvid, 
                     securityidasentered AS enteredsym, 
                     securityid          AS sym, 
                     rootorderid         AS rgaotag, 
                     timeinforce         AS tif, 
                     settletype          AS settletype, 
                     sourceservicename   AS appl, 
                     account             AS acct 
              FROM   ordersastextdatestring 
              WHERE  agoralocaltradedate = '190205' 
              AND    destinationdesk IN ('BENCHX', 
                                         'VEXSVC', 
                                         'SORMKT', 
                                         'MAIA', 
                                         'LLSOR', 
                                         'TARSSVC')) AS o, 
where  f.gaotag = o.gaotag

上述查询失败，在impala中出现以下错误：
analysisexception:第4行出现语法错误：未定义：其中f.gaotag=o.gaotag^遇到：预期位置：默认值，标识符原因：exception:语法错误
我浏览了以下cloudera链接：https://www.cloudera.com/documentation/enterprise/5-4-x/topics/impala_subqueries.html 但却没能成功。
我对sybase iq和imapala都是新手。

sql impala sap-iq

来源：https://stackoverflow.com/questions/54735490/converting-a-query-from-sybase-iq-to-impala

1条答案

按热度按时间

w8rqjzmb1#

您能尝试用下面的查询替换最后一个查询并运行它吗-

SELECT f.exch 
FROM   (SELECT CASE 
                 WHEN sourcedesk IN ( 'GOBUS_NY', 'GOBUS_UK', 'PETRDAES', 
                                      'PEETRAD' ) 
                       THEN 
                 clientid 
                 WHEN sourcedesk = 'AESLDN' 
                      AND clientid != '' THEN clientid 
                 ELSE owner 
               END                                 AS aclient, 
               clientid                            AS client, 
               orderid                             AS gaotag, 
               exchange                            AS exch, 
               side                                AS side, 
               securityid                          AS sym, 
               cumulativequantity * executionprice AS value, 
               cumulativequantity                  AS qdone, 
               fillid                              AS gaftag, 
               executionexchangetimestamplcl       AS xstamp, 
               executionprice                      AS lprice, 
               executionexchangeid                 AS xftag, 
               serverid                            AS srvid, 
               reasoncode                          AS rescode, 
               rootfillid                          AS rgaftag, 
               miccode                             AS miccode, 
               mifidtradeflags                     AS mifidtradeflags, 
               sourcedesk                          AS desk 
        FROM   fillsastextdatestring 
        WHERE  agoralocaltradedate = '190205' 
               AND exchange = 'VEX' 
               AND sourceservicename LIKE 'vexprod%' 
               AND securityid LIKE '%.%' 
        UNION ALL 
        SELECT CASE 
                 WHEN sourcedesk IN ( 'GOBUS_NY', 'GOBUS_UK', 'PETRDAES', 
                                      'PEETRAD', 
                                      'VEXSVC', 'LLSOR' ) THEN clientid 
                 WHEN sourcedesk = 'AESLDN' 
                      AND clientid != '' THEN clientid 
                 ELSE owner 
               END                                 AS aclient, 
               clientid                            AS client, 
               orderid                             AS gaotag, 
               exchange                            AS exch, 
               side                                AS side, 
               securityid                          AS sym, 
               cumulativequantity * executionprice AS value, 
               cumulativequantity                  AS qdone, 
               fillid                              AS gaftag, 
               executionexchangetimestamplcl       AS xstamp, 
               executionprice                      AS lprice, 
               executionexchangeid                 AS xftag, 
               serverid                            AS srvid, 
               reasoncode                          AS rescode, 
               rootfillid                          AS rgaftag, 
               miccode                             AS miccode, 
               mifidtradeflags                     AS mifidtradeflags, 
               sourcedesk                          AS desk 
        FROM   fillsastextdatestring 
        WHERE  agoralocaltradedate = '190205' 
               AND sourceservicename IN ( 'SORUK', 'PRODSorBkFlr', 
                                          'PRODFixBkFlr', 
                                          'PRODFixBkFlr1', 
                                          'PRODFixBkFlr2', 'PRODSmgBkFlr', 
                                          'ECGaia2Agora' ) 
               AND owner NOT LIKE 'tree%' 
               AND securityid LIKE '%.%' 
        UNION ALL 
        SELECT CASE 
                 WHEN sourcedesk IN ( 'GOBUS_NY', 'GOBUS_UK', 'PETRDAES', 
                                      'PEETRAD', 
                                      'VEXSVC', 'LLSOR' ) THEN clientid 
                 WHEN sourcedesk = 'AESLDN' 
                      AND clientid != '' THEN clientid 
                 ELSE owner 
               END                                 AS aclient, 
               clientid                            AS client, 
               orderid                             AS gaotag, 
               exchange                            AS exch, 
               side                                AS side, 
               securityid                          AS sym, 
               cumulativequantity * executionprice AS value, 
               cumulativequantity                  AS qdone, 
               fillid                              AS gaftag, 
               executionexchangetimestamplcl       AS xstamp, 
               executionprice                      AS lprice, 
               executionexchangeid                 AS xftag, 
               serverid                            AS srvid, 
               reasoncode                          AS rescode, 
               rootfillid                          AS rgaftag, 
               miccode                             AS miccode, 
               mifidtradeflags                     AS mifidtradeflags, 
               sourcedesk                          AS desk 
        FROM   fillsastextdatestring 
        WHERE  agoralocaltradedate = '190205' 
               AND securityid LIKE '%.%' 
               AND exchange = 'BENCHMKX' 
               AND sourceservicename LIKE 'BENCHM_%' 
        UNION ALL 
        SELECT CASE 
                 WHEN sourcedesk IN ( 'GOBUS_NY', 'GOBUS_UK', 'PETRDAES', 
                                      'PEETRAD', 
                                      'VEXSVC', 'LLSOR' ) THEN clientid 
                 WHEN sourcedesk = 'AESLDN' 
                      AND clientid != '' THEN clientid 
                 ELSE owner 
               END                                 AS aclient, 
               clientid                            AS client, 
               orderid                             AS gaotag, 
               exchange                            AS exch, 
               side                                AS side, 
               securityid                          AS sym, 
               cumulativequantity * executionprice AS value, 
               cumulativequantity                  AS qdone, 
               fillid                              AS gaftag, 
               executionexchangetimestamplcl       AS xstamp, 
               executionprice                      AS lprice, 
               executionexchangeid                 AS xftag, 
               serverid                            AS srvid, 
               reasoncode                          AS rescode, 
               rootfillid                          AS rgaftag, 
               miccode                             AS miccode, 
               mifidtradeflags                     AS mifidtradeflags, 
               sourcedesk                          AS desk 
        FROM   fillsastextdatestring 
        WHERE  agoralocaltradedate = '190205' 
               AND sourceservicename = 'ECGaia2Agora' 
               AND owner NOT LIKE 'tree%' 
               AND exchange IN ( 'BATS', 'CHIX' ) 
               AND reasoncode = 'NT' 
               AND side = 'B' 
        UNION ALL 
        SELECT CASE 
                 WHEN sourcedesk IN ( 'GOBUS_NY', 'GOBUS_UK', 'PETRDAES', 
                                      'PEETRAD', 
                                      'VEXSVC', 'LLSOR' ) THEN clientid 
                 WHEN sourcedesk = 'AESLDN' 
                      AND clientid != '' THEN clientid 
                 ELSE owner 
               END                                 AS aclient, 
               clientid                            AS client, 
               orderid                             AS gaotag, 
               exchange                            AS exch, 
               side                                AS side, 
               securityid                          AS sym, 
               cumulativequantity * executionprice AS value, 
               cumulativequantity                  AS qdone, 
               fillid                              AS gaftag, 
               executionexchangetimestamplcl       AS xstamp, 
               executionprice                      AS lprice, 
               executionexchangeid                 AS xftag, 
               serverid                            AS srvid, 
               reasoncode                          AS rescode, 
               rootfillid                          AS rgaftag, 
               miccode                             AS miccode, 
               mifidtradeflags                     AS mifidtradeflags, 
               sourcedesk                          AS desk 
        FROM   fillsastextdatestring 
        WHERE  agoralocaltradedate = '190205' 
               AND sourceservicename LIKE 'tars_%') AS f 
       INNER JOIN (SELECT orderid             AS gaotag, 
                          serverid            AS srvid, 
                          securityidasentered AS enteredsym, 
                          securityid          AS sym, 
                          rootorderid         AS rgaotag, 
                          timeinforce         AS tif, 
                          settletype          AS settletype, 
                          sourceservicename   AS appl, 
                          account             AS acct 
                   FROM   ordersastextdatestring 
                   WHERE  agoralocaltradedate = '190205' 
                          AND destinationdesk IN ( 'BENCHX', 'VEXSVC', 'SORMKT', 
                                                   'MAIA' 
                                                   , 
                                                   'LLSOR', 'TARSSVC' )) o 
               ON f.gaotag = o.gaotag;

赞(0）回复(0）举报 2021-06-26

我来回答

将查询从sybase iq转换为impala

1条答案

相关问题

热门标签

最新问答