Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

SQL Syntax Error when using Group by conditional expression

K
kalena last edited by
I'm trying to run SQL procedure with group by conditional expression
```
ORDER BY 
    CASE
        (WHEN tnx_order = TRUE THEN `txn`.id ASC) END,
        (WHEN tnx_order = FALSE THEN `txn`.id DESC) END
    LIMIT 1;
```
And i get this type of error:

SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHEN tnx_order = TRUE THEN `txn`.id ASC) END,
Reply Quote 0
1 Reply Last reply
C
chanisef last edited by
Where is tnx_order coming from? You have additional parenthesise and you can’t return asc or desc within the case statement.

You might want
```
ORDER BY CASE WHEN tnx_order THEN txn.id ELSE NULL END ASC
, CASE WHEN !tnx_order THEN txn.id ElSE NULL END DESC
LIMIT 1;
```
If tnx_order is a variable then you’d probably be better off having two different queries with the alternate order by clauses and have your application decide which to run. This would better take advantage of indexes to provide no sort plans.
Reply Quote 0
1 Reply Last reply

mysql 49766

2
Posts

0
Views

Suggested Topics

Pagement with NodeJS with database MySQL
Software Programming • javascript mysql node.js react page • • morde

2

0
Votes

2
Posts

0
Views

T

Hey, what's up?If I understand what you want, it is to create a way when the user accesses the http://localhost:3010/usuarios direct the page automatically to page 1. Right? Conception:For this, we will need to uncouplate your routes and use a very nice standard known as middleware (i.e., it is a program that runs in the middle of something offering aid in some specific task, such as the processing of inputs for example).Our middleware In this case, it will be built to treat url before the user listing is accessed. Hand in code:Well, I'm going to set up an express service from this zero and you take advantage of the parts you add to your case, okay?Creating index.js, server.js or app.js// Porta do serviço. const port = 4000; // Importação do módulo express. const express = require('express'); // Criação da instância do serviço. const app = express(); // Importação das nossa rotas desacopladas (falaremos delas mais tarde). const routes = require('./routes'); // Aqui podemos ver a primeira forma de um middleware. // Com o método "use" eu digo ao serviço que ele deve executar as rotas importadas // somente se houver um prefixo "/usuarios" na url. app.use('/usuarios', routes); // Inicialização do serviço. app.listen(port, () => console.log(Running on ${port}...)); Creating routes.jsconst express = require('express'); // Criação de uma instância de roteador (router) do express. const router = express.Router(); // Aqui temos mais um middleware, e esse é a estrela do nosso negócio. // É ele quem vai verificar a url e tratar para depois chamar nossa rota de paginação. router.use((req, res, next) => { // Se a url for igual a uma "/" pura, reset ela para "/1". if (req.url === '/') { req.url = '/1'; } // Chamo o nosso next, ele quem vai fazer a próxima rota na fila ser executada. next(); }); // E finalmente nossa rota de listagem com toda lógica complexa. router.get('/:page', (req, res) => { const page = req.params.page; res.send(Página = ${page}); }); // Exportação do router para que possamos usar ele no index.js module.exports = router;
Error when using WITH (DROP_EXISTING = ON)
SQL, Database Testing • sql server sql server 2014 index maintenanc language • • Alberto

2

0
Votes

2
Posts

0
Views

F

This can happen when the current primary key is nonclustered, and there is also a clustered index: CREATE TABLE dbo.MyTable ( MyTableID integer NOT NULL, RowStatus tinyint NOT NULL, SomeOtherKey integer, CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED (MyTableID, RowStatus), CONSTRAINT [CUQ_MyTable] UNIQUE CLUSTERED (SomeOtherKey) ); CREATE Unique CLUSTERED INDEX PK_MyTable ON dbo.MyTable (MyTableID, RowStatus) WITH (DROP_EXISTING = ON); Msg 1902, Level 16, State 3, Line 14 Cannot create more than one clustered index on table 'dbo.MyTable'. Drop the existing clustered index 'CUQ_MyTable' before creating another. It is valid to change a primary key from nonclustered to clustered using DROP_EXISTING syntax: CREATE TABLE dbo.MyTable ( MyTableID integer NOT NULL, RowStatus tinyint NOT NULL, SomeOtherKey integer, CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED (MyTableID, RowStatus), ); CREATE Unique CLUSTERED INDEX PK_MyTable ON dbo.MyTable (MyTableID, RowStatus) WITH (DROP_EXISTING = ON);
G

