|
Apologies for the shouting but this is important.
When answering a question please:
- Read the question carefully
- Understand that English isn't everyone's first language so be lenient of bad spelling and grammar
- If a question is poorly phrased then either ask for clarification, ignore it, or mark it down. Insults are not welcome
- If the question is inappropriate then click the 'vote to remove message' button
Insults, slap-downs and sarcasm aren't welcome. Let's work to help developers, not make them feel stupid.
cheers,
Chris Maunder
The Code Project Co-founder
Microsoft C++ MVP
|
|
|
|
|
For those new to message boards please try to follow a few simple rules when posting your question.- Choose the correct forum for your message. Posting a VB.NET question in the C++ forum will end in tears.
- Be specific! Don't ask "can someone send me the code to create an application that does 'X'. Pinpoint exactly what it is you need help with.
- Keep the subject line brief, but descriptive. eg "File Serialization problem"
- Keep the question as brief as possible. If you have to include code, include the smallest snippet of code you can.
- Be careful when including code that you haven't made a typo. Typing mistakes can become the focal point instead of the actual question you asked.
- Do not remove or empty a message if others have replied. Keep the thread intact and available for others to search and read. If your problem was answered then edit your message and add "[Solved]" to the subject line of the original post, and cast an approval vote to the one or several answers that really helped you.
- If you are posting source code with your question, place it inside <pre></pre> tags. We advise you also check the "Encode "<" (and other HTML) characters when pasting" checkbox before pasting anything inside the PRE block, and make sure "Use HTML in this post" check box is checked.
- Be courteous and DON'T SHOUT. Everyone here helps because they enjoy helping others, not because it's their job.
- Please do not post links to your question into an unrelated forum such as the lounge. It will be deleted. Likewise, do not post the same question in more than one forum.
- Do not be abusive, offensive, inappropriate or harass anyone on the boards. Doing so will get you kicked off and banned. Play nice.
- If you have a school or university assignment, assume that your teacher or lecturer is also reading these forums.
- No advertising or soliciting.
- We reserve the right to move your posts to a more appropriate forum or to delete anything deemed inappropriate or illegal.
cheers,
Chris Maunder
The Code Project Co-founder
Microsoft C++ MVP
|
|
|
|
|
This worked fine until I got to version 11, where the version numbers sorted like this - vers_id
0, 1, 10, 2, 3, 4, 5, 6, 7, 8, 9, I was expecting 0, 1, 2, 3, 4, ....
I'm thinking that I need another sort clause but sure how to integrate it into my case
But then I could be wrong, and my case statements may just need to be better.
Everything I tried failed. Looking for help on this to keep my customers project clean.
SELECT
Proj_Stage,
vers_id,
vers_note,
markup_price,
sell_price,
CONVERT(CHAR(19), sell_date, 120)<br />
FROM proj_vers
WHERE proj_id = '$projectNumber'
ORDER BY CASE
WHEN CONVERT(VARCHAR, Proj_Stage) = 'designing' THEN 0
WHEN CONVERT(VARCHAR, Proj_Stage) = 'engineering' THEN 1
WHEN CONVERT(VARCHAR, Proj_Stage) = 'construction' THEN 2
WHEN CONVERT(VARCHAR, Proj_Stage) = 'finished' THEN 3<br />
END
I tried this
END, vers_id
And I tried this
WHEN CONVERT(VARCHAR, Proj_Stage) = 'construction' THEN 2, vers_id DESC
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Just a stab in the dark, but you can Cast/Convert vers_id to a int. Cast(vers_id as int) in the order by should do what you want. Assuming vers_id is will always convert to an int.
varchars will be sorted in the way you are experiencing.
Jack of all trades, master of none, though often times better than master of one.
|
|
|
|
|
Great idea!
I just checked the table and vers_id was set to char.
Just crafted this and it does what I want now. I didn't think of that, where it sorted as chars and not numbers.
Good eye, or stab in the dark, very helpful and spot on.
SELECT
Proj_Stage,
CAST(vers_id AS int),
vers_note,
markup_price,
sell_price,
CONVERT(CHAR(19), sell_date, 120)<br />
FROM proj_vers
WHERE proj_id = '$projectNumber'
ORDER BY CASE
WHEN CONVERT(VARCHAR, Proj_Stage) = 'designing' THEN 0
WHEN CONVERT(VARCHAR, Proj_Stage) = 'engineering' THEN 1
WHEN CONVERT(VARCHAR, Proj_Stage) = 'construction' THEN 2
WHEN CONVERT(VARCHAR, Proj_Stage) = 'finished' THEN 3<br />
END, CAST(vers_id AS int) ASC
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Off topic - I would assume that "Proj_Stage" would be in another table and should have a sequence no in that table, this would eliminate the case statement in the where clause.
If it is input text then you have another problem altogether!
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I'm stuck with the existing database to make it backwards capable, with older construction projects. Every record has a ...
project number
project stage
version number
This table keeps track of the versions of the construction project. As typical, customers that have construction done always change their mind during the construction process, and don't understand once you pull a permit from the city, you can't change the project. But they change it anyways and then you have to get a new permit.
I didn't get any documentation, nor useful comments and had to reverse engineer the old version to make this new version of the software. And it was very broken and needed fixing, thus below ...
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Even if you cannot change the existing tables in the database, you should be able to create new entities.
I'd still create another table with 2 columns in it as suggested by @Mycroft Holmes[^] as suggested above[^] (cols: Proj_Stage and SortSequence), indexed by Proj_Stage. Then
LEFT JOIN ProjStageSortSequence AS ps ON proj_vers.Proj_Stage = ps.Proj_Stage and change the ORDER BY to be
ORDER BY ps.SortSequence, vers_id . You would also need to change Proj_Stage in the SELECT to proj_vers.Proj_Stage. These changes will save loads of CONVERT() operations and should (not tested) run faster as SQL SERVER is very good at optimizing joins with low nos of rows.
This leaves the existing table unaltered and would be more efficient that the query it already has. It would not be as efficient or as normalised as using the sort sequence in the existing table; but it is a stepwise improvement.
|
|
|
|
|
Took a few minutes for me to absorb, but I get it now, and that's an enhancement that would be backwards compatible. I would be able to apply this new table to every other table that uses the project stages, which is pretty much about 15 more tables.
I'll dabble with this tonight at home in my new home office. Thanks!
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Your Awesome! 
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Your welcome. Been there done that as they say.
Jack of all trades, master of none, though often times better than master of one.
|
|
|
|
|
Presumably no one is ever going to use version number 1.1 or even 1.0
|
|
|
|
|
Just whole integers, maybe up to 20 if the customer gets real bad, or excited about their swimming pool while it's being built.
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
I'm retired but when I was working I had a major problem. I had an app that users could query tables. The app form contained many text boxes which could be filled with values if they wanted to see the result. If the user typed in a box I'd concat a parameterized condition to the final SQL statement where clause including that column. Then we were hit with new coding standards. I could no longer concat SQL and had to use stored procedures. No exceptions.
What I decided to do was create a stored procedure for every possible combination of search boxes on the form. The only impact and drawback that I saw to this method was having to code and maintain many stored procedures.
This was quite a few years ago. At the time I tried searching the www for some kind of SQL solution, but I couldn't describe what I was looking for into a www search, it was too complicated, too many words. At the time I didn't ask the question on this forum.
Basically I was looking for a way in a stored procedure not to include a particular column in a where clause. It couldn't test the actual parameter variable for null because the app form allowed the user to specifically request that they wanted to see results if the column were null in any of the rows as well.
I didn't want the stored procedure to be a giant mess of if statements and multiple SQL statements with varying where clauses because that wouldn't very efficient.
What I thought I wanted was a new kind of SQL operator that could be used to specify that the column should only be included in the final query plan if a smart parameter property were triggered from the app that it should be acted upon.
something like this
select * from people
where if @fNameActive then fname like @fNameParam
and if @dobActive then dob = @dobParam
Otherwise name and dob should not be evaluated.
I think the proper solution would be to allow exceptions to the mandatory stored procedure rule. I think SQL was designed to allow programmers to build the where clause as needed in this specific case.
Allowing for parameterized concatenation would enable even more powerful searches where the user can specify less than or greater than as well.
Our rules makers meant well. They were trying to close the loop on web sites that concatenated login credentials.
So was there ever a feature added to SQL that allowed us to fiddle with a where clause in a stored procedure in this manner?
I can't be the only developer to run up against this wall. But it's so complicated that it's difficult to formulate an effective www search.
Could I have coded this? Just saw something similar on Stack Overflow.
SELECT * FROM people
WHERE
( @fNameActive = 1 AND fname like @fNameParam )
AND
( @dobActive = 1 AND dob = @dobParam)
It looks to me like the only way to get it to work is by omitting the column. I actually tried this on MySQL and it failed. If one of the () were false because of my simulated "activate test" I just coded 1=0 it returned no rows.
How about...
SELECT * FROM people
WHERE
( @nameactive = 1 AND name like @nameparam )
OR
( @dobActive = 1 AND dob = @dobParam)
Well it seemed to work but failed to narrow down to a specific row. Hypothetically if fnameparam were george and dobparam was 2010/2/30 it would return all georges and all people who were born on feb 30th, so no, it should return no rows if there are no georges born on feb 30th. Yes, by George I am joking about feb 30th.
I ask this question because I'm curious. I have no problem using parameterized concats on my home projects.
|
|
|
|
|
Was there ever a feature added to support what you were thinking of doing? No.
What you do in a case like this is either write your own syntax parser and query language to break down what is typed into a search box, or boxes, and build the query yourself, or use a dedicated indexing and search engine, like Lucene, to do it for you.
In my latest web app, I went with the home-built route. I have a single search box where you type your query. It understands a specific, home brewed, query language. That query statement gets tokenized and broken down into an "abstract syntax tree", or AST. That tree is then sent to another module that walks the tree, interprets it, and builds an SQL SELECT WHERE clause from it, complete with parenthesis, AND/OR operators. One important part you have to remember is building proper indexes on the database to support the queries to make them more performant.
|
|
|
|
|
Brian L Hughes wrote: Our rules makers meant well. They were trying to close the loop on web sites that concatenated login credentials.
That sounds like the EU. We are having a tiny problem with X, let's ban EVERYTHING that resembles it. You can make those rules, but I will not ever bend software to fit some idiot idea.
You query stale data from a snapshot, from a readonly DB, as is best practice, using a locked down account. WTF is going "Stored Procedures" going to do in terms of added safety??
Brian L Hughes wrote: It looks to me like the only way to get it to work is by omitting the column.
In SQL Server it's called "free text search".
Bastard Programmer from Hell 
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Brian L Hughes wrote: I had an app that users could query tables. The app form contained many text boxes
That is pretty indefinite.
Especially as it says 'tables' plural.
Also it is not clear how the users build there statements. For example is it only 'and' or does it allow 'or' clauses also.
-------------------------------------------------------
There are in fact many tables and each table only has a few columns. Also there are no joins.
But 'many' actually means not all that many. So perhaps 10 tables and 3 columns each.
Then if it was me I would generate (not manually code) the variations to produce the procs. This would happen during the build not at run time. There would be a process to determine whether changes were made so that it would not require updating the procs every release. For example there would be a separate build that only runs when something is known to have changed. Such as adding a new table.
-------------------------------------------------------
But lets presume you had one table with a lot of columns.
First the UI should limit the number of clauses that can be created. That should be true regardless of any other solution. Nothing in a computer should be unlimited and in a case like this at some point one reaches diminishing returns.
Solutions.
1. Any process should have a process for exceptions. If it does not then the process itself is flawed (I spent 15 years in process control groups, principal process author and sometimes sole process controller along with 5 years in security process.) So either use the exception process or insist that one is added. Process control exceptions should of course be documented. Probably more so than any other process control step. So that would need to be done also.
2. Pass an array to the proc. The proc builds dynamic SQL and then executes it. There will be a limit to the size of the array. This of course is just a variation of your solution but moving where the code executes.
3. Create the procs dynamically. I would go ballistic on this solution if there was more than about 30 procs (per table). Even with generation at some point this becomes a maintenance problem.
|
|
|
|
|
Brian L Hughes wrote: SELECT * FROM people
WHERE
( @nameactive = 1 AND name like @nameparam )
OR
( @dobActive = 1 AND dob = @dobParam)
Well it seemed to work but failed to narrow down to a specific row. Hypothetically if fnameparam were george and dobparam was 2010/2/30 it would return all georges and all people who were born on feb 30th, so no, it should return no rows if there are no georges born on feb 30th. Yes, by George I am joking about feb 30th.
So, you want to only have Georges born on the 30th Feb? If so, why no use De Morgan's laws - Wikipedia[^] and reverse your tests e.g.
SELECT * FROM people
...
WHERE (@nameactive = 0 OR name like @nameparam)
AND (@dobactive = 0 OR dob = @dobparam)
This should just treat unwanted criteria as true and just AND wanted criteria.
|
|
|
|
|
Where I either don't want this column included or the column matches param, then AND them across the where clause and presto!
I tested it on mysql and it worked!
It's kind of funny that I couldn't come up with a solution for this after years of sql coding. I will admit that sometimes I can't figure out multiple combinations of AND and OR tags in code.
Is the sql engine is smart enough not to include any actual column testing at runtime if the "include this column" param is 0?
|
|
|
|
|
The phrase you are looking for is 'short circuiting'.
https://stackoverflow.com/questions/789231/is-the-sql-where-clause-short-circuit-evaluated[^] quotes an SQL standard that says
ANSI SQL Draft 2003 5WD-01-Framework-2003-09.pdf
6.3.3.3 Rule evaluation order
[...].
Where the precedence is not determined by the Formats or by parentheses, effective evaluation of expressions is generally performed from left to right. However, it is implementation-dependent whether expressions are actually evaluated left to right, particularly when operands or operators might cause conditions to be raised or if the results of the expressions can be determined without completely evaluating all parts of the expression.
I have not found any specific answer for MySql but IIRC MySql gives you a choice of engines so it might depend on the engine.
The answer for MS SQL SERVER (according to Understanding T-SQL Expression Short-Circuiting – SQLServerCentral[^]) is that short circuiting does happen (but that is specific to that one environment).
It is easy to test. If you have a WHERE clause like
WHERE NULL = NULL OR 1/0 = 1 then it will deliver TRUE if short circuiting is implemented and throw an error (trying to divide by zero) if not implemented.
|
|
|
|
|
jsc42 wrote: I have not found any specific answer for MySql
Interesting. I couldn't either.
I know it happens in C++ so I attempted to find that expression ("short circuit") in the standard and as far as I can tell it does not exist. I used the actual book to look it up. So for that (C++) it is expressed like the following
"The operators && and || will not evaluate their second argument unless doing so is necessary"
For C it is expressed as the following
"Expressions connected by && or || are evaluated left to right, and evaluation stops as soon as the truth of falsehood of the result is known"
Then I looked for that terminology in MySQL docs (8) and found nothing on the page that defines AND and OR that would suggest that.
I also attempted the same for PL/SQL and also found nothing.
|
|
|
|
|
Recently, I migrated from version 5.7.40-0 to version 8.0.31 of MySQL, but I faced the following error message on the columns that are time.
Error :
Code: 1292 SQL State: 22007 --- Incorrect datetime value: '2023-06-31 23:59:00' for column 'date' at row 1
MySQL Query :
SELECT
DATE(`Date`) AS `Date`,
COUNT(`Date`) AS `Record count`
FROM tbl_Pw
WHERE `Date` BETWEEN '2023-01-01 00:00:00' AND '2023-03-31 23:59:00'
GROUP BY DATE(`Date`)
ORDER BY `Date`;
How can I fix the problem?
|
|
|
|
|
|
thanks for your quick answer
date is field name not data type and its data type is DATETIME
SELECT
DATE(`MyDateColumn`) AS `Date`,
COUNT(`MyDateColumn`) AS `Record count`
FROM tbl_Pw
WHERE `MyDateColumn` BETWEEN '2023-01-01 00:00:00' AND '2023-03-31 23:59:00'
GROUP BY DATE(`MyDateColumn`)
ORDER BY `MyDateColumn`;
|
|
|
|
|
My apologies, I misread the question. The actual problem is as shown in the error message:
Incorrect datetime value: '2023-06-31 23:59:00'
The month of June (06) contains only 30 days, not 31. You will need to manually correct that field.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
:text=MySQL%20retrieves%20and%20displays%20DATE,%3Amm%3Ass%20'%20format.