Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Search functinality is not working in this SQL

Posted on 2014-01-15
9
Medium Priority
?
334 Views
Last Modified: 2014-01-16
Hello experts,

I have this SQL where I am not getting the expected result.

For example I have to search for global search on COUNTRY , and I typed 'USA' then I get the result with "USA" along with "India" .

But the expected result is to get the "USA" result.  
 

SQL :
SELECT 
     FACILITY_ID,
     facility_name,
     city,
     STATE_PROVINCE,
     COUNTRY,
     VISIT_ID,
     START_DATE,
     FILTER_STATUS,
     VISIT_STATUS,
     LINK
FROM
  (SELECT f.Facility_id,
    f.facility_name,
    f.city,
    F.STATE_PROVINCE,
    F.COUNTRY,
    v.visit_id,
    TRUNC(v.start_date) start_date,
    DECODE(V.VISIT_STATUS, '1', 'Pending', '2','Pending', '3', 'Pending','4' ,'Active', '5','Active', '6','Active', 'D','Cancelled','8','Completed','9','Completed','10','Completed','11','Completed','12','Completed','13','Completed','14','Completed' ) AS filter_status,
    MS_APPS_UTILITIES.GET_DISPLAY_VALUE(100000,'MS SRA VISIT STATUS',V.VISIT_STATUS) AS VISIT_STATUS ,
    MS_SRA_VISIT_SETUP_HOOK_PKG.ms_sra_get_link(v.dd_process_instance_id,'2109760788',v.visit_status,v.visit_id) LINK
  FROM MS_SRA_VISIT_SETUP V,
    (SELECT VISIT_ID ,
      FACILITY_ID,
      facility_name,
      city,
      STATE_PROVINCE,
      COUNTRY
    FROM MS_SRA_FACILITY_INFO p
    WHERE DD_OBJECT_TYPE='MS_SRA_VISIT_SETUP'
    ) F
  WHERE F.VISIT_ID = V.VISIT_ID
  ORDER BY F.VISIT_ID DESC
  )
WHERE (:status                                              IS NULL
OR filter_status                                             = :status)
AND ((TRIM( :global_status)                                 IS NULL
OR (facility_name                                           IS NULL
OR visit_id                                                 IS NULL
OR CITY                                                     IS NULL
OR STATE_PROVINCE                                           IS NULL
OR start_date                                               IS NULL
OR country                                                  IS NULL))
OR INSTR(LOWER(facility_name), LOWER( :global_status))       > 0
OR INSTR(LOWER(visit_id), LOWER( :global_status))            > 0
OR INSTR(LOWER(CITY), LOWER( :global_status))                > 0
OR INSTR(LOWER(STATE_PROVINCE), LOWER( :global_status))      > 0
OR INSTR(LOWER(TO_CHAR(START_DATE)), LOWER( :global_status)) > 0
OR INSTR(LOWER(COUNTRY), LOWER( :global_status))             > 0) ;

Open in new window



Experts ....

If you need the table structure or sample data then I can provide.

Let me know if you need any details.

Thanks,
Thomos
0
Comment
Question by:deve_thomos
  • 5
  • 4
9 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39783022
Check out all the OR's you have.

It reads like:
A is null or B is null or INSTR ...

If ANY of those return true, the entire section (everything in the parenthesis) is true.

Check the data.  The row with India must be meeting one of the other OR'd clauses.

For example:  Maybe It's CITY is NULL?
0
 

Author Comment

by:deve_thomos
ID: 39783060
Hi Experts,
That is not my requirement. i  if i am searching  , suppose country is  usa . it will show all the records belongs USA. but i am getting the record which is  not related   to usa. some other countries  records are showing.

Or if i search a city then I should get only list of  cities which i have input . But there were I am facing issues.  

Thanks
Thomos
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39783084
>>suppose country is  usa

Based on the SQL you posted, how do you specify the term to search is ONLY in the Country field?

That said, check your OR's.

For example:
drop table tab1 purge;
create table tab1(col1 char(1), col2 char(1));
insert into tab1 values('a','b');
insert into tab1 values('c',null);
commit;

Open in new window


I only want col1 that is an 'a'.

Your query is similar to this:
select col1 from tab1 where (col1 is null or col2 is null or col1='a');

Both rows above are returned.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:deve_thomos
ID: 39783130
HI  expert,
I am not getting from your answer. can you   please give an example with query.
the way i have given to you. please i am not able to understand.


Thanks
Thomos
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39783190
My example above is trying to simplify your query and show you the issue you are likely having.

You said you want COUNTRY='USA' but I do not see anything in your query where you specifically search COUNTRY.

You are searching many fields for the same INSTR value.

The other problem you have is all the NULL checks with an OR between them.

Back to my example above:  Where you want country='USA', I search for col1='A'.

Look at my query:  The OR with the null checks cause both rows to be returned even though I have col1='a' because "col2 is null" in the other row.
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 39783213
See if this one helps.

It uses the same column names and most of your existing where clause.

I insert two rows.  One with country of USA and one with India.

I then issue a select after setting :global_status='USA'.

It returns both rows because in the second one, facility_name is null.

You can run the complete test case using sqlplus.

drop table tab1 purge;
create table tab1(
	facility_name char(1),
	visit_id char(1),
	city char(1),
	state_province char(1),
	start_date char(1),
	country varchar2(10)
);

insert into tab1 values('a','a','a','a','a','USA');
insert into tab1 values(null,'a','a','a','a','India');
commit


var global_status varchar2(10)

exec :global_status := 'USA'

select country from tab1
WHERE 
((TRIM( :global_status)                                 IS NULL
OR (facility_name                                           IS NULL
OR visit_id                                                 IS NULL
OR CITY                                                     IS NULL
OR STATE_PROVINCE                                           IS NULL
OR start_date                                               IS NULL
OR country                                                  IS NULL))
OR INSTR(LOWER(facility_name), LOWER( :global_status))       > 0
OR INSTR(LOWER(visit_id), LOWER( :global_status))            > 0
OR INSTR(LOWER(CITY), LOWER( :global_status))                > 0
OR INSTR(LOWER(STATE_PROVINCE), LOWER( :global_status))      > 0
OR INSTR(LOWER(TO_CHAR(START_DATE)), LOWER( :global_status)) > 0
OR INSTR(LOWER(COUNTRY), LOWER( :global_status))             > 0) ;

Open in new window

0
 

Author Comment

by:deve_thomos
ID: 39784489
@slightwv: I undertand but my issue is like I can put 'USA' or 'usa' or null .

If I pass USA then I should get all the records only for USA and this should be case insensitive too.

And if I pass null then I should get all the records .
0
 

Author Closing Comment

by:deve_thomos
ID: 39784493
Thanks a lot...
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39785218
>>If I pass USA then I should get all the records only for USA and this should be case insensitive too.

Then you need to change your logic.

Another issue you have is if any of the other columns would have USA in it somewhere.  For example facility_name has 'Usability Facility name', even if the country is India, it would be returned since it has 'Usa' in it.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question