logo
down
shadow

Set a variable inside case statement in mysql


Set a variable inside case statement in mysql

By : DavidV
Date : November 19 2020, 03:01 PM
Hope that helps I have the following scenario , What about this?
code :
SELECT ...
   , @var1 = MAX(IF(table1.name='Mod', table1.value, NULL))
   , @var2 = MAX(IF(table1.name='Man', table1.value, NULL))
   , CASE
        WHEN @var1 IS NOT NULL && @var2 IS NOT NULL THEN @var1+"/"+@var2
        WHEN @var1 IS NULL && @var2 IS NOT NULL THEN @var2
        WHEN @var1 IS NOT NULL && @var2 IS NULL THEN @var1
        ELSE NULL
     END AS "col44"
   SELECT ...
         , CASE
            WHEN modMax IS NOT NULL && manMax IS NOT NULL THEN modMax+"/"+manMax
            WHEN modMax IS NULL && manMax IS NOT NULL THEN manMax
            WHEN modMax IS NOT NULL && manMax IS NULL THEN modMax
            ELSE NULL
         END AS "col44"
    FROM (
        SELECT ...
           , MAX(IF(table1.name='Mod', table1.value, NULL)) AS modMax
           , MAX(IF(table1.name='Man', table1.value, NULL)) AS manMax
        ...
      ) AS subQ


Share : facebook icon twitter icon
Trying to set a variable inside a case statement.

Trying to set a variable inside a case statement.


By : user2212162
Date : March 29 2020, 07:55 AM
will help you It may not be the most elegant solution, but here is what I ended up with.
I linked a copy of the script details to a grouped by version of the same thing.
code :
SELECT fcname FYName, fdstart PeriodStart, fdend PeriodEnd, fnnumber PeriodNo, GLRAGG.AGGFYName,
       GLRAGG.QuarterSize, GLRAGG.PeriodCount, GLRAGG.Quarterific, GLRAGG.SemesterSize, GLRAGG.Semesterific
FROM   M2MData01.dbo.glrule GLR
     INNER JOIN
       (SELECT fcname AGGFYName, min(fdstart) YearStart,
               MAX(fdend) YearEnd, MAX(fnnumber) PeriodCount,
               (Max(fnnumber) / 4) QuarterSize, CASE WHEN Max(fnnumber) % 4 = 0 THEN 'Yes' ELSE 'No' END AS Quarterific,
               (Max(fnnumber) / 2) SemesterSize, CASE WHEN Max(fnnumber) % 2 = 0 THEN 'Yes' ELSE 'No' END AS Semesterific
        FROM M2MData01.dbo.glrule
        GROUP BY fcname) GLRAGG
     ON GLR.FCNAME = GLRAGG.AGGFYNAME
MySQL SELECT statement inside CASE

MySQL SELECT statement inside CASE


By : Berliana Rianti
Date : March 29 2020, 07:55 AM
wish of those help I have a PRODUCTS table which contains product records or subproducts records. Subproducts are indicated by having an entry in the HIGHERCATALOGID column, while for products the HIGHERCATALOGID column is NULL. I'm trying to write a query that would output product name if catalogid is pointing to a product record, or name of the parent product if catalogid is pointing to a subproduct. Here is what I tried to do: , You can try it this way
code :
SELECT p.catalogid, p.highercatalogid, oi.orderid,
       COALESCE(p2.cname, p.cname) cname
  FROM oitems oi JOIN products p
    ON oi.catalogid = p.catalogid LEFT JOIN products p2
    ON p.highercatalogid = p2.catalogid
How do I use a case and an if statement inside an update...set in mysql?

How do I use a case and an if statement inside an update...set in mysql?


By : Joan
Date : March 29 2020, 07:55 AM
wish help you to fix your issue This is what I'm trying to do: , I think you can use a nested case to do this:
code :
UPDATE
    my_table
SET
    column_a = (
        case
            when (@var_a := (
                case 
                    when column_a is null then 20
                    when column_a < 10 then column_a
                    when column_a = 10 then 20
                    else least(column_a, 20)
                end)) <= unix_timestamp() then @var_a
            else null
        end)
WHERE
    column_b = 'something';
MySQL IF inside a SWITCH CASE statement

MySQL IF inside a SWITCH CASE statement


By : Rhisereld
Date : March 29 2020, 07:55 AM
this one helps. I'm trying to put a couple of if statements inside of a CASE WHERE in a select query to control the order by. This is a simplified version that is still giving me a syntax error. , I think you meant to do like below using a Nested CASE expression
code :
ORDER BY
    CASE 
      WHEN MONTH(now()) >= '9' THEN    
          CASE WHEN YEAR(st_graduation) = YEAR(now()) THEN 0 ELSE 1 END
  ORDER BY
    CASE 
      WHEN (MONTH(now()) >= '9')
        THEN    
          CASE WHEN YEAR(st_graduation) = YEAR(now()) THEN 0 ELSE 1 END          
      WHEN (MONTH(now()) < '9')
        THEN
          CASE WHEN YEAR(st_graduation) >= YEAR(now()) THEN 1 ELSE 0 END
      END, st_graduation;
or inside case statement not running in mysql

or inside case statement not running in mysql


By : chrislamp
Date : March 29 2020, 07:55 AM
it helps some times Use the IS operator when comparing with NULL. Because comparing something with NULL will lead to unknown when using the normal operators, which is not true.
Related Posts Related Posts :
  • How to setup local server for wordpress site with git
  • Multiplying CASE row with different values
  • Index on a table not being used all the time
  • How can I get a date from mysql database if it is not null?
  • How to execute a TRIGGER in MSSQl?
  • MySQL 8 Window Functions + Full-text searching
  • Join on large table getting slower
  • Select record from two different table
  • Getting 1064 error while creating mysql trigger
  • MySQL Database Operations
  • How can I make this SQL sort by most relevent?
  • Database query on month
  • Select number of matching rows of a particular column in MySQL
  • How to use rake database commands with password
  • If we change a primary key value, why don't we have to change a dependent column value?
  • MySQL - selecting all records except the ones already associated in the relational table
  • Delete Duplicate MySQL rows but keep one
  • ORDER BY does not perform any function
  • Update column with output of select within the same table in mysql
  • MYSQL: How to get rows inserted in the last X hours without querying the entire table
  • AWS RDS MySQL Cross-region replication
  • Cannot truncate a table referenced in a foreign key constraint from empty table
  • How does SQL determine a character's length in a varchar?
  • MySQL : Getting DB row with exact same data from a vector
  • Mysql update query with join
  • Group values that have the same name in one column and same id in other column
  • Mysql query syntax for conditional inserts
  • Is it faster to run an SQL count(*) query in a loop, or try to merge it into the parent query?
  • MySQL query to fetch product variants
  • Report Reindex taking too long after destroy
  • MySQL - Adding varchar as a foreign key
  • How to find duplicated entries which has different slug?
  • SQL Query comparing values in different rows
  • Eloquent giving error but query executes fine in phpmyadmin
  • JPQL fetch data from multiple tables
  • MySql Query - Expanding data into new table
  • Official MySQL Docker container not caching queries?
  • MySQL, Count values in every fields in a table
  • SQL : number different dates within users
  • elastic beanstalk docker app cannot connect to mysql
  • Why am I getting errno: 150 "Foreign key constraint is incorrectly formed"?
  • Update columns which included in payload
  • MySQL subquery in select
  • Get difference in top 2 salary of employee from each department
  • connecting to database using RMySQL and .my.cnf file in R
  • How to get data by mysql
  • SQL Error [1054] [42S22]: Unknown column ' ' in 'field list'
  • turn c# code into mysql function
  • How to loop through and output nested array in Laravel
  • MySQL TRIM spaces inside text
  • Mysql query with multiple selects results in high CPU load
  • Backup DB Django MysqlDump
  • How to select all rows from group, until occurrence of a value
  • Using substring to filter a specific word from a string in MySQL
  • Mysql - Alias in Left Outer Join giving error
  • How use custom alias field from select fields in join?
  • How to sum durations in units of Year, Month and Day in MySQL?
  • Is it possible to assign the values from select exist query in MySQL to multiple variables in a stored procedure?
  • How does mysql resolves conflict when same option is configured twice?
  • Most efficient way to clone an AWS RDS database?
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org