Solved

Search functinality is not working in this SQL

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
JDeveloper 12c for 32 bit 4 67
null value 15 92
EXECUTE IMMEDIATE 5 52
What is the version of ojdbc6.jar 2 37
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

914 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

20 Experts available now in Live!

Get 1:1 Help Now