Union of two mysql consultations
Software Programming • mysql sql • • gionna

2

0
Votes

2
Posts

0
Views

P

The second consultation should be sufficient, but the LEFT JOIN condition is wrong: LEFT JOIN pr_funds on pr_funds.fund = pr_employees.afp_id = pr_employees.afp_id You must correct it because you are trying to compare, on the one hand, the same field (repeated code). On the other hand, surely return NULL because there is no relation between the field fund (a string with the name) and the field afp_id (an integer).I understand most likely there's a id on the board. pr_funds that can match the field afp_id of the table pr_employees. Kind of:LEFT JOIN pr_funds ON pr_funds.afp_id = pr_employees.afp_id In any case, the problem is in that part of the code: Check the relationship between the tables and fix the repeated comparison (pr_employees.afp_id = pr_employees.afp_id).
Using TDE and SQL Server 2016 AlwaysOn Availability Groups
SQL, Database Testing • sql server sql server 2016 availability groups transparent data enc lang • • Laycee

2

0
Votes

2
Posts

0
Views

B

From your experience, is it good idea to use TDE in our situation? No idea, you didn't tell us the attack vectors you are looking to secure. In the rest of my answers, I'll assume the only attack vector is on disk at rest. Is it worth? If you're already running Enterprise Edition (which is required) then there is no direct extra cost. There will obviously admin cost for it in terms of your working hours. How well TDE compatible with AlwaysOn AGs? It's 100% compatible. We use EMC SAN for storage and it provides the data encryption at rest on hardware level. What happens when the backups, data files, etc., are moved off the SAN? What about backups in transit over network? I read, for example, you can't use AG GUI to do anything. In older versions of SSMS, that's correct. This has been fixed for a while. Everything has to be done through scripts. I don't see why that's a bad thing, though.
C

Data coding in phpmyadmin
Software Programming • php mysql recoding • • chanisef

2

0
Votes

2
Posts

0
Views

G

It's like, at the beginning of the violin... mysql_query("SET NAMES 'ваша кодировка в бд'");In the muscle, go into the braces and there'll be a change of coding.
db query error: pq: column "systemevents.receivedat" must appear in the GROUP BY clause or be used in an aggregate function
SQL, Database Testing • postgresql group by postgresql 10 • • morde

2

0
Votes

2
Posts

1
Views

S

When using "group by", then every column you select must either be used in an aggregate function or included in the "group by" clause. Consider this query: select a, b, c from ... group by a, b Chances are, there will be multiple values of c for each combination of a and b. Which one should your query return? The database doesn't know either - and so throws this error.
Logging the conditions within a conditional split transformation (SSIS in SQL Server 2012 and 2016)
SQL, Database Testing • sql server ssis ssis expressions • • Marcee

2

0
Votes

2
Posts

0
Views

S

If you use variables or parameters in the Conditional Split, you can log the values of these. There are several ways to do this, either insert the values into a custom logging table or use a Script Task to capture these. The following example uses the Dts.Events.FireInformation method from a C# Script Task. Under the Basic logging mode, this will be logged in the SYSSSISLOG table, assuming that you've chosen this as a log provider. string taskName = Dts.Variables["System::TaskName"].Value.ToString(); string varName = Dts.Variables["User::YourVariableName"].Value.ToString(); int infoCode = 99; bool fireAgain = true; string loggedMsg = "Value of Conditional Split Variable: " + varName; Dts.Events.FireInformation(infoCode, taskName, loggedMsg, "", 0, ref fireAgain);
SQL Agent - PowerShell step "syntax error"
SQL, Database Testing • sql server 2012 powershell windows server • • Analeea

2

0
Votes

