Comparing input values with table data [message #683878] |
Tue, 02 March 2021 04:53 |
|
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
- Generate new PRCnumber when color changes
- Generate a new PRCnumber when Day changes
- 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 |
|
Michel Cadot
Messages: 68665 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
|
|
|