Forum Stats

  • 3,769,363 Users
  • 2,252,957 Discussions
  • 7,875,005 Comments

Discussions

Error in SQL Query

User_Q6SP8
User_Q6SP8 Member Posts: 4 Green Ribbon

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;

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond

    Hi,

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

    User_Q6SP8
  • Paulzip
    Paulzip Member Posts: 8,494 Blue Diamond

    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?

    User_Q6SP8
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,594 Red Diamond

    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?

    User_Q6SP8
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond
    edited Dec 18, 2020 11:46AM

    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
  • User_Q6SP8
    User_Q6SP8 Member Posts: 4 Green Ribbon

    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
    Solomon Yakobson Member Posts: 18,916 Red Diamond

    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.

    User_Q6SP8
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,594 Red Diamond

    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
  • User_Q6SP8
    User_Q6SP8 Member Posts: 4 Green Ribbon

    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.