Home » SQL & PL/SQL » SQL & PL/SQL » What may be problem? ( 8.1.7.0.0)
What may be problem? [message #381077] Thu, 15 January 2009 03:07 Go to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Full outer join is used in following query. Two subquries are used in FROM Clause. But when query is executed message is
"SQL Command Not Properly Ended"

SELECT *
  FROM (SELECT   ROUND (SUM (str_rol)) str_rol, unt_cod,
                 DECODE (unt_cod,
                         17, dpt_cod,
                         DECODE (dpt_cod, 8, 7, dpt_cod)
                        ) dpt_cod,
                 dsg_cod, 4 sft_cod,
                 DECODE (cat_cod, 1, 'PM', 2, 'DW') cat_des
            FROM sal.apr_str_01
           WHERE TRUNC (:p_too_dte) BETWEEN str_frm_dte
                                        AND DECODE (str_too_dte,
                                                    NULL, TRUNC (SYSDATE),
                                                    str_too_dte
                                                   )
             AND cat_cod = 2
        GROUP BY unt_cod,
                 DECODE (unt_cod,
                         17, dpt_cod,
                         DECODE (dpt_cod, 8, 7, dpt_cod)
                        ),
                 dsg_cod,
                 DECODE (cat_cod, 1, 'PM', 2, 'DW')) apr_str
       FULL OUTER JOIN
       (SELECT   COUNT (*) act_emp, emp_fil.unt_cod,
                 DECODE (unt_cod,
                         17, emp_fil.dpt_cod,
                         DECODE (emp_fil.dpt_cod, 8, 7, emp_fil.dpt_cod)
                        ) dpt_cod,
                 emp_fil.dsg_cod
            FROM sal.emp_fil_veu emp_fil
           WHERE TRUNC (:p_too_dte) BETWEEN unt_frm_dte AND unt_too_dte
             AND TRUNC (:p_too_dte) BETWEEN sft_frm_dte AND sft_too_dte
             AND TRUNC (:p_too_dte) BETWEEN dsg_frm_dte AND dsg_too_dte
             AND TRUNC (:p_too_dte) BETWEEN rst_frm_dte AND rst_too_dte
             AND rsg_dte IS NULL
             AND cat_cod = 2
             AND (emp_fil.emp_cod, emp_fil.bas_cod) NOT IN (
                                   SELECT emp_cod, bas_cod
                                     FROM sal.not_ent_01
                                    WHERE rin_dte IS NULL
                                          AND ap_id IS NOT NULL)
             AND (emp_fil.emp_cod, emp_fil.bas_cod) NOT IN (
                                   SELECT emp_cod, bas_cod
                                     FROM sal.not_ent_01
                                    WHERE rin_dte IS NOT NULL
                                          AND ap_id IS NULL)
        GROUP BY emp_fil.unt_cod,
                 DECODE (unt_cod,
                         17, emp_fil.dpt_cod,
                         DECODE (emp_fil.dpt_cod, 8, 7, emp_fil.dpt_cod)
                        ),
                 emp_fil.dsg_cod) act_str
       ON (    apr_str.unt_cod = act_str.unt_cod
           AND apr_str.dpt_cod = act_str.dpt_cod
           AND apr_str.dsg_cod = act_str.dsg_cod
          )


Thanks In Advance.
Re: What may be problem? [message #381080 is a reply to message #381077] Thu, 15 January 2009 03:14 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Oracle 8.1.7 did not support ANSI joins.

MHE
Re: What may be problem? [message #381084 is a reply to message #381077] Thu, 15 January 2009 03:29 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Thanks for reply.

What may be alternative without UNION???


Best Regards.
Asif.
Re: What may be problem? [message #381085 is a reply to message #381084] Thu, 15 January 2009 03:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
None.

Regards
Michel
Re: What may be problem? [message #381086 is a reply to message #381077] Thu, 15 January 2009 03:32 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear Michel.

It means that final solution is UNION Clause???

Best Regards.
Asif.
Re: What may be problem? [message #381087 is a reply to message #381086] Thu, 15 January 2009 03:36 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The final solution is to upgrade to a supported version.
Otherwise you have no other solution than using UNION ALL.

Regards
Michel
Previous Topic: Trigger lost when View Re-Created
Next Topic: Select last record in a group depending on a condition
Goto Forum:
  


Current Time: Wed Dec 07 10:46:33 CST 2016

Total time taken to generate the page: 0.10747 seconds