2
Posts

0
Views

C

This is a not very intuitive and I was never able to find anything concrete on the explanation [e.g. no exact BOL or white paper was found]. The syntax error in the SQL Agent job is a T-SQL syntax error based on User Tokens. So that basically means that a PowerShell Sub-Expression Operator is treated as a Token to SQL Server Agent. So in PowerShell this $( ) appears to be treated as reserved character sequence for SQL Server Agent. So SQL Agent would be looking for something like this T-SQL example from the BOL article referenced: PRINT N'Current database name is $(A-DBN)' ; . So in my script when it reached ,@DriveLetter = '$($c.DriveLetter)', the "$c.DriveLetter" is not one of the tokens allowed. The workaround to this is simply to not use sub-expression statements in PowerShell. I would make the adjustments in my script so this: $qAddServerDiskSpace = @" EXEC [dbo].[StoredProcInsert] @ServerName = '$($c.ServerName)' ,@DriveLetter = '$($c.DriveLetter)' ,@DiskSpaceCapacityGB = $($c.DiskSpaceCapacityGB) ,@DiskFreeSpaceGB = $($c.DiskFreeSpaceGB) "@ would have to be modified to something like this: $severName = $c.ServerName $driveLetter = $c.DriveLetter $capacityGB = $c.DiskSpaceCapacityGB $freeGB = $c.DiskFreeSpaceGB $qAddServerDiskSpace = @" EXEC [dbo].[StoredProcInsert] @ServerName = '$serverName' ,@DriveLetter = '$driveLetter' ,@DiskSpaceCapacityGB = $CapacityGB ,@DiskFreeSpaceGB = $freeGB "@ Made the above adjustments to my script and it runs perfectly now.
C

What are some practical use cases for memory tables?
SQL, Database Testing • mysql memory optimized tables mariadb 10.2 • • charlenef

2

0
Votes

2
Posts

0
Views

A

If you are learning MySQL, skipping MEMORY storage engine is not a bad idea. InnoDB is the default storage engine and it is suitable for most cases. Only in edge cases it makes sense to use another one, and you probably shouldn't consider this option before knowing InnoDB quite well. However, to answer your question, first let's check pro's and con's of MEMORY. Cons: You could lose your data (except for table definition). No transactions, no consistency of any kind. Only supports HASH indexes, so there is no optimisation for queries like WHERE col > n, ORDER BY or GROUP BY. Massive usage consumes memory. No trivial way to monitor how much memory you are using for these tables. Pros: Not dramatically faster than InnoDB (in terms of latency), but still generally faster when used properly. Better throughput because you don't talk to storage devices. So the question becomes, which use cases don't suffer because of the cons and take advantage of the pro's? Well, a first obvious generic answer is, avoid all use cases where data loss is a problem. More specifically, you could consider MEMORY for: Caches: all queries are point searches, data can always be reconstructed. Speed up some massive insertion. You put data into a MEMORY table temporarily, do your transformations, and copy the rows to a "regular" table. But if your transformations require BTREE indexes, this is not a good option. TEMPORARY tables, when you don't need BTREE indexes.
K

SQL Server Express vs. Oracle Express Edition vs DB2 Express-C
SQL, Database Testing • sql server oracle db2 express edition • • kalyn

2

0
Votes

2
Posts

0
Views

J

I think you are missing couple important parameters. If you believe the project will advance , you have to keep in mind that one day you will need to switch to non-express version. And at that point license cost becomes very important; switching to another RDMS may require serious refactoring (and usually it does). For startup project I'd not pay too much attention to hardware configuration supported in express version, I'm pretty sure even the lowest will work fine. More important thing in my opinion is DBA's skills; in practice, not so many database professionals are equally good in all RDMS. So ask your DBA which server she/he would prefer before making a decision.
M

Missing two tenths in mysql?
Software Programming • mysql • • magpie

2

0
Votes

2
Posts

0
Views

