CODEWITHSUNDEEP
oracle-databaseplsql
mf.cummings
mf.cummings

Reputation: 315

Create simple PL/SQL variable - Use Variable in WHERE clause

Thanks for looking...

I've spent hours researching this and I can't believe it's that difficult to do something in PL/SQL that is simple in TSQL.

I have a simple query that joins 2 tables:

     Select DISTINCT
      to_char(TO_DATE('1899123000', 'yymmddhh24')+ seg.NOM_DATE, 'mm/dd/yyyy') AS "Record Date"
    , cd.CODE
    , EMP.ID
    , EMP.SHORT_NAME

    FROM
     EWFM.GEN_SEG seg join EWFM.SEG_CODE cd ON seg.SEG_CODE_SK = cd.SEG_CODE_SK
     join EMP on seg.EMP_SK = EMP.EMP_SK
    where NOM_DATE = vMyDate;

I use Toad Date Point and I'm querying against an Oracle Exadata source. The resulting query will be dropped into a visualization tool like QlikView or Tableau. I'd like to create a simple variable to use the the WHERE clause as you can see in the code.

In this example, NOM_DATE is an integer such as 42793 (2/27/2017) as you can see in the first row "Record Date". Nothing new here, not very exciting... Until... I tried to create a variable to make the query more dynamic.

I've tried a surprising variety of examples found here, all have failed. Such as:
declare myDate number(8); Begin myDate := 42793; --Fail ORA-06550 INTO Clause is expected

variable nomDate NUMBER
DEFINE nomDate = 42793
EXEC : nomDate := ' & nomDate'
...where NOM_DATE = ( & nomDate) ;

--ORA-00900: invalid SQL statement

and

variable nomDate NUMBER;
EXEC nomDate := 42793;
select count(DET_SEG_SK) from DET_SEG
where NOM_DATE = :nomDate;

--ORA-00900: invalid SQL statement

and several more.. hopefully you get the idea. I've spent a few hours researching stackoverflow for a correct answer but as you can see, I'm asking you. From simple declarations like "Var" to more complex " DECLARE, BEGIN, SELECT INTO...." to actually creating Functions, using cursors to iterate the output.... I still can't make a simple variable to use in a Where clause.

Please explain the error of my ways.

--Forlorn SQL Dev

Upvotes: 0

Views: 2066

Answers (3)

BobC
BobC

Reputation: 4416

Since you are using an implicit cursor, you have to select then INTO variables. Now I d not know the data types of you variables, so I have just guessed in this example below, but hopefully you get the point.

Two other things I should mention

  1. Why are you TO_CHARing you DATE. Just use a DATE datatype. Also, I think your format mask is wrong too 1899123000 does not match yymmddhh24.
  2. In explicit cursor expects exactly one row; no rows and you get NO_DATA_FOUND; more than one and you get TOO_MANY_ROWS
Declare 
   myDate number(8) := 42793; 
    /* These 4 variable data types are a guess */
   v_record_date varchar2(8);
   v_cd_code varchar2(10);
   v_emp_id number(4);
   v_emp_short_name varchar2(100);
 BEGIN 
 Select DISTINCT to_char(TO_DATE('1899123000', 'yymmddhh24')
                     + eg.NOM_DATE, 'mm/dd/yyyy') AS "Record Date" 
 , cd.CODE 
 , EMP.ID 
 , EMP.SHORT_NAME 
 INTO v_record_date, v_cd_code, v_emp_id, v_emp_short_name
 FROM EWFM.GEN_SEG seg 
 join EWFM.SEG_CODE cd 
   ON seg.SEG_CODE_SK = cd.SEG_CODE_SK 
 join EMP 
     on seg.EMP_SK = EMP.EMP_SK 
 where NOM_DATE = myDate; 
 END;
 /

Upvotes: 1

MT0
MT0

Reputation: 167867

VARIABLE vMyDate NUMBER;

BEGIN
  :vMyDate  := 42793;
END;
/

-- or
-- EXEC :vMyDate := 42793;

SELECT DISTINCT
       TO_CHAR( DATE '1899-12-30' + seg.NOM_DATE, 'mm/dd/yyyy') AS "Record Date"
     , cd.CODE
     , EMP.ID
     , EMP.SHORT_NAME
FROM   EWFM.GEN_SEG seg
       join EWFM.SEG_CODE cd
       ON seg.SEG_CODE_SK = cd.SEG_CODE_SK
       join EMP
       on seg.EMP_SK = EMP.EMP_SK
WHERE  NOM_DATE = :vMyDate;

Upvotes: 1

Frank
Frank

Reputation: 1

You put the variables with getter and setter in a package.

Then use a view that uses the package getter

Personally I prefer to use a collection that way I can do a select * from table (packagage.func(myparam))

Upvotes: 0

Related Questions