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?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Helena Markováprogrammer-analystCommented:
I don't know your table but it seems that there are some problems with your data.
0
 
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
 
awking00Commented:
>>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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.