This query error: missing rigth parenthesis

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Tue, 01 Jul 2008 10:03 am

SELECT DISTINCT DPTO.NU_DPTO, DPTO.DESC_DPTO FROM GLB_DPTO DEPARTMENT, (SELECT NU_DPTO, SUM (QTDE_ESTOQUE_PA + QTDE_ESTOQUE_PA_OUT) PARTS FROM TB_CTRL_ESTOQUE_PA WHERE CDG_SECAO = 4 AND CDG_COR = 001 & CDG_TAM = ' 7 ' AND MES_ESTOQUE_PA = 8 AND (QTDE_ESTOQUE_PA + QTDE_ESTOQUE_PA_OUT) > 0 GROUP BY NU_DPTO ORDER BY DESC PARTS) WHERE PA PA.NU_DPTO = DPTO.NU_DPTO where is the error? I can't understand. The subquery, individually works as expected. Where I am wrong?
pc_delphi
Location: São Paulo

Tudo posso, nAquel que me fortalece
Filipenses 4:13

Poston Tue, 01 Jul 2008 2:46 pm

See if that helps you!

Code: Select all
SELECT DISTINCT DPTO.NU_DPTO,DPTO.DESC_DPTO,pra.pecas
FROM GLB_DPTO DPTO
join (SELECT NU_DPTO,SUM(QTDE_ESTOQUE_PA +    QTDE_ESTOQUE_PA_OUT) AS PECAS
       FROM TB_CTRL_ESTOQUE_PA
       WHERE CDG_SECAO = 4
       AND CDG_COR = 001
       AND CDG_TAM = '7'
       AND MES_ESTOQUE_PA = 8
       AND (QTDE_ESTOQUE_PA + QTDE_ESTOQUE_PA_OUT) > 0
       GROUP BY NU_DPTO
       ORDER BY PECAS DESC) pra
ON pra.NU_DPTO = DPTO.NU_DPTO
vetis
Location: cariacica - es

Thiago Vetis
Creedence Clearwater Revival - :-o

Poston Tue, 01 Jul 2008 3:58 pm

company that I Join, gives this error:
Sql command not properly ended
told me that the Oracle 8i behind does not accept the Join and its variants. When I took the Order By the SubQuery and put at the end of it all, it worked.
pc_delphi
Location: São Paulo

Tudo posso, nAquel que me fortalece
Filipenses 4:13

Poston Wed, 02 Jul 2008 8:16 am

You're right in 8i and earlier the join does not work, use the union to emulate a full-outer-join. att],
vetis
Location: cariacica - es

Thiago Vetis
Creedence Clearwater Revival - :-o

Poston Wed, 02 Jul 2008 9:40 am

Guys, just for knowledge.

The only situation in which the ORDER BY is allowed in a subquery is exactly when the subquery is made in the FROM clause, which is called INLINE viewing.
In other subqueries using ORDER BY results in an error.

From qualquer form, it's quite strange to have this error given queries and have worked after the withdrawal of the ORDER BY. :?


T +
joaogarcia
Location: Campinas - SP

Cordialmente,
João C. Garcia

Poston Wed, 02 Jul 2008 1:07 pm

Anyway, it's quite strange to have this error given queries and have worked after the withdrawal of the ORDER BY.


In fact, the 8i version (tested on version 8.1.7) does not accept the order by clause in a subquery, via PL/SQL (the same occurs with forms 6i), due to the version of the PL/SQL parser used in these tools.

However, as the topic was posted in SQL subforum, I am assuming that the query is being held purely via SQL-in this case, really doesn't make sense this error being displayed.
rogenaro
Location: Londrina - PR

Rafael O. Genaro

Poston Wed, 02 Jul 2008 2:30 pm

Rafael, I honestly don't know what can is giving error, because I checked your sql, did test using a table (oracle 9i) mine and showed no problem.
You're not really making any sense give error in sql!

Ps: give a tip on that server with the 8i, to see if the bits go back to work. :D att,
vetis
Location: cariacica - es

Thiago Vetis
Creedence Clearwater Revival - :-o


  • See also
    Replies
    Views
    Last Post


Return to SQL

Who is online

Users browsing this forum: No registered users and 8 guests