You can use https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_truncate , is this case you can define the number of decimals to show when making the query (second parameter define 2), examples:mysql> SELECT TRUNCATE(1.223,1) -> 1.2 mysql> SELECT TRUNCATE(1.999,2) -> 1.99 mysql> SELECT TRUNCATE(1.999,0) -> 1 mysql> SELECT TRUNCATE(-1.999,1) -> -1.9 mysql> SELECT TRUNCATE(122,-2) -> 100 mysql> SELECT TRUNCATE(10.28*100,0) -> 1028 https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_truncate Return number X, truncated to D decimals. Yeah. D is 0, the result has no decimal point or fractional part. D can be negative to make D digits to the left of the Decimal point of the X value become zero.I see that as an option is mentioned https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_format or ROUND() But in this case the value could be rounded which is not correct.mysql> SELECT FORMAT(1.999,2) -> 2.00 mysql> SELECT ROUND(1.999,2) -> 2.00
K

Upgrade from SQL Server 2008 R2 SP2 to SQL Server 2014 Error
SQL, Database Testing • sql server sql server 2008 r2 sql server 2014 upgrade • • kalena

2

0
Votes

2
Posts

0
Views

Z

The problem is related to the account name, that is, the login name or user name. Windows creates security ID for each account we create. It could be the case where security ID is missing for some account. You may try creating a new username as suggested in the following referenced article. http://community.norton.com/en/forums/solution-no-mapping-between-account-names-and-security-ids-was-done-error Hope this helps.
SQL Error while connecting with SQL server Authentication
SQL, Database Testing • sql server sql server 2005 • • Marcee

2

0
Votes

2
Posts

0
Views

J

It looks like you have to enable sql authentication. Start the management studio and connect to your database. Right click porperties and in the security tab, allow sql authentication in addition. Then you can connect with the user sa. Atm it looks only on windows accounts.
R

What is the T-SQL syntax to connect to another SQL Server
SQL, Database Testing • sql server • • rielyn

2

0
Votes

2
Posts

0
Views

You can kind of make up a linked server on the fly: SELECT name FROM OPENROWSET ( 'SQLNCLI', 'Server=Server_Name\Instance_Name;UID=Username;PWD=Password;', 'SELECT name FROM master.sys.databases;' ); If you want Windows auth, just change the second line to: 'Server=Server_Name\Instance_Name;Trusted_Connection=yes;', First you need to set up the source server correctly: EXEC sp_configure 'show advanced', 1; GO RECONFIGURE WITH OVERRIDE; GO EXEC sp_configure 'ad hoc distributed queries', 1; GO RECONFIGURE WITH OVERRIDE; GO EXEC sp_configure 'show advanced', 0; GO RECONFIGURE WITH OVERRIDE; GO But you can't parameterize this etc., making it much less useful than a linked server or SQLCMD mode. So I suggest if all three of these options are not desirable for you, stop trying to catch fish with a toothpick, and do this outside of the database - PowerShell, etc. T-SQL was not really designed to function as an enterprise-wide information gatherer.
S

My SQL syntax is error, how to solve it?
SQL, Database Testing • mysql • • Saumya

2

0
Votes

2
Posts

0
Views

T

MySQL doesn't support Common Table Expressions (CTE) so either your mysql tag is incorrect or you've pulled this syntax from nowhere. To make this work with MySQL, you'll need to rewrite it as derived tables. Note, these sql fiddles use the question that was a duplicate of this as it was at least more interesting to answer.https://stackoverflow.com/questions/1382573/how-do-you-use-the-with-clause-in-mysql This is the SQL Server version but I guarantee whatever logic you're attempting could be simplified if you explained what you were attempting to do versus "code doesn't work" This specific question suffers from two issues: The first and already addressed is that CTEs WITH don't exist in mysql. The second is simply syntax. You have SELECTMAX instead of SELECT MAX WITH max_budget(value) AS (SELECT MAX(budget) FROM department) SELECT budget FROM department, max_budget WHERE department.budget = max_budget.value; The above is valid syntax for SQL Server and all DBMS that have implemented CTEs. The MySQL equivalent follows SELECT * FROM department WHERE department.budget = ( SELECT MAX(budget) FROM department ); This uses a correlated subquery which has the distinct advantage of working in SQL Server, MySQL, PostGres, etc. You could also write it as a derived table: SELECT department.* FROM department JOIN ( SELECT MAX(budget) AS value FROM department ) max_budget ON department.budget = max_budget.value ;

