Oracle – Incorrect Query Filter Solution

oracle

My requirement wholly depends on the QUERY.

I am filtering my gridview on the basis of three parameters.

  1. Project_name (Always required)
  2. Date (Not always)
  3. Vehicle_No (Not always)

So, what my requirement is,

With project_name either any one can be used for filtering my gridview.

Currently my debugged query works exactly fine for any two parameters.

Below is my debugged query:-

SELECT DISTINCT sv.mkey, vehicle_no,
               TO_CHAR (date_in, 'dd-MM-yyyy')
            || ' & '
            || time_in vehicleindate_time,
               TO_CHAR (date_out, 'dd-MM-yyyy')
            || ' & '
            || time_out vehicleoutdate_time,
            gate_no_in || ' & ' || gate_no_out ingate_outgateno,
            gd.good_type goods_type, net_weight netweight,
               TO_CHAR (challan_date, 'dd-MM-yyyy')
            || ' & '
            || challan_no challandate_no,
            remark_in remarkin, NULL receipt_no, date_in
       FROM xxcus.xxgid_supinv sv,
            xxcus.xx_supinv_goodtype gd,
            xxcus.xxacl_xxgid_user_mst ms
      WHERE (gd.good_type_code(+) = sv.good_type)
        AND (       sv.project_id = '1368'
                AND TO_CHAR (date_in, 'dd-MM-yyyy') = '26-11-2015'
             OR vehicle_no = '0'
            )
        AND sv.delete_flag = 'N'
   ORDER BY date_in DESC, vehicle_no

The above query shows me the exact result for project_name and Date which I entered.

But when I add the third parameter say Vehicle_no it doesn't show the data for that exact vehicle_no it still shows me the same data which I looked for 2 parameters.

I am using Oracle for the database.

LATEST UPDATE WHICH IS NOT WORKING

I am confused as how to deal with the query, as Balazs' solution was working fine when I selected, Project_name, Date and then vehicle_no (not necessary). But it was not fetching proper result when I took first as Project_name, and Vehicle_no. Any guesses why?

The issue is when I only filter with Project_name and Vehicle_no the date_column value comes as 0 so it doesn't show me any row, which is incorrect as I have data for that Project_name and Vehicle_no.

I have used Balaz's second query for the solution which is below

WHERE (gd.good_type_code(+) = sv.good_type)
    AND (       sv.project_id = '1368'
            AND TO_CHAR (date_in, 'dd-MM-yyyy') = '26-11-2015'
         AND vehicle_no = decode(:vehicle_no, '0', vehicle_no, :vehicle_no)
        )

Best Answer

The problem is with the below bind variable substitution:

AND ( sv.project_id                 = DECODE(:'  1368','0', project_id, :'1368')
AND TO_CHAR (date_in, 'dd-MM-yyyy') = DECODE(:'  ', '0', date_in, :'  ')
AND vehicle_no                      = DECODE(:'  GJ-06-0Z-9202', '0', vehicle_no, :'  GJ-06-0Z-9202') ))

Your application should pass the value to the bind variable correctly. The query you need to use is:

AND sv.project_id = DECODE(:project_id, 0, project_id, :project_id) 
AND date_in       = DECODE(:date_in, 0, date_in, :date_in) 
AND vehicle_no    = DECODE(:vehicle_no, 0, vehicle_no, :vehicle_no)

The above DECODE is just like an IF-ELSE construct. It means, if the passed values are 0, then use the column value from the table, else, use the value that is passed.

Your application need to assign values to the following bind variables:

  • :project_id
  • :date_in
  • :vehicle_no

For example, in SQL*Plus:

SQL> var num number
SQL> exec :num := 10

PL/SQL procedure successfully completed.

SQL> print num

       NUM
----------
        10

SQL> var text varchar2(10)
SQL> exec :text := 'coder'

PL/SQL procedure successfully completed.

SQL> print text

TEXT
--------------------------------
coder

Also, no need to use TO_CHAR for the DATE. Pass the value as DATE. Else, use TO_DATE on the literal on the right hand side. Leave the date as it is, since you should compare the dates and not strings. TO_CHAR converts the date into string in your desired format. You need TO_CHAR only to display the date, not while comparing or date arithmetic.

UPDATE :

Your application should pass the value to the bind variable properly. After passing the value, your query still has the bind variable symbol :.

:' 1368'

If you capture the bind variable value, then it should just be:

'1368'

Also, there is a leading space in the value. Although, it won't throw any error, it would give you incorrect output.

Related Question