How to execute subquery in Oracle? My query gets parentheses erro

0

I'm trying to execute a query inside LabVIEW so I can informations stored in a Oracle Database, but when a try to execute a query with parenthesis it doesn't works and gives me this erro:

ADO Error: 0x80004005 Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [Oracle][ODBC][Ora]ORA-00907: parêntese direito não encontrado

Here is the SQL query I'm trying to execute:

SELECT
F.CODIGOFAIXAMODELO,
F.CODIGOMODELO,
F.INICIOESCALA,
F.FUNDOESCALA,
F.FAIXA,
F.DESCFAIXA,
F.ORDEM,
P.CODIGOPROCEDIMENTO
FROM FAIXAS F INNER JOIN PROCEDS P ON F.CODIGOFAIXAMODELO=(
    SELECT
    CODIGOFAIXAMODELO
    FROM PROCEDS
    WHERE
    PROCEDS.CODIGOFAIXAMODELO=F.CODIGOFAIXAMODELO
    LIMIT 1
    )
WHERE
F.CODIGOMODELO='%CODIGOMODELO%'
ORDER BY F.ORDEM ASC;

The %CODIGOMODELO% is replaced with a value by LabVIEW.

When I try the following Query it works:

SELECT
F.CODIGOFAIXAMODELO,
F.CODIGOMODELO,
F.INICIOESCALA,
F.FUNDOESCALA,
F.FAIXA,
F.DESCFAIXA,
F.ORDEM,
P.CODIGOPROCEDIMENTO
FROM FAIXAS F INNER JOIN PROCEDS P ON F.CODIGOFAIXAMODELO=P.CODIGOFAIXAMODELO
WHERE
F.CODIGOMODELO='%CODIGOMODELO%'
ORDER BY F.ORDEM ASC;

The problem with the second solution is that it returns me many P.CODIGOPROCEDIMENTO, and what I want is to get only one even when there are many.

sql
oracle
asked on Stack Overflow Feb 7, 2019 by Lukas Belck • edited Feb 8, 2019 by APC

3 Answers

1

there is no LIMIT function in Oracle

you need to use ROWNUM = 1 or OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY

Also as it is stated by @APC, you shouldn't be joining your table on a subquery.

answered on Stack Overflow Feb 7, 2019 by Derviş Kayımbaşıoğlu • edited Feb 8, 2019 by Derviş Kayımbaşıoğlu
0

I would write it this way. It may be more efficient and more readable to avoid trying to evaluate a subquery inside an expression:

SELECT
F.CODIGOFAIXAMODELO,
F.CODIGOMODELO,
F.INICIOESCALA,
F.FUNDOESCALA,
F.FAIXA,
F.DESCFAIXA,
F.ORDEM,
P.CODIGOPROCEDIMENTO
FROM FAIXAS F
INNER JOIN (
                SELECT
                P1.CODIGOFAIXAMODELO,
                MAX(P1.CODIGOPROCEDIMENTO) AS CODIGOPROCEDIMENTO
                FROM PROCEDS P1
                GROUP BY P1.CODIGOFAIXAMODELO
           ) P ON P.CODIGOFAIXAMODELO = F.CODIGOFAIXAMODELO
WHERE F.CODIGOMODELO = '%CODIGOMODELO%'
ORDER BY F.ORDEM ASC;

MAX() is an Aggregate Function that will return only one value for each group - specified in the GROUP BY clause. Therefore, using a subquery and joining on CODIGOFAIXAMODELO ensures that only one row is filtered against the main query. The results really depend on the key structures, datatypes and how many rows are available in PROCEDS. There are of course other, more complex methods to achieve the same result, such as using Analytic Functions.

answered on Stack Overflow Feb 8, 2019 by bruceskyaus • edited Feb 9, 2019 by bruceskyaus
0

I think you can write it this way:

SELECT
F.CODIGOFAIXAMODELO,
F.CODIGOMODELO,
F.INICIOESCALA,
F.FUNDOESCALA,
F.FAIXA,
F.DESCFAIXA,
F.ORDEM,
P.CODIGOPROCEDIMENTO
FROM FAIXAS F INNER JOIN PROCEDS P ON F.CODIGOFAIXAMODELO=P.CODIGOFAIXAMODELO and ROWNUM = 1
WHERE
F.CODIGOMODELO='%CODIGOMODELO%'
ORDER BY F.ORDEM ASC;
answered on Stack Overflow Feb 8, 2019 by Nick

User contributions licensed under CC BY-SA 3.0