|
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
|
|
|
|
|
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
|
|
|
|
|
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", DBNull.Value).MySqlDbType = MySqlDbType.Int32;
Technology News @ www.JassimRahma.com
|
|
|
|
|
It was a general solution i gave you, so it work for that case too. 
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
I have decimal(10,2) table column, is there way to format it like if you have 10.02 it will display 10.02 then if you 10.50 it will display 10.5.
It look like that format of .ToString("##.##") in c#.
BTW this is on MySQL.
I just want to omit the last zero of the decimal.
I will appreciate for any help will come.
modified 20-Nov-14 19:30pm.
|
|
|
|
|
Where will the number display, in a report, in a browser, in a Windows Form?
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
Actually it will be used in windows forms.
|
|
|
|
|
If you convert it to float and then round in SQL it would work as you need.
check this
declare @no float=10.20
select Round(@no,2)
i have executed it in sql server, so check if that works in My SQL or not
Mark the answer as accepted if that worked for you .
And for down-voters please specify the reason to improve the solution .
|
|
|
|
|
Thank you for your response but, it doesn't seems will work on MySql
|
|
|
|
|
did you tried it
Mark the answer as accepted if that worked for you .
And for down-voters please specify the reason to improve the solution .
|
|
|
|
|
Yes I did, but there is no way I can cast decimal to float.
I did a work around but it is ugly, it could cause a performance issue, so I am still looking for a better solution.
|
|
|
|
|
In one table i am having 30 columns,in each column i mentioned data type as long text with character set as utf-8.When i am inserting the text from c# it only saving some limited number of characters,It is not saving full text.I want to insert long type text to almost to 30 columns I tried with changing different data types and character sets but not working.Please help me in getting this mysql problem.Example text from c# to insert in mysql column
height:30px;width:70px;background-color:rgba(255,255,255,1);color:#000000;font-family:Francois One, sans-serif;font-size:14px;padding:0px;margin:0px;border-radius:0px;border:solid 0px rgba(255,255,255,1);
|
|
|
|
|
Soooo... what's the definition of the column?
Where's the code you're using?
|
|
|
|
|
I am trying to replace names found in 'xml' fieldname as hyperlinks, by matching with the names in database_tags.
I am using the following function below, however the UDF is only recognizing one name from the XML fieldname data, instead of all the names present in the XML data.
ALTER FUNCTION [dbo].[ReplaceTags](@XML VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @N VARCHAR(MAX)
SELECT @N = [Name] FROM [dbo].[database_tags]
WHERE @XML LIKE '%'+[Name]+'%'
AND UploadDate >= '2014-09-01'
IF @N IS NOT NULL
BEGIN
SELECT @XML = REPLACE(@XML,
@N,
'<a href="<a href="pagename.aspx?tag='+@N+'">'+@N+'</a>')
END
RETURN @XML
END
for example, if the XML input data is the following: It consists of: BANKP, BCJA, BCJAM, BFTH, BFTH, and EMPOP.
But the updated function is only recognizing two of names BFTH, BFTH, as hyperlinks, from the database_tags table.
Is there a way to get the function to recognize more than one names as hyperlinks.
Thank you very much for your time and help.
|
|
|
|
|
Try this:
ALTER FUNCTION [dbo].[ReplaceTags]( @XML VARCHAR(MAX) )
RETURNS VARCHAR(MAX)
AS
BEGIN
SELECT
@XML = Replace(@XML, [Name], '<a href="pagename.aspx?tag=' + [Name] + '>' + [Name] + '</a>')
FROM
[dbo].[database_tags]
WHERE
@XML LIKE '%' + [Name] + '%'
And
UploadDate >= '2014-09-01'
;
RETURN @XML;
END
GO
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank you very much for your reply and help.
I have already tried the above approach and unfortunately, it gives me incorrect output.
I pass the following XML Input to the UDF:
<Body><p align="justify">One is a 1m block of AIREM 2006-1X 2A3, which has never appeared on SMO.<p></Body>
the function above, outputs the following (which is incorrect).
One is a £1m block of <a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a>"><a href="<a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a></a>"><a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a>"><a href="<a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a>
The desired output should be :
<Body><p align="justify">One is a 1m block of <a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a>, which has never appeared on SMO.<p></Body>
I have attached an example of my dataset in the following link below, for further reference as to what my dataset types are.
http://sqlfiddle.com/#!6/96cac8/2
I looked into a cursor approach for this replace function and have come up with the following below. However, I am still experiencing the same output error, as explained above. the function loops through continuously and creates duplicate names of hyperlinks, within the XML data.
ALTER FUNCTION [dbo].[ReplaceTags2](@XML VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Name VARCHAR(MAX)
DECLARE CUR CURSOR FAST_FORWARD FOR
SELECT name
FROM [dbo].[database_tags]
Where UploadDate >= '2014-09-01'
and @XML LIKE '%' + Name + '%'
OPEN CUR
WHILE 1 = 1
BEGIN
FETCH cur INTO @name
IF @@fetch_status <> 0
BREAK
BEGIN
SELECT @XML = REPLACE(@XML,
@Name,
'<a href="<a href="pagename.aspx?tag='+@Name+'">'+@Name+'</a>')
END
END
CLOSE CUR;
DEALLOCATE CUR;
RETURN @XML
END
Please advice further, if possible.
Thank you for your help and time.
|
|
|
|
|
I can't reproduce the problem using your SQL Fiddle data and the code I posted.
<Body><p align="justify">One is a 1m block of <a href="pagename.aspx?tag=AIREM 2006-1X 2A3>AIREM 2006-1X 2A3</a>, which has never appeared on SMO.<p></Body>
<body>It consists of: <a href="pagename.aspx?tag=BANKP>BANKP</a>, <a href="pagename.aspx?tag=BCJA>BCJA</a>, <a href="pagename.aspx?tag=BCJA>BCJA</a>M, <a href="pagename.aspx?tag=<a href="pagename.aspx?tag=BFTH>BFTH</a>><a href="pagename.aspx?tag=BFTH>BFTH</a></a>, <a href="pagename.aspx?tag=<a href="pagename.aspx?tag=BFTH>BFTH</a>><a href="pagename.aspx?tag=BFTH>BFTH</a></a>, and <a href="pagename.aspx?tag=EMPOP>EMPOP</a>.</body>
DECLARE @XML nvarchar(max);
SELECT
@XML = XML
FROM
article
WHERE
id = '1'
;
SELECT
@XML = Replace(@XML, [Name], N'<a href="pagename.aspx?tag=' + [Name] + N'>' + [Name] + N'</a>')
FROM
[dbo].[database_tags]
WHERE
@XML LIKE N'%' + [Name] + N'%'
;
SELECT @XML As Result;
NB: You should avoid using text, ntext or image types in SQL. They are deprecated and will be removed in a future version[^]. Use varchar(max), nvarchar(max) and varbinary(max) instead.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Please note that I've repeated Richard's solution and don't get any problem either. I know that it's frustrating!
Can we see the code you are using to call the UDF?
Life is like a s**t sandwich; the more bread you have, the less s**t you eat.
|
|
|
|
|
Thank you for your suggestion and help.
I am manage to get the cursor function work by adding a distinct clause in the tags select query of function:
DECLARE @fullame VARCHAR(MAX)
DECLARE CUR CURSOR FAST_FORWARD FOR
SELECT distinct name
FROM database_tags
Where UploadDate >= '2014-09-01'
Thank you very much Richard for your solution, I am sorry to inform, the above solution was little slow loading the articles with hyperlinks name, hence I choose the cursor function approach.
Thank you all, for your time and suggestion for this post. I appreciate all your help.
|
|
|
|
|
You are welcome! Richard's solution should have worked - that is the one I also tested and is exactly the approach I would have used. I only mentioned the cursor to help you understand the problem, but if it works ...!
If Richard's query is performing badly, it could probably be improved with a well-chosen additional index. When you have some time, I'd suggest running it with the SQL query analyzer.
Good luck!
Life is like a s**t sandwich; the more bread you have, the less s**t you eat.
|
|
|
|
|
Your problem is caused by the fact that only one name match will work. Although your first select statement will potentially return multiple rows @N will only ever have one value. Using Richard's method you are wrapping everything in together. Your approach would have required being wrapped in a cursor to enumerate your first select.
Life is like a s**t sandwich; the more bread you have, the less s**t you eat.
modified 18-Nov-14 16:23pm.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
