Home » SQL & PL/SQL » SQL & PL/SQL » Comparing input values with table data (Oracle 19c Enterprise Edition Release
Comparing input values with table data [message #683878] Tue, 02 March 2021 04:53 Go to next message
Messages: 32
Registered: January 2016
Location: delhi

I want to generate a random number based on below three conditions when either of them qualifies. The Random number will be used to identify the lot created on a day

  1. Generate new PRCnumber when color changes
  2. Generate a new PRCnumber when Day changes
  3. Generate a New PRCnumber when Shirt number changes(Input Value)

This is code I have been trying and need help on how to put comparison operators. I have tried two conditions but it's not working as per my expectations and I still have one more conditions to (Color Changed) add. Please advise if other details required

 create or replace procedure(Shnum IN Shirts.shirt_num%type)

   cursor Mytest is 
   select shcolor, shstyle, product_line from shirts, Colors
    where  shirts.shcolor = Colors.shcolor 
	       and shirts.shirt_num = Shnum;
 open Mytest;
    fetch Mytest into Rtest;
    if Mytest%notfound then
        close Mytest;
    end if;
    close Mytest;
    (to_date(to_char(sysdate, 'YYYYMMDD') != to_date(to_char(ShProd.created , 'YYYYMMDD') or  ShProd.shirt_num !=mytest.Shnum)   then 
    select RAND() into PRCnumber; 
  end if;
After generating the PRCnumber, it will be saved in another table ShProd along with the timestamp and Shirt number. I am using this table to
compare in above IF condition
  insert into ShProd

Re: Comparing input values with table data [message #683880 is a reply to message #683878] Tue, 02 March 2021 06:01 Go to previous message
Michel Cadot
Messages: 68043
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The Random number will be used to identify the lot created on a day

Why a random number? Use a sequence.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

A VALID test case, your code contains many syntax errors and does not compile.

[Updated on: Tue, 02 March 2021 06:03]

Report message to a moderator

Previous Topic: remove comments from all_source output
Next Topic: Group result set in a count of 5 records.
Goto Forum:

Current Time: Thu Dec 02 16:53:55 CST 2021