Problem reading null
-
Hi, I have a table with floor_id field. The floor_id is null and I am trying to read it when I read it using where floor_id = NULL I don't get the correct result but when I try where floor_id IS NULL it's working so how can I do when passing a parameter like where floor_id = param_floor? Thanks, Jassim
Technology News @ www.JassimRahma.com
As a general solution you can try:
WHERE floor_id = param_floor
OR (floor_id IS NULL AND param_floor IS NULL)Wrong is evil and must be defeated. - Jeff Ello
-
As a general solution you can try:
WHERE floor_id = param_floor
OR (floor_id IS NULL AND param_floor IS NULL)Wrong is evil and must be defeated. - Jeff Ello
hmm but can I ask why? I was using just floor_id = param_floor in other application and it was working fine.. why it's acting funny now?
Technology News @ www.JassimRahma.com
-
hmm but can I ask why? I was using just floor_id = param_floor in other application and it was working fine.. why it's acting funny now?
Technology News @ www.JassimRahma.com
You cannot equal null in tristate logics, it's undefined and does not equal anything. You can only check for null in both the field and the parameter.
Wrong is evil and must be defeated. - Jeff Ello
-
You cannot equal null in tristate logics, it's undefined and does not equal anything. You can only check for null in both the field and the parameter.
Wrong is evil and must be defeated. - Jeff Ello
so what if I want to say:
floor_id = param_floor
where the passed param_floor from my application could be a value or null. How can I do this?
Technology News @ www.JassimRahma.com
-
hmm but can I ask why? I was using just floor_id = param_floor in other application and it was working fine.. why it's acting funny now?
Technology News @ www.JassimRahma.com
Have a bit of a google as there is no simple way to explain it, however it goes something like this: Null is not nothing, null is indeterminate, in other words null is an unknown value. So in trying to compare an unknown value with another unknown value the result is always false. Null == Null returns false.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
so what if I want to say:
floor_id = param_floor
where the passed param_floor from my application could be a value or null. How can I do this?
Technology News @ www.JassimRahma.com
Based on your question and
Jassim Rahma wrote:
where the passed param_floor from my application could be a value or null.
floor_id = param_floor
is valid.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Based on your question and
Jassim Rahma wrote:
where the passed param_floor from my application could be a value or null.
floor_id = param_floor
is valid.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
GuyThiebaut wrote:
is valid.
but it returns nothing when the passed value is null and I do have null in the table! here is my stored procedure:
SELECT assets$audit.audit_id,
assets$audit.audit_date,
assets$status.status_name,
assets$conditions.condition_name,
assets$audit.quantity
FROM assets$audit
JOIN assets$status ON `assets$status`.status_id = assets$audit.audit_status
LEFT JOIN assets$conditions ON `assets$conditions`.condition_id = assets$audit.audit_condition
WHERE item_id = param_item_id
AND location = param_loaction
AND floor = param_floor
AND room = param_room
AND department = param_department
AND employee = param_employee
ORDER BY audit_date DESC, created_date DESC;Technology News @ www.JassimRahma.com
-
Based on your question and
Jassim Rahma wrote:
where the passed param_floor from my application could be a value or null.
floor_id = param_floor
is valid.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
and this is my .NET application code:
sql_connection = new MySqlConnection(xmlClass.get_connectionstring("Assets\\Assets"));
sql_connection.Open();
sql_command = new MySqlCommand("sp_populate_asset_audits", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_command.CommandTimeout = Convert.ToInt32(string_encryptor.DecryptString(xmlClass.read_xml_value("Assets\\Assets", "CommandTimeOut"), "JassimRahma@731004167"));
sql_command.Parameters.AddWithValue("param_item_id", item_id).MySqlDbType = MySqlDbType.Int32;
sql_command.Parameters.AddWithValue("param_loaction", Convert.ToInt32(gridLocation.GetRowCellValue(gridLocation.FocusedRowHandle, "location_id"))).MySqlDbType = MySqlDbType.Int32;
if (gridLocation.GetRowCellValue(gridLocation.FocusedRowHandle, "floor_id") != null) sql_command.Parameters.AddWithValue("param_floor", Convert.ToInt32(gridLocation.GetRowCellValue(gridLocation.FocusedRowHandle, "floor_id"))).MySqlDbType = MySqlDbType.Int32; else sql_command.Parameters.AddWithValue("param_floor", DBNull.Value).MySqlDbType = MySqlDbType.Int32;
if (gridLocation.GetRowCellValue(gridLocation.FocusedRowHandle, "room_id") != DBNull.Value) sql_command.Parameters.AddWithValue("param_room", Convert.ToInt32(gridLocation.GetRowCellValue(gridLocation.FocusedRowHandle, "room_id"))).MySqlDbType = MySqlDbType.Int32; else sql_command.Parameters.AddWithValue("param_room", DBNull.Value).MySqlDbType = MySqlDbType.Int32;
if (gridLocation.GetRowCellValue(gridLocation.FocusedRowHandle, "department_id") != DBNull.Value) sql_command.Parameters.AddWithValue("param_department", Convert.ToInt32(gridLocation.GetRowCellValue(gridLocation.FocusedRowHandle, "department_id"))).MySqlDbType = MySqlDbType.Int32; else sql_command.Parameters.AddWithValue("param_department", DBNull.Value).MySqlDbType = MySqlDbType.Int32;
// sql_command.Parameters.AddWithValue("param_employee", Convert.ToInt32(gridLocation.GetRowCellValue(gridLocation.FocusedRowHandle, "employee"))).MySqlDbType = MySqlDbType.Int32;
sql_command.Parameters.AddWithValue("param_employee", DBNull.Value).MySqlDbType = MySqlDbType.Int32;Technology News @ www.JassimRahma.com
-
GuyThiebaut wrote:
is valid.
but it returns nothing when the passed value is null and I do have null in the table! here is my stored procedure:
SELECT assets$audit.audit_id,
assets$audit.audit_date,
assets$status.status_name,
assets$conditions.condition_name,
assets$audit.quantity
FROM assets$audit
JOIN assets$status ON `assets$status`.status_id = assets$audit.audit_status
LEFT JOIN assets$conditions ON `assets$conditions`.condition_id = assets$audit.audit_condition
WHERE item_id = param_item_id
AND location = param_loaction
AND floor = param_floor
AND room = param_room
AND department = param_department
AND employee = param_employee
ORDER BY audit_date DESC, created_date DESC;Technology News @ www.JassimRahma.com
Read and understand this visual explanation of sql joins[^] then you will have an idea of what is happening. You may not have a null value in the table but you certainly have a left join which will return nulls if there is no corresponding row on the right side of the join.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
so what if I want to say:
floor_id = param_floor
where the passed param_floor from my application could be a value or null. How can I do this?
Technology News @ www.JassimRahma.com
It was a general solution i gave you, so it work for that case too. :)
Wrong is evil and must be defeated. - Jeff Ello
-
Read and understand this visual explanation of sql joins[^] then you will have an idea of what is happening. You may not have a null value in the table but you certainly have a left join which will return nulls if there is no corresponding row on the right side of the join.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
the left join dose not return null
Technology News @ www.JassimRahma.com
-
Hi, I have a table with floor_id field. The floor_id is null and I am trying to read it when I read it using where floor_id = NULL I don't get the correct result but when I try where floor_id IS NULL it's working so how can I do when passing a parameter like where floor_id = param_floor? Thanks, Jassim
Technology News @ www.JassimRahma.com
Hello Jassim, while working in a procedure then i got the same problem. so based on my experience i would suggest to use decode function. decode function is similar to if else block.
decode(floor_id,null,1,floor_id)=decode(param_floor,null,1,param_floor)
thanks in advance regards, Sundeep