|
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.
|
|
|
|
|
So summing up the other posts and from the OP.
Basically it appears that your database itself has invalid data.
How could that happen? Something pushed it in there before it was validated.
Or perhaps there is some problem with timezones and/or an actual bug in the database itself.
You might be able to fix a timezone problem but otherwise you would need to update the row to put a correct value in there. If it was me I would expect that there is other invalid data in that table and perhaps other tables with timestamps also. You would need to uniquely identify each row then craft an update statement to force that column to a correct (manually determined) value.
|
|
|
|
|
I got this off SO, wrt "Code: 1292 SQL State: 22007":
"Change the system timezone to the one that does not use DST ..."
Who knows why, right? When everything you always wanted to know about anything was everywhere. Exactly as it is, I mean.
|
|
|
|
|
Is it possible to insert default data in a table that was created by Entity Framework?
Here's my DBContext
public class SqlDataContext : DbContext
{
private string _connectionString = "";
public DbSet<UserEntity> Users { get; set; }
public SqlDataContext()
{
_connectionString = @"Server=MAROIS_KEVIN_1\SQLEXPRESS;Database=Test1;Trusted_Connection=true;Encrypt=false;";
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(_connectionString, options => options.EnableRetryOnFailure());
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<UserEntity>(entity =>
{
entity.ToTable("Users", "dbo");
entity.HasKey(e => e.UserId)
.HasName("PrimaryKey_UserId");
});
}
}
I would like to insert a default user into the User's table each time I create the database.
Thanks
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
|
Awesome, thanks!
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
hi,
i have a problem while executing updates using the update script inside the variable, it gives the error "String or binary data would be truncated";
I realized that if reduce the number of fields to update, it no longer gives the error!
But I didn't want to divide the execution of the update into 2 variables!!! Can you help?
Thanks!
|
|
|
|
|
|
How is the @Sql1 variable defined ?
Ex: Declare @Sql1 Varchar(200)
|
|
|
|
|
After thinking about this a bit more than required;
Member 11533892 wrote: But I didn't want to divide the execution of the update into 2 variables!!! You mean you don't want to execute two update-statements for the same record. Two variables cost nothing nowadays, so that doesn't make any sense. Two updates must be atomic, or you get concurrency issues. Could be simply solved by using a transaction.
You may find the error to be the design of the table, not the update-statement. I'm going to guess a lot of those fields can be "NULL" or revert to some preset default value.
..but, let me give you a solution from a VB6 programmer view; you can rename all columns into numbers. So if column one is named [Name], you replace that with [1]. You can keep a list in Excel to look up the column names. That way, the update-statement is shorted a lot, and a huge lots of them would fit in a VARCHAR(MAX) command.
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.
|
|
|
|
|
I Have 3 Tables Orders, OrderItems, OrderPymts. For this query, I only need records from the Orders and OrderPymts Tables.
The Order Table Contains Fields: OrderId, ClientId, SubTotal, Tax, OrderTotal
The OrderPymts Table Contains Fields: PaymentId, OrderId, PaymentDate, Terms, PrevBal, Payment, Balance.
I'm trying to query records only on the Last PaymentDate for each Order (To show the Outstanding Balance of Each Order).
I tried This:
SELECT Orders.OrderId, Orders.ClientId, Orders.OrderTotal, OrderPymts.Terms, (SELECT MAX(OrderPymts.PaymentDate)) AS PaymentDate, OrderPymts.PrevBal, OrderPymts.Payment, OrderPymts.Balance
FROM OrderPymts
JOIN Orders ON OrderPymts.OrderId = Orders.OrderId
WHERE (Balance >= 0.01)
ORDER BY Orders.OrderId, PaymentDate
To just get the last payment but it still returns all the payments.
Example Result of My query:
OrderId |ClientId |OrderTotal|Terms |PaymentDate|PrevBal|Payment|Balance|
1001 | 1 | 500 |Credit| 1/1/2023 | 500 | 100 | 400 |
1002 | 2 | 800 |Cash | 1/9/2023 | 800 | 200 | 600 |
1002 | 2 | 800 |Cash | 1/11/2023 | 600 | 100 | 500 |
Above is an example of the data my query is showing. I only want to get the bolded records in the query. But my code above is pulling all the records with a balance greater than 0.01. As you can see, I tried getting the Max date but that didn't work. I also tried Group By but I get the same results as above. I need someone smarter than me, to help me with this.
Thanks for your help.
modified 20-Jan-23 11:34am.
|
|
|
|
|
Assuming Microsoft SQL Server, ROW_NUMBER is your friend.
WITH ctePayments As
(
SELECT
OrderId,
PaymentDate,
Terms,
PrevBal,
Payment,
Balance,
ROW_NUMBER() OVER (PARTITION BY OrderId ORDER BY PaymentDate DESC) As RN
FROM
OrderPymts
)
SELECT
O.OrderId,
O.ClientId,
O.OrderTotal,
P.Terms,
P.PaymentDate,
P.PrevBal,
P.Payment,
P.Balance
FROM
Orders As O
INNER JOIN ctePayments As P
ON P.OrderId = O.OrderId And P.RN = 1
WHERE
P.Balance >= 0.01
ORDER BY
O.OrderId
; ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Learn[^]
Alternatively, you could use a correlated sub-query:
SELECT
O.OrderId,
O.ClientId,
O.OrderTotal,
P.Terms,
P.PaymentDate,
P.PrevBal,
P.Payment,
P.Balance
FROM
Orders As O
CROSS JOIN
(
SELECT TOP 1 Terms, PaymentDate, PrevBal, Payment, Balance
FROM OrderPymts
WHERE OrderPymts.OrderId = O.OrderId
ORDER BY PaymentDate DESC
) As P
WHERE
P.Balance >= 0.01
ORDER BY
O.OrderId
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank You Richard. I'm coming From VB.Net learning C#, and SQL, Didn't think about a cross Join.
modified 20-Jan-23 18:43pm.
|
|
|
|
|
On a different note - it is probably not a good idea to link payments to an order, or at least make it a secondary connection. A client should have an account, orders place a debit on the account and payments credit the account. You may then want to link 1 or more payments to an order or 1 or more orders to a payment.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I needed it to show the last payment made on an invoice. But thanks for your advice.
|
|
|
|
|
I have an entity table which is begin queried into an entity:
UserEntity? user = dc.Users
.Where(x => x.UserName.CompareTo(entity.UserName) == 0)
.FirstOrDefault();
Now, if I add a property to the entity that does NOT exist in the underlying table, it throws an exception:
Microsoft.Data.SqlClient.SqlException: 'Invalid column name 'FullName'.'
FullName if not a table field. How can I prevent this exception for properties that don't exist in the data?
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
If I remember correctly, you add the [NotMapped] attribute to the property in your model class.
public class User
{
public string FirstName { get; set; }
public string LastName { get; set; }
[NotMapped]
public string FullName { get; set; }
}
You could easily find this yourself just by Googling for "Entity Framework ignore property".
|
|
|
|
|
Thanks.
I spent time on Google looking around, but I wasn't really sure what I was looking for.
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|