Re: facing problem with is null in stored procedure
hai shawn green,
thanks for ur response.My situation is to generate a production
plan.for this I collect the data from sales orders and compare the stock
availability in rthe warehouse and if the stock is less than the ordered
quantity I plan for production and for this I have to issue the raw material
required for the production by calculating based on bom(bill of material)
details.I am doing this but for every item in the sales oredr I am inserting
the record even though same item is in the another order.I have to update
the quantity i the item is already in the list otherwise i have to insert.
please give me solution if u got my situation.
On Sun, Apr 12, 2009 at 1:07 AM, Shawn Green wrote:
> syed basha wrote:
>
>> delimiter //
>> create procedure sample1(in p_item varchar(30),
>> in p_size varchar(6),
>> in p_quantity decimal(10,3),
>> in p_unit varchar(3),
>> in p_autoincrement varchar(30),
>> out v_mess varchar(50)
>>
>> )
>> begin
>> declare done int default 0;
>> declare v_bhqty decimal(10,3);
>> declare v_bhunit varchar(3);
>> declare v_blrawm varchar(30);
>> declare v_blqty decimal(10,3);
>> declare v_blunit varchar(3);
>> declare v_puid varchar(30);
>> declare v_conqty decimal(10,3);
>> declare v_plid int(3) default 1;
>> declare v_calqty decimal(10,3);
>> declare c_bomdet cursor for select bh.qty,bh.unit,bl.rawm,bl.qty,bl.unit
>> from bomhead bh,bomlins bl
>> where bl.item=bh.item and bl.szid=bh.szid and bl.item=p_item and
>> bl.szid=p_size;
>> declare continue handler for not found set done=1;
>> select ppid into v_puid from prplrawm where ppid=(select
>> concat('pph',max(convert(substr(ppid,4),unsigned integer))) from
>> prpllins);
>> open c_bomdet;
>> repeat
>> fetch c_bomdet into v_bhqty,v_bhunit,v_blrawm,v_blqty,v_blunit;
>> if not done then
>> call convertion(p_unit,v_bhunit,p_item,p_size,p_quantity,v_conqty);
>> set v_calqty=v_conqty*v_blqty;
>> if v_puid is not null then
>> set v_mess='max id is exists';
>> else
>>
>> insert into prplrawm(ppid,plid,item,szid,rawm,rqty,runt)
>> values(p_autoincrement,v_plid,p_item,p_size,v_blrawm,v_blrqty,v_blrunt);
>> set v_plid=v_plid+1;
>> end if;
>> end if;
>> until done end repeat;
>> close c_bomdet;
>> end
>> //
>> delimiter ;
>>
>> when i call this procedure it is not performing the insert statement even
>> though the condition is true.
>>
>> please tell me why.
>>
>> thank you
>>
>>
>>
> How do you know your condition is true?
>
> Could this be part of a transaction that is rolled-back instead of
> committed?
>
> There are too many unknowns about your situation to make a clear judgement
> on this particular problem.
>
> --
> Shawn Green, MySQL Senior Support Engineer
> Sun Microsystems, Inc.
> Office: Blountville, TN
>
>
>
--
sibasha
Newbie ... Schema details listing
I have written a .cfm to output a listing of columns with Name, Format, Size, NULL, KEY, etc. One thing I need to include is whether the Primary Key is AUTO_INCREMENT That element does not appear to be in the same SCHEMA table. Where is it ? and How can I use it with this Query ... ? SELECT COLUMN_NAME AS pCname, DATA_TYPE AS pDtype, IS_NULLABLE AS pISnull, CHARACTER_MAXIMUM_LENGTH AS pMAXlen, COLUMN_DEFAULT AS pCdefault, COLUMN_TYPE AS pCtype, COLUMN_KEY AS pCkey, NUMERIC_SCALE AS pNscale, NUMERIC_PRECISION AS pNprec FROMINFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = "Products" AND TABLE_SCHEMA = "bs3578"; -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12962 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message
RE: Newbie ... Schema details listing
in information_schema.tables e.g. SELECT AUTO_INCREMENT from information_schema.tables where WHERE TABLE_NAME = "Products" AND TABLE_SCHEMA = "bs3578"; Viel Gluck, Martin __ Disclaimer and Confidentiality/Verzicht und Vertraulichkeitanmerkung / Note de déni et de confidentialité This message is confidential. If you should not be the intended receiver, then we ask politely to report. Each unauthorized forwarding or manufacturing of a copy is inadmissible. This message serves only for the exchange of information and has no legal binding effect. Due to the easy manipulation of emails we cannot take responsibility over the the contents. Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. > From: bobsh...@ntlworld.com > To: mysql@lists.mysql.com > CC: win32-h...@lists.mysql.com > Subject: Newbie ... Schema details listing > Date: Sun, 12 Apr 2009 14:54:50 +0100 > > I have written a .cfm to output a listing of columns with Name, Format, Size, > NULL, KEY, etc. > > One thing I need to include is whether the Primary Key is AUTO_INCREMENT > That element does not appear to be in the same SCHEMA table. > > Where is it ? and How can I use it with this Query ... ? > > > SELECT COLUMN_NAME AS pCname, > DATA_TYPE AS pDtype, > IS_NULLABLE AS pISnull, > CHARACTER_MAXIMUM_LENGTH AS pMAXlen, > COLUMN_DEFAULT AS pCdefault, > COLUMN_TYPE AS pCtype, > COLUMN_KEY AS pCkey, > NUMERIC_SCALE AS pNscale, > NUMERIC_PRECISION AS pNprec > FROMINFORMATION_SCHEMA.COLUMNS > WHERE TABLE_NAME = "Products" AND TABLE_SCHEMA = "bs3578"; > > > > > > -- > I am using the free version of SPAMfighter. > We are a community of 6 million users fighting spam. > SPAMfighter has removed 12962 of my spam emails to date. > Get the free SPAMfighter here: http://www.spamfighter.com/len > > The Professional version does not have this message _ Rediscover Hotmail®: Now available on your iPhone or BlackBerry http://windowslive.com/RediscoverHotmail?ocid=TXT_TAGLM_WL_HM_Rediscover_Mobile1_042009
