Home » Developer & Programmer » Forms » matched query issue (11g db , form 6i)
matched query issue [message #666557] Mon, 13 November 2017 06:00 Go to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Sir my question is I want to pick data from 3 tables and call into main form example
1. Item table ...... Want to pick stock_code,unit price, level2 price
2. Client_option..... customer_code and 3 radio buttons regular price, level2 price
3. History ...stock_code
And main form containing
Stock_code, customer_code, price

I want to pick data from item table stock_code, unit price, level price and want to match with history.stock if item.stock_code=:history. Stock_code and clnt_option.customer_code=master.customer_no and if price level2 then 20 dollar else normal price until_PRC
If clnt_option .customer not equal master.customer despite this if item.stock.code =history. Stock_code matched than normal unt_PRC should bring. Sorry about English
May be I could not explained properly but hope experts peoples can understand my problems because English is not my mother tongue.
Example query will be highly appreciated.

Thanks in advance


Re: matched query issue [message #666559 is a reply to message #666557] Mon, 13 November 2017 06:49 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It looks like an ordinary list of values based on a record group query which selects data from 3 tables; use CASE (or DECODE) to deal with the "IF" part of your question.

Basically, you should write a query which returns required data set, test it in SQL*Plus (or any tool you use) and - once you're done - copy it to your form.

[Updated on: Mon, 13 November 2017 06:49]

Report message to a moderator

Re: matched query issue [message #666566 is a reply to message #666559] Mon, 13 November 2017 22:59 Go to previous messageGo to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Select add_item.stk_code,description, qty,unt_PRC, ad_level2,op_typ,cust_no,history. Stk_code into
 :itrn_bar.item_code,
:itrn_bar.item_name,
:itrn_bar.qty,
Aa,
Bb,
Cc,
Dd,ff from add_item,clnt_option,history where add_item.STK_code=:control.a and
History. STK_code=:add_item.stk_code and
Clnt_option.cust_no=:imaster_bar.p_code;
If cc='level' then 
    :itrn_bar=:bb;
Else
:itrn_bar=:aa;
End if;
Above code is not working properly Sir kindly guide me what's wrong with this code not giving correct result.
Example query shall be highly appreciated

[Updated on: Mon, 13 November 2017 23:01]

Report message to a moderator

Re: matched query issue [message #666570 is a reply to message #666566] Tue, 14 November 2017 01:24 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What's wrong with it, apart from the fact that it is difficult to read as it is not formatted? Please, read and follow instructions described in How to use code tags and make your code easier to read.

Unless Aa, Bb etc. are local variables, that code won't even compile. Furthermore, those names ("Aa", "Bb") doesn't seem to be real names so - it might be a good idea to post your actual code, not imaginary one.

There are syntax errors ("History." and "STK_code" are separated by a space).

So, yes - I agree, that code isn't working properly. You should fix it.

Furthermore, it is not a record group query (i.e. you didn't accept my suggestion) so I have no idea what you are doing. Where did you put that code? What do you mean by saying that "it is not working properly"? Is there any error? If so, which one?
Re: matched query issue [message #666638 is a reply to message #666570] Sat, 18 November 2017 03:13 Go to previous messageGo to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
scenario is something like this i have added two codes in add_item table 

1001----containing both prices (a).Regular price (b).level 2 price
1002----containing only regular price

and added twi clients in client table .

c013----which has radio button selected "level2" price.
c014----regular price.


problem is when i am entering "1001" which has both prices working 100% fine with both clients c013,c014
but when i am entering code "1002" which has only regular price and client table radio button selected "regular price"

working with only code "c014" when i entered "c013" bringing blank data code as follo below.

 declare 
	aa varchar2(20);
	bb number(8);
	cc number(10);
	dd varchar2(8);
	ee varchar2(15);
	ff varchar2(20);

	begin

select stk_code,
       ad_level2,
       unt_prc
               into aa,
                    bb,
                    cc
      from add_item 
                  where
            add_item.stk_code=:block3.stk  ; 
             message(cc);
             message(cc);
             select op_typ,cust_no into dd,ee
from clnt_option
      where
              cust_no=:block3.clnt; 
       
       if dd='level2' then 
       
              :block3.prc:=bb;    
          else
              :block3.prc:=cc;    
     
      end if;
    	end;

Thanks

[Updated on: Sat, 18 November 2017 03:14]

Report message to a moderator

Re: matched query issue [message #666641 is a reply to message #666638] Sat, 18 November 2017 11:57 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It appears that you enter item code first. Then you enter client code.

The problem appears when you enter item code which has level 2 price, followed by a client which doesn't have level 2 price option (but only regular price).

Therefore, you should restrict client codes to the ones that match entered item code. How to do that? Either by creating a list of values and set item's "Validate from list" property to "Yes". Or, use WHEN-VALIDATE-ITEM trigger.

By the way, your "formatted" code looks even worse than your previous attempt. What a shame.
Re: matched query issue [message #666646 is a reply to message #666638] Sun, 19 November 2017 01:09 Go to previous messageGo to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Above code is working 100% fine but I had little confusion which is very much cleared thanks to you sir.
Re: matched query issue [message #666649 is a reply to message #666638] Sun, 19 November 2017 03:41 Go to previous message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Above code is working 100% fine but I had little confusion which is very much cleared. Thanks


Regards
Previous Topic: Oracle Developer 10g - webconfig issue
Next Topic: how to navigate on list item
Goto Forum:
  


Current Time: Thu Mar 28 05:52:26 CDT 2024