SQL Query to Oracle DB

I am converting an application to run off a Oracle 10g database from a DB2 database.  I am trying to get a SQL statement to run off the Oracle version and keep getting "SQL command not ended properly"   I am posting the SQL and hoping someone may point me in the right direction.  I have tried various little changes to the Order By and Group By but can not get it to work.  The SQL statement is below with a little synopsis.

SELECT nbr_comp, nbr_fund, nme, sort_date, SUM(Issued_Dollars) as Issued_Dollars, SUM(Issued_Shares) as Issued_Shares
 FROM ( SELECT h.nbr_comp, h.nbr_fund, c.nme, CASE WHEN h.cd_as_of_rsn = 'K' THEN h.dte_eff ELSE h.dte_proc END AS sort_date, SUM(CASE WHEN h.TXN_CODE = 'PUR' AND h.TXN_SUB NOT IN ('10', '13') AND h.IND_REV IN ('O', 'C') THEN h.amt_gross ELSE 0 End + CASE WHEN  h.TXN_CODE = 'WOP' AND h.IND_REV IN ('O', 'C') THEN h.amt_gross ELSE 0 End + CASE WHEN  h.TXN_CODE = 'ADJ' AND h.TXN_SUB = '01' AND h.IND_REV IN ('O', 'C') THEN h.amt_gross ELSE 0 End) AS Issued_Dollars, SUM(CASE WHEN h.TXN_CODE = 'PUR' AND h.TXN_SUB NOT IN ('10', '13') AND h.IND_REV IN ('O', 'C') THEN h.qty_ord ELSE 0 End + CASE WHEN  h.TXN_CODE = 'WOP' AND h.IND_REV IN ('O', 'C') THEN h.qty_ord ELSE 0 End + CASE WHEN  h.TXN_CODE = 'ADJ' AND h.TXN_SUB = '01' AND h.IND_REV IN ('O', 'C') THEN h.qty_ord ELSE 0 End) AS Issued_Shares
FROM  EP1003.mfthist_all h LEFT OUTER JOIN EP1003.mftfund c ON h.nbr_comp = c.nbr_comp AND h.nbr_fund = c.nbr_fund WHERE (((h.nbr_comp = 202 AND h.nbr_fund in (1,2,3,4,5))) AND ((h.cd_as_of_rsn <> 'K' and h.dte_proc between {ts '2011-11-03 00:00:00'} AND {ts '2011-12-26 00:00:00'}) OR (h.cd_as_of_rsn = 'K' AND h.dte_eff between {ts '2011-11-03 00:00:00'} AND {ts '2011-12-26 00:00:00'})) ) GROUP BY h.nbr_comp, h.nbr_fund, c.nme, (CASE WHEN h.cd_as_of_rsn = 'K' THEN h.dte_eff ELSE h.dte_proc END)   UNION ALL   SELECT h.nbr_comp, h.nbr_fund, c.nme, (CASE WHEN h.cd_as_of_rsn = 'K' THEN h.dte_eff ELSE h.dte_proc END) AS sort_date, 0 Issued_Dollars, 0 Issued_Shares
 FROM  EP1003.mfthist_all h LEFT OUTER JOIN  EP1003.mftfund c ON h.nbr_comp = c.nbr_comp AND h.nbr_fund = c.nbr_fund INNER JOIN  EP1003.mftrfee_all rf ON h.nbr_comp = rf.nbr_comp AND h.nbr_fund = rf.nbr_fund AND h.dte_proc = rf.dte_proc AND h.typ_acct = rf.typ_acct AND h.dte_estab_trac = rf.dte_estab_trac AND h.nbr_btch_trac = rf.nbr_btch_trac AND h.nbr_seq_trac = rf.nbr_seq_trac WHERE (((h.nbr_comp = 202 AND h.nbr_fund in (1,2,3,4,5))) AND ((h.cd_as_of_rsn <> 'K' and h.dte_proc between {ts '2011-11-03 00:00:00'} AND {ts '2011-12-26 00:00:00'}) OR (
h.cd_as_of_rsn = 'K' AND h.dte_eff between {ts '2011-11-03 00:00:00'} AND {ts '2011-12-26 00:00:00'})) ) AND rf.id_fee IN(48, 98) AND ((h.txn_code = 'EXO') OR (h.txn_code = 'LIQ') OR (h.txn_code = 'WOL')) GROUP BY h.nbr_comp, h.nbr_fund, c.nme, CASE WHEN h.cd_as_of_rsn = 'K' THEN h.dte_eff ELSE h.dte_proc END ORDER BY sort_date) AS  x GROUP BY nbr_comp, nbr_fund, nme, sort_date ORDER BY nbr_comp, nbr_fund, nme, sort_date


