oracle forms question using def_where.

IF (:FREIGHT_ESTIMATES.DSP_REGION_FROM IS NOT NULL)  THEN 
                      def_where:=def_where||' reg_id_from in (select id from dss.regions where '||
   	                    '                region like '''||:freight_estimates.DSP_region_from || ''''||
   	                    ' and            region_type = ''S'')';  
 ELSIF (:FREIGHT_ESTIMATES.DSP_REGION_TO IS NOT NULL)  THEN 
                      def_where:=def_where||'  reg_id_to in (select id from dss.regions where '||
   	                    '                region like '''||:freight_estimates.DSP_region_to || ''''||
   	                    ' and            region_type = ''S'')';   

Open in new window


Now I need to write the code for

ELSIF (:FREIGHT_ESTIMATES.DSP_REGION_FROM IS NOT NULL)  AND (:FREIGHT_ESTIMATES.DSP_REGION_TO IS NOT NULL) THEN 

Open in new window


Help appreciated
LVL 6
anumosesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Helena Markováprogrammer-analystCommented:
I am not sure I understand your requirement correctly. You can try it like this:

IF (:FREIGHT_ESTIMATES.DSP_REGION_FROM IS NOT NULL)  AND (:FREIGHT_ESTIMATES.DSP_REGION_TO IS NOT NULL) THEN
                      def_where:=def_where||' reg_id_from in (select id from dss.regions where '||
                        '                region like '''||:freight_estimates.DSP_region_from || ''''||
                        ' and            region_type = ''S'') and '||' reg_id_to in (select id from dss.regions where '||
                        '                region like '''||:freight_estimates.DSP_region_to || ''''||
                        ' and            region_type = ''S'')';
ELSIF (:FREIGHT_ESTIMATES.DSP_REGION_FROM IS NOT NULL)  THEN
                      def_where:=def_where||' reg_id_from in (select id from dss.regions where '||
                        '                region like '''||:freight_estimates.DSP_region_from || ''''||
                        ' and            region_type = ''S'')';  
ELSIF (:FREIGHT_ESTIMATES.DSP_REGION_TO IS NOT NULL)  THEN
                      def_where:=def_where||'  reg_id_to in (select id from dss.regions where '||
                        '                region like '''||:freight_estimates.DSP_region_to || ''''||
                        ' and            region_type = ''S'')';  

END IF;
0
anumosesAuthor Commented:
I am querying region from = North America and region to = North America. But I am getting records with Asia that I did not query for.
0
Helena Markováprogrammer-analystCommented:
I don't know your table but it seems that there are some problems with your data.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

anumosesAuthor Commented:
can I load that data in sample table and send?
0
Helena Markováprogrammer-analystCommented:
I think that you can. I will try tomorrow.
0
anumosesAuthor Commented:
Let me know
pre-query-trigger.jpg
0
flow01Commented:
You get Asia even if you follow the post 42426989 ?
Try to display de def_where  at the end of the  pre-query-trg to examine the constructed where.

And do you need to change the query ?
If the are no other overridings of the def_where ,  you can define the behaviour in the standard where of the block
(
 (:freight_estimates.DSP_region_from IS NULL
   OR reg_id_from in (select id from dss.regions where region like :freight_estimates.DSP_region_from  and  region_type = 'S')
 )
AND
 (:freight_estimates.DSP_region_to  IS NULL
   OR  reg_id_to in (select id from dss.regions where region like  :freight_estimates.DSP_region_to  and  region_type = 'S')
 )
)
1
awking00Information Technology SpecialistCommented:
>>Now I need to write the code for
ELSIF (:FREIGHT_ESTIMATES.DSP_REGION_FROM IS NOT NULL)  AND (:FREIGHT_ESTIMATES.DSP_REGION_TO IS NOT NULL) THEN
<<

Then what?
0
Mark GeerlingsDatabase AdministratorCommented:
I think you need an "or" instead of the "and" in the suggestion from Helena. like this:
IF (:FREIGHT_ESTIMATES.DSP_REGION_FROM IS NOT NULL)  AND (:FREIGHT_ESTIMATES.DSP_REGION_TO IS NOT NULL) THEN 
                      def_where:=def_where||' reg_id_from in (select id from dss.regions where '||
   	                    '                (region like '''||:freight_estimates.DSP_region_from || ''''||
   	                    ' or             region like '''||:freight_estimates.DSP_region_to || ''''||
   	                    ') and            region_type = ''S'')';  
 ELSIF (:FREIGHT_ESTIMATES.DSP_REGION_FROM IS NOT NULL)  THEN 
                      def_where:=def_where||' reg_id_from in (select id from dss.regions where '||
   	                    '                region like '''||:freight_estimates.DSP_region_from || ''''||
   	                    ' and            region_type = ''S'')';  
 ELSIF (:FREIGHT_ESTIMATES.DSP_REGION_TO IS NOT NULL)  THEN 
                      def_where:=def_where||'  reg_id_to in (select id from dss.regions where '||
   	                    '                region like '''||:freight_estimates.DSP_region_to || ''''||
   	                    ' and            region_type = ''S'')';   

Open in new window


And, you definitely need to check for these both being "not null" before you check for either one being "not null".
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anumosesAuthor Commented:
Elsif will not work. I will have to have If and End if for one condition. I think def_where ignores the elsif.
0
Mark GeerlingsDatabase AdministratorCommented:
"Elsif will not work."  

What?  Are you sure?  Your question included that syntax, and implied that was working.

And, "elsif" is valid Oracle syntax that should work wherever "if" is supported.

I suspect that the real problem here is trying to use "if" in an Oracle Forms block "default where" clause that forces you to use single quotes (and multiple single quotes) to construct a legal "where" clause.
1
flow01Commented:
I did a test  with your code in sql_developer only changing the bind_variables that in your form refer to :block.item and setting those bind variables to North America

declare
  def_where varchar2(32000);
begin
dbms_output.put_line('def_where test');  
IF (:DSP_REGION_FROM IS NOT NULL)  AND (:DSP_REGION_TO IS NOT NULL) THEN
                      def_where:=def_where||' reg_id_from in (select id from dss.regions where '||
                        '                region like '''||:DSP_REGION_FROM || ''''||
                        ' and            region_type = ''S'') and '||' reg_id_to in (select id from dss.regions where '||
                        '                region like '''||:DSP_REGION_TO || ''''||
                        ' and            region_type = ''S'')';
ELSIF (:DSP_REGION_FROM IS NOT NULL)  THEN
                      def_where:=def_where||' reg_id_from in (select id from dss.regions where '||
                        '                region like '''||:DSP_REGION_FROM || ''''||
                        ' and            region_type = ''S'')';  
ELSIF (:DSP_REGION_TO IS NOT NULL)  THEN
                      def_where:=def_where||'  reg_id_to in (select id from dss.regions where '||
                        '                region like '''||:DSP_REGION_TO || ''''||
                        ' and            region_type = ''S'')';  

END IF;
dbms_output.put_line('def_where=');                      
dbms_output.put_line(def_where);
end;

result
def_where test
def_where=
 reg_id_from in (select id from dss.regions where                 region like 'North America' and            region_type = 'S') and  reg_id_to in (select id from dss.regions where                 region like 'North America' and            region_type = 'S')

If you use the resulting def_where  as the where in an sql-query from the table your block is based on you can check the results with your data.
If they don't match  your item's don't contain 'North America'  or the def_where your constructed is not assigned to your block property.
You probably have a procedure to write debugging data  (using text_io,  utl_file or a procedure that insert a record in a debugging table using an discrete transaction.  At the end of the prequery trigger display:
:FREIGHT_ESTIMATES.DSP_REGION_TO
:FREIGHT_ESTIMATES.DSP_REGION_FROM
def_where
GET_BLOCK_PROPERTY('yourblockname',DEFAULT_WHERE)
and check what is really happening
1
Helena Markováprogrammer-analystCommented:
I agree with previous comment. It is curious that you use LIKE without %.
0
flow01Commented:
Like without %: Yes indeed, like has no function here,  but it will not be the problem.
It still should get north americ and not asia.
0
anumosesAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.