Invalid SQL generation, ORA-00907: missing right parenthesis
Posted: Wed 03 Feb 2021 10:34
We are using version 9.14.1180 with the changes suggested here: viewtopic.php?f=1&t=44230#p178438
The selector code we had was inadvertently modified, and lead to the ORA-00907 exception.
This does not happen with MSSQL and PostgreSQL, so we believe this is a bug with dotConnect.
The original code, which works fine:
The code that does not work with dotConnect, but does work ok with other providers.
Here are the respective SQL code.
Works:
ORA-00907:
We have reverted the changes in our code, which were not required, but I thought it important to let you know of a potential issue.
Code: Select all
public class Client
{
public User User { get; private set; }
public int UserId { get; private set; }
public int ClientId { get; private set; }
// ... Other fields...
}
public class User
{
private readonly List<Client> _clientsList = new List<Client>();
public IReadOnlyCollection<Client> ClientsList => _clientsList;
public int UserId { get; private set; }
public string IsUserActive { get; private set; }
// ... Other fields...
}
This does not happen with MSSQL and PostgreSQL, so we believe this is a bug with dotConnect.
The original code, which works fine:
Code: Select all
var clients = dbContext.Clients.Select(client => new ClientDto()
{
Id = client.Id,
// ... Other fields...
IsUserActive = client.User.IsActive.ToUpper() == BoolUtils.Y,
UserName = client.User == null ? string.Empty : StringUtils.Sanitize(client.User.Name),
});
Code: Select all
var clients = dbContext.Clients.Select(client => new ClientDto()
{
Id = client.Id,
// ... Other fields...
IsUserActive = client.User == null ? false : client.User.IsActive.ToUpper() == BoolUtils.Y,
UserName = client.User == null ? string.Empty : StringUtils.Sanitize(client.User.Name),
});
Works:
Code: Select all
SELECT "c".CLIENT_ID, (CASE
WHEN (UPPER("u".US_IS_ACTIVE) = 'Y') AND UPPER("u".US_IS_ACTIVE) IS NOT NULL THEN 1
ELSE 0
END), "u".USER_NAME
FROM CLIENTS "c"
INNER JOIN USERS "u" ON "c".USER_ID = "u".USER_ID
Code: Select all
SELECT "c".CLIENT_ID, (CASE
WHEN 0 = 1 THEN 0
ELSE (UPPER("u".US_IS_ACTIVE) = 'Y') AND UPPER("u".US_IS_ACTIVE) IS NOT NULL
END), "u".USER_NAME
FROM CLIENTS "c"
INNER JOIN USERS "u" ON "c".USER_ID = "u".USER_ID