Synopsis
SELECT (  6 fields)  
FROM
      ( SELECT 6 fields
      FROM
      WHERE
      GROUP BY  - 4 fields
      UNION ALL
      SELECT 6 Fields
      FROM
      WHERE
      GROUP BY – 4 fields
      ORDER BY – 1 field)
AS X
GROUP BY  - 4 fields
ORDER BY – 4 fields

I believe there is an issue with my fields in the GROUP BY or ORDER BY clause but have not been able to pinpoint it.  Any help is appreciated. Thanks

Lance

lva6600311Asked:
Who is Participating?
 
ytarkanCommented:
remove the "AS" from "AS X" part :

try this :

SELECT nbr_comp,
       nbr_fund,
       nme,
       sort_date,
       SUM(Issued_Dollars) AS Issued_Dollars,
       SUM(Issued_Shares) AS Issued_Shares
FROM   (SELECT h.nbr_comp,
               h.nbr_fund,
               c.nme,
               CASE
                   WHEN h.cd_as_of_rsn = 'K' THEN
                    h.dte_eff
                   ELSE
                    h.dte_proc
               END AS sort_date,
               SUM(CASE
                       WHEN h.TXN_CODE = 'PUR' AND h.TXN_SUB NOT IN ('10', '13') AND
                            h.IND_REV IN ('O', 'C') THEN
                        h.amt_gross
                       ELSE
                        0
                   END + CASE
                       WHEN h.TXN_CODE = 'WOP' AND h.IND_REV IN ('O', 'C') THEN
                        h.amt_gross
                       ELSE
                        0
                   END + CASE
                       WHEN h.TXN_CODE = 'ADJ' AND h.TXN_SUB = '01' AND h.IND_REV IN ('O', 'C') THEN
                        h.amt_gross
                       ELSE
                        0
                   END) AS Issued_Dollars,
               SUM(CASE
                       WHEN h.TXN_CODE = 'PUR' AND h.TXN_SUB NOT IN ('10', '13') AND
                            h.IND_REV IN ('O', 'C') THEN
                        h.qty_ord
                       ELSE
                        0
                   END + CASE
                       WHEN h.TXN_CODE = 'WOP' AND h.IND_REV IN ('O', 'C') THEN
                        h.qty_ord
                       ELSE
                        0
                   END + CASE
                       WHEN h.TXN_CODE = 'ADJ' AND h.TXN_SUB = '01' AND h.IND_REV IN ('O', 'C') THEN
                        h.qty_ord
                       ELSE
                        0
                   END) AS Issued_Shares
        FROM   EP1003.mfthist_all h
        LEFT   OUTER JOIN EP1003.mftfund c ON h.nbr_comp = c.nbr_comp AND
                                              h.nbr_fund = c.nbr_fund
        WHERE  (((h.nbr_comp = 202 AND h.nbr_fund IN (1, 2, 3, 4, 5))) AND
               ((h.cd_as_of_rsn <> 'K' AND h.dte_proc BETWEEN {ts '2011-11-03 00:00:00' } AND {ts
                '2011-12-26 00:00:00' }) OR
               (h.cd_as_of_rsn = 'K' AND h.dte_eff BETWEEN {ts '2011-11-03 00:00:00' } AND {ts
                '2011-12-26 00:00:00' })))
        GROUP  BY h.nbr_comp,
                  h.nbr_fund,
                  c.nme,
                  (CASE
                      WHEN h.cd_as_of_rsn = 'K' THEN
                       h.dte_eff
                      ELSE
                       h.dte_proc
                  END)
        UNION ALL
        SELECT h.nbr_comp,
               h.nbr_fund,
               c.nme,
               (CASE
                   WHEN h.cd_as_of_rsn = 'K' THEN
                    h.dte_eff
                   ELSE
                    h.dte_proc
               END) AS sort_date,
               0 Issued_Dollars,
               0 Issued_Shares
        FROM   EP1003.mfthist_all h
        LEFT   OUTER JOIN EP1003.mftfund c ON h.nbr_comp = c.nbr_comp AND
                                              h.nbr_fund = c.nbr_fund
        INNER  JOIN EP1003.mftrfee_all rf ON h.nbr_comp = rf.nbr_comp AND
                                             h.nbr_fund = rf.nbr_fund AND
                                             h.dte_proc = rf.dte_proc AND
                                             h.typ_acct = rf.typ_acct AND
                                             h.dte_estab_trac = rf.dte_estab_trac AND
                                             h.nbr_btch_trac = rf.nbr_btch_trac AND
                                             h.nbr_seq_trac = rf.nbr_seq_trac
        WHERE  (((h.nbr_comp = 202 AND h.nbr_fund IN (1, 2, 3, 4, 5))) AND
               ((h.cd_as_of_rsn <> 'K' AND h.dte_proc BETWEEN {ts '2011-11-03 00:00:00' } AND {ts
                '2011-12-26 00:00:00' }) OR
               (h.cd_as_of_rsn = 'K' AND h.dte_eff BETWEEN {ts '2011-11-03 00:00:00' } AND {ts
                '2011-12-26 00:00:00' }))) AND
               rf.id_fee IN (48, 98) AND
               ((h.txn_code = 'EXO') OR (h.txn_code = 'LIQ') OR (h.txn_code = 'WOL'))
        GROUP  BY h.nbr_comp,
                  h.nbr_fund,
                  c.nme,
                  CASE
                      WHEN h.cd_as_of_rsn = 'K' THEN
                       h.dte_eff
                      ELSE
                       h.dte_proc
                  END
        ORDER  BY sort_date) X
