Solved

Search functinality is not working in this SQL

Posted on 2014-01-15
9
322 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 76

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 76

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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now