Incorrect query filter according to the requirement

All we need is an easy explanation of the problem, so here it is.

My requirement wholly depends on the QUERY.

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

Project_name (Always required)
Date (Not always)
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)
        )

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Method 2

If you want to restrict the resultset based on the vehicle_no, then use AND instead of OR.

 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'
            )

You could use something like this, though I am not a fan of code like this:

 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)
            )

You can use the above little trick on other parameters as well.

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

Where variables starting with : are bind variables, so that is where you bind your actual values.

And this is really bad practice:

TO_CHAR (date_in, 'dd-MM-yyyy') = '26-11-2015'

You should compare same datatypes. If you must convert different datatypes, converting the constant or bind variable is way better. The above prevents using a regular index on the date_in column because of the conversino. Do it like this:

date_in = to_date('26-11-2015', 'dd-MM-yyyy')

Method 3

After so much of thinking about logic and OPERATORS what I simply did was

first I created a simple string in my code like below

string date = txtdatefilter.Text;

in which I will assign the value of date textbox.

My main issue was, I was not able to decode my date values. So I checked the condition in my C# code like below:-

if (date == "")
    {

        // Query if date was not added [Only difference was **OR** operator] here
    }

    else
    {
        //// Query if date was not added [Only difference was **AND** operator here]
    }

And the query was

if (date == "")
    {

        strquerry = "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 = '" + cmbProjectName.SelectedValue + "'  AND TO_CHAR (date_in, 'dd-MM-yyyy') = '" + txtdatefilter.Text + "' " +
                 "OR vehicle_no = decode('" + ddlVehicleno.SelectedValue + "', '0', vehicle_no, '" + ddlVehicleno.SelectedValue + "')) AND sv.delete_flag = 'N' ORDER BY date_in DESC, vehicle_no";
    }

    else
    {
        strquerry = "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 = '" + cmbProjectName.SelectedValue + "'  AND TO_CHAR (date_in, 'dd-MM-yyyy') = '" + txtdatefilter.Text + "' " +
                "AND vehicle_no = decode('" + ddlVehicleno.SelectedValue + "', '0', vehicle_no, '" + ddlVehicleno.SelectedValue + "')) AND sv.delete_flag = 'N' ORDER BY date_in DESC, vehicle_no";
    }

I thought of doing it by creating a function, but i was rather preferring my C# code for this.

That resolved my query

Any answer better than this would be most welcomed

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

All we need is an easy explanation of the problem, so here it is.

How to solve :

Method 1

Method 2

Method 3

You Might Also Like

How large should be mysql innodb_buffer_pool_size?

MySQL transaction size – how big is too big?

how to add a query to postgres and have it show up as a table

Leave a Reply Cancel reply