GROUP  BY nbr_comp,
          nbr_fund,
          nme,
          sort_date
ORDER  BY nbr_comp,
          nbr_fund,
          nme,
          sort_date
0
 
Wasim Akram ShaikCommented:
Can you post the error message you have encountered while trying to execute this query,

also can you let me know why have you used the condition like this

between {ts '2011-11-03 00:00:00'} AND {ts '2011-12-26 00:00:00'}

i think here is the problem, if you want to use timestamps in a particular format then you have to convert them using to_char function.
0
 
SharathData EngineerCommented:
Remove the ORDER BY clause in the sub-query
SELECT nbr_comp, nbr_fund, nme, sort_date, SUM(Issued_Dollars) as Issued_Dollars, SUM(Issued_Shares) as Issued_Shares
 FROM ( SELECT h.nbr_comp, h.nbr_fund, c.nme, CASE WHEN h.cd_as_of_rsn = 'K' THEN h.dte_eff ELSE h.dte_proc END AS sort_date, SUM(CASE WHEN h.TXN_CODE = 'PUR' AND h.TXN_SUB NOT IN ('10', '13') AND h.IND_REV IN ('O', 'C') THEN h.amt_gross ELSE 0 End + CASE WHEN  h.TXN_CODE = 'WOP' AND h.IND_REV IN ('O', 'C') THEN h.amt_gross ELSE 0 End + CASE WHEN  h.TXN_CODE = 'ADJ' AND h.TXN_SUB = '01' AND h.IND_REV IN ('O', 'C') THEN h.amt_gross ELSE 0 End) AS Issued_Dollars, SUM(CASE WHEN h.TXN_CODE = 'PUR' AND h.TXN_SUB NOT IN ('10', '13') AND h.IND_REV IN ('O', 'C') THEN h.qty_ord ELSE 0 End + CASE WHEN  h.TXN_CODE = 'WOP' AND h.IND_REV IN ('O', 'C') THEN h.qty_ord ELSE 0 End + CASE WHEN  h.TXN_CODE = 'ADJ' AND h.TXN_SUB = '01' AND h.IND_REV IN ('O', 'C') THEN h.qty_ord ELSE 0 End) AS Issued_Shares
FROM  EP1003.mfthist_all h LEFT OUTER JOIN EP1003.mftfund c ON h.nbr_comp = c.nbr_comp AND h.nbr_fund = c.nbr_fund WHERE (((h.nbr_comp = 202 AND h.nbr_fund in (1,2,3,4,5))) AND ((h.cd_as_of_rsn <> 'K' and h.dte_proc between {ts '2011-11-03 00:00:00'} AND {ts '2011-12-26 00:00:00'}) OR (h.cd_as_of_rsn = 'K' AND h.dte_eff between {ts '2011-11-03 00:00:00'} AND {ts '2011-12-26 00:00:00'})) ) GROUP BY h.nbr_comp, h.nbr_fund, c.nme, (CASE WHEN h.cd_as_of_rsn = 'K' THEN h.dte_eff ELSE h.dte_proc END)   UNION ALL   SELECT h.nbr_comp, h.nbr_fund, c.nme, (CASE WHEN h.cd_as_of_rsn = 'K' THEN h.dte_eff ELSE h.dte_proc END) AS sort_date, 0 Issued_Dollars, 0 Issued_Shares
 FROM  EP1003.mfthist_all h LEFT OUTER JOIN  EP1003.mftfund c ON h.nbr_comp = c.nbr_comp AND h.nbr_fund = c.nbr_fund INNER JOIN  EP1003.mftrfee_all rf ON h.nbr_comp = rf.nbr_comp AND h.nbr_fund = rf.nbr_fund AND h.dte_proc = rf.dte_proc AND h.typ_acct = rf.typ_acct AND h.dte_estab_trac = rf.dte_estab_trac AND h.nbr_btch_trac = rf.nbr_btch_trac AND h.nbr_seq_trac = rf.nbr_seq_trac WHERE (((h.nbr_comp = 202 AND h.nbr_fund in (1,2,3,4,5))) AND ((h.cd_as_of_rsn <> 'K' and h.dte_proc between {ts '2011-11-03 00:00:00'} AND {ts '2011-12-26 00:00:00'}) OR (
h.cd_as_of_rsn = 'K' AND h.dte_eff between {ts '2011-11-03 00:00:00'} AND {ts '2011-12-26 00:00:00'})) ) AND rf.id_fee IN(48, 98) AND ((h.txn_code = 'EXO') OR (h.txn_code = 'LIQ') OR (h.txn_code = 'WOL')) GROUP BY h.nbr_comp, h.nbr_fund, c.nme, CASE WHEN h.cd_as_of_rsn = 'K' THEN h.dte_eff ELSE h.dte_proc END) AS  x GROUP BY nbr_comp, nbr_fund, nme, sort_date ORDER BY nbr_comp, nbr_fund, nme, sort_date

Open in new window

0
 
lva6600311Author Commented:
Thank You YTARKAN!!!!!!!   In my research I did for this I had read where Oracle does not take the "AS" phrase for table aliases which I thought I had tried but I guess I hadn't or had changed other things that may have affected it.  I am still ramping up my SQL Querying skills so much appreciated. Thank You

Lance
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.