SQL Syntax Error when using Group by conditional expression

Suggested Topics

Pagement with NodeJS with database MySQL Software Programming • javascript mysql node.js react page • • morde

Error when using WITH (DROP_EXISTING = ON) SQL, Database Testing • sql server sql server 2014 index maintenanc language • • Alberto

Union of two mysql consultations Software Programming • mysql sql • • gionna

Using TDE and SQL Server 2016 AlwaysOn Availability Groups SQL, Database Testing • sql server sql server 2016 availability groups transparent data enc lang • • Laycee

Data coding in phpmyadmin Software Programming • php mysql recoding • • chanisef

db query error: pq: column "systemevents.receivedat" must appear in the GROUP BY clause or be used in an aggregate function SQL, Database Testing • postgresql group by postgresql 10 • • morde

Logging the conditions within a conditional split transformation (SSIS in SQL Server 2012 and 2016) SQL, Database Testing • sql server ssis ssis expressions • • Marcee

SQL Agent - PowerShell step "syntax error" SQL, Database Testing • sql server 2012 powershell windows server • • Analeea

What are some practical use cases for memory tables? SQL, Database Testing • mysql memory optimized tables mariadb 10.2 • • charlenef

SQL Server Express vs. Oracle Express Edition vs DB2 Express-C SQL, Database Testing • sql server oracle db2 express edition • • kalyn

Missing two tenths in mysql? Software Programming • mysql • • magpie

Upgrade from SQL Server 2008 R2 SP2 to SQL Server 2014 Error SQL, Database Testing • sql server sql server 2008 r2 sql server 2014 upgrade • • kalena

SQL Error while connecting with SQL server Authentication SQL, Database Testing • sql server sql server 2005 • • Marcee

What is the T-SQL syntax to connect to another SQL Server SQL, Database Testing • sql server • • rielyn

My SQL syntax is error, how to solve it? SQL, Database Testing • mysql • • Saumya

Pagement with NodeJS with database MySQL
Software Programming • javascript mysql node.js react page • • morde

Error when using WITH (DROP_EXISTING = ON)
SQL, Database Testing • sql server sql server 2014 index maintenanc language • • Alberto

Union of two mysql consultations
Software Programming • mysql sql • • gionna

Using TDE and SQL Server 2016 AlwaysOn Availability Groups
SQL, Database Testing • sql server sql server 2016 availability groups transparent data enc lang • • Laycee

Data coding in phpmyadmin
Software Programming • php mysql recoding • • chanisef

db query error: pq: column "systemevents.receivedat" must appear in the GROUP BY clause or be used in an aggregate function
SQL, Database Testing • postgresql group by postgresql 10 • • morde

Logging the conditions within a conditional split transformation (SSIS in SQL Server 2012 and 2016)
SQL, Database Testing • sql server ssis ssis expressions • • Marcee

SQL Agent - PowerShell step "syntax error"
SQL, Database Testing • sql server 2012 powershell windows server • • Analeea

What are some practical use cases for memory tables?
SQL, Database Testing • mysql memory optimized tables mariadb 10.2 • • charlenef

SQL Server Express vs. Oracle Express Edition vs DB2 Express-C
SQL, Database Testing • sql server oracle db2 express edition • • kalyn

Missing two tenths in mysql?
Software Programming • mysql • • magpie

Upgrade from SQL Server 2008 R2 SP2 to SQL Server 2014 Error
SQL, Database Testing • sql server sql server 2008 r2 sql server 2014 upgrade • • kalena

SQL Error while connecting with SQL server Authentication
SQL, Database Testing • sql server sql server 2005 • • Marcee

What is the T-SQL syntax to connect to another SQL Server
SQL, Database Testing • sql server • • rielyn

My SQL syntax is error, how to solve it?
SQL, Database Testing • mysql • • Saumya