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

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;
 begin 
 open Mytest;
    fetch Mytest into Rtest;
    if Mytest%notfound then
        close Mytest;
    end if;
    close Mytest;
if 
    (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;
           
	end;
		
		 
				 
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
            (
                 PRCnumber,
	         shirt_num,
		shshcolor,
                 shstyle,
                 Created
            )
       values
            (
                @PRCnumber,
		@shirt_num,
                @shshcolor,
                @shstyle,
                sysdate
             );


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: 67842
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
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: Sat May 08 20:02:47 CDT 2021