Error in SQL Query

User_Q6SP8

Hello,

I am not a SQL-Developer but have to create some SQL Queries as part of my job. I want to create a Procedure but something is going wrong. Can you please help me identify the error. Thank you very much.

CREATE OR REPLACE PROCEDURE CONTRACT_PARTNERS 

(

 USER_INPUT IN VARCHAR2 

, RELATION IN VARCHAR2 

, OUTPUT OUT VARCHAR2 

) AS 

v_filter nvarchar2(30);

v_Value NUMBER(10);

BEGIN

v_Value :=

  CASE WHEN p_relation= 'eingehend' THEN 'D'

    WHEN p_relation = 'ausgehend' THEN 'K'

  END

SELECT into OUTPUT

no, name, street,' ' as ADDITION, zipcode,city,' ' as State,country_code, 'Vertragspartner' as CONTLIST

FROM sol_vendor WHERE (company_code like p_userinput OR lower(no) like lower(p_userinput) OR lower(name) like lower(p_userinput)) AND no LIKE 'p_userinput%';

END CONTRACT_PARTNERS;

Frank Kulash

Hi,

What, exactly, is the error? Post the complete error message(s), including line numbers.

Paulzip

We don't know your data model or what you are trying to do, so "something's wrong" is beyond vague.

What do you want to return in your output?

Billy Verreynne

Your approach to using SQL via PL/SQL is flawed. Oracle is nothing like SQL-Server in this regard, and approaches and techniques used in T-SQL simply do not work in SQL and PL/SQL.

What exact business requirement are you trying to meet?

Frank Kulash

Hi,

SELECT into OUTPUT

no, name, street,' ' as ADDITION, zipcode,city,' ' as State,country_code, 'Vertragspartner' as CONTLIST

FROM sol_vendor WHERE (company_code like p_userinput OR lower(no) like lower(p_userinput) OR lower(name) like lower(p_userinput)) AND no LIKE 'p_userinput%';

Review the syntax of SELECT ... INTO. The INTO clause comes after the SELECT clause, right before FROM. If you;re SELECTing into one VARCHAR2 variable (and nothing else), then the SELECT clause must contain one string column (and nothing else), e.g.

SELECT  no || ' ' || name || ' ' || street || ...
INTO    output
FROM    ...

User_Q6SP8

Sorry, I am also new to this Community. I need some time to learn to ask questions properly.

I have got a table sol_vendor where we have contract partners. Based on the relation to the partner the Company name has a prefix "D" or "K". We are using a software called ELO to create contracts. When creating a new contract the employees choose the option for contract relation ("ingoing" or "outgoing"). Based on the selection I want to filter the listed contract partners. If "ingoing" is selected, I want to list all companies with the prefix "D" like, "D - Apple".

To get data from database ELO is using an interface to create SQL queries and to pass variables to the query.

Currently working SQL Query is:

SELECT no, name, street,' ' as ADDITION, zipcode,city,' ' as State,country_code, 'Vertragspartner' as CONTLIST

FROM sol_vendor WHERE company_code like ? OR lower(no) like lower(?) OR lower(name) like lower(?)

? is the user-input

To filter the query Output I could just modify the SQL Query or try something new. The ELO Team told me that i can create procedere.

Solomon Yakobson

For starters

CREATE OR REPLACE PROCEDURE CONTRACT_PARTNERS 
(
 USER_INPUT IN VARCHAR2 
, RELATION IN VARCHAR2 
, OUTPUT OUT VARCHAR2 
) AS 
v_filter nvarchar2(30); -- do you really need Nvarchar2?
v_Value NUMBER(10);
BEGIN
v_Value :=
  CASE WHEN p_relation= 'eingehend' THEN 'D' -- parameter name is relation, not p_relation
    WHEN p_relation = 'ausgehend' THEN 'K'
  END; -- missing semi-colon
SELECT into OUTPUT -- INTO clause must follow select list, not preceed it.
no, name, street,' ' as ADDITION, zipcode,city,' ' as State,country_code, 'Vertragspartner' as CONTLIST
FROM sol_vendor WHERE (company_code like p_userinput OR lower(no) like lower(p_userinput) OR lower(name) like lower(p_userinput)) AND no LIKE 'p_userinput%';
END CONTRACT_PARTNERS; --parameter name is USER_INPUT not p_userinput

SY.

Billy Verreynne

To filter the query Output I could just modify the SQL Query or try something new. The ELO Team told me that i can create procedere.

Wrong.

In Oracle the best method for accessing/processing data is SQL. For high performance SQL (minimal CPU and memory spend on parsing) is SQL with bind variables.

Your SQL used currently, seems to be a SQL using bind variables. This is the correct approach.

User_Q6SP8

I will try out your answers once i get acces to the server again. Thank you very much for all suggestions. Have a nice weekend.