Logo SQLinForm

 

Re: Two issues

[solved] Two issues
September 23, 2014 11:48PM
I just upgraded to the latest version (engine 4.9.30, gui 4.9.30) and while it's still a great product I have noticed a couple of issues. Using the following example sql from MS Access:

SELECT DISTINCT qryInventory.StudyID,
qryInventory.FamMbr,
tblbox.BoxDesc
FROM qryInventory
INNER JOIN tblbox
ON qryInventory.BoxID = tblbox.ACHBarcode
WHERE (((qryInventory.BoxID) IN (45500,45555,45600,45499)))
ORDER BY tblbox.BoxDesc,
qryInventory.Row,
qryInventory.Column;

1. Ticking the "Remove unnecessary brackets" checkbox removes only the outer set of brackets on the WHERE clause even though there are other "unnecessary brackets" and it also (unnecessarily) removes the space after WHERE:

SELECT DISTINCT qryInventory.StudyID,
qryInventory.FamMbr,
tblbox.BoxDesc
FROM qryInventory
INNER JOIN tblbox
ON qryInventory.BoxID = tblbox.ACHBarcode
WHERE((qryInventory.BoxID) IN (45500,45555,45600,45499))
ORDER BY tblbox.BoxDesc,
qryInventory.Row,
qryInventory.Column;


MS Access adds a lot of "unnecessary brackets" in compound WHERE clauses. It would be nice if the tool could convert to the following (i.e. remove the additional unnecessary brackets in the WHERE clause):

SELECT DISTINCT qryInventory.StudyID,
qryInventory.FamMbr,
tblbox.BoxDesc
FROM qryInventory
INNER JOIN tblbox
ON qryInventory.BoxID = tblbox.ACHBarcode
WHERE qryInventory.BoxID IN (45500,45555,45600,45499)
ORDER BY tblbox.BoxDesc,
qryInventory.Row,
qryInventory.Column;


2. Setting output to VB String(2) results in the following:

strSQL= "" & _
"SELECT DISTINCT qryInventory.StudyID, " & _
"qryInventory.FamMbr, " & _
"tblbox.BoxDesc " & _
"FROM qryInventory " & _
"INNER JOIN tblbox " & _
"ON qryInventory.BoxID = tblbox.ACHBarcode " & _
"WHERE (((qryInventory.BoxID) IN (45500,45555,45600,45499))) " & _
"ORDER BY tblbox.BoxDesc, " & _
"qryInventory.Row, " & _
"qryInventory.Column;"

It would be nice if one could get rid of the null string on the first line, i.e.

strSQL= "SELECT DISTINCT qryInventory.StudyID, " & _
"qryInventory.FamMbr, " & _
"tblbox.BoxDesc " & _
"FROM qryInventory " & _
"INNER JOIN tblbox " & _
"ON qryInventory.BoxID = tblbox.ACHBarcode " & _
"WHERE (((qryInventory.BoxID) IN (45500,45555,45600,45499))) " & _
"ORDER BY tblbox.BoxDesc, " & _
"qryInventory.Row, " & _
"qryInventory.Column;"

All of the VB String() formats result in a null string on the first line.



Edited 1 time(s). Last edit at 06/13/2016 11:18PM by guidomarcel.
Re: Two issues
September 28, 2014 09:50PM
Hi bbhulsey,
thanks for your feedback.

For point 1 please find a new release here: New Release.

It removes more brackets as the current release but still keeps the brackets for (qryInventory.BoxID) as the parser cannot find out if the brackets are unnecessary.

For point 2: the first line with the null string has been added to insure that the SELECT statement starts on a fresh new line and has a nice indention with the following lines.

Regards
Guido
Re: Two issues
September 29, 2014 11:12PM
Thank you Guido. Can you provide specific instructions on how to perform the update? I've downloaded the file but double-clicking it doesn't seem to do anything. Thanks!

-Bruce
Re: Two issues
September 29, 2014 11:40PM
sorry, my fault. please copy this one into your SQLinForm folder. SQLinForm Beta Release
Re: Two issues
October 01, 2014 12:06AM
Hello Guido,

Definitely an improvement and a help, thanks! Note that it still removes the space after WHERE though.

SELECT DISTINCT qryInventory.StudyID,
qryInventory.FamMbr,
tblbox.BoxDesc
FROM qryInventory
INNER JOIN tblbox
ON qryInventory.BoxID = tblbox.ACHBarcode
WHERE(qryInventory.BoxID) IN (45500,45555,45600,45499)
ORDER BY tblbox.BoxDesc,
qryInventory.Row,
qryInventory.Column;

-Bruce
Re: Two issues
October 01, 2014 01:09PM
indeed, you are 100% correct.
blanks are now being kept.

SQLinFormpro_Desktop_2014OOctoberBeta2.jar

Regards
Guido
Author:

Your Email (Optional):


Subject:


Spam prevention:
Please, solve the mathematical question and enter the answer in the input field below. This is for blocking bots that try to post this form automatically.
Question: how much is 24 plus 6?
Message: