logo
Tags down

shadow

Using IN clause in THEN portion of a CASE expression


By : user6111749
Date : August 01 2020, 12:00 PM
wish help you to fix your issue I want to include only certain customergroups for each region in my query. I am using the below query that shows an error. , You can do it without a case expression:
code :
WHERE (Region = 'TN' AND CustomerGroup in (68,75,78,69,51,77,66))
OR    (Region= 'KL'  AND CustomerGroup in (77,01,68,51,69,78))


Share : facebook icon twitter icon

SQL CASE WHEN Recurring expression in case clause


By : user1623916
Date : March 29 2020, 07:55 AM
should help you out my query: , No idea about DB2, but I would try this:
code :
select
  CASE 
  WHEN 
  computed_col < 31 THEN ' 030'
  ...
  END AS AGED
from
(
  select DAYS(DATE(
  SUBSTR(DIGITS(STOCK_MONTH),5,4)  CONCAT '-'  CONCAT
  SUBSTR(DIGITS(STOCK_MONTH),9,2)  CONCAT '-01') - 1 DAY + 1 MONTH)
  - DAYS(HUB_ARRIVAL_DT) as computed_col
  from...
) as x

Oracle SQL CASE Including the expression clause Error ORA-00936: missing expression


By : user3749098
Date : March 29 2020, 07:55 AM
wish help you to fix your issue I have a CASE statement that works fine, and i want to include it in another CASE statement but i am getting a ORA-00936: missing expression error as i want it to use IN not =. , Clever, but that is not how case or in works. You can do:
code :
SELECT (CASE (CASE WHEN l.dnames = 'II-SH' THEN 'IW' 
                   WHEN l.dnames = 'IIC-SH' THEN 'IW' 
                   WHEN l.dnames = 'CD' THEN 'RPFX' 
                   WHEN l.dnames LIKE '%-%' THEN SUBSTR(l.dnames, INSTR(l.dnames, '-',2,1)+1, 20 )
                   WHEN l.dnames LIKE 'EXCR%' THEN SUBSTR(l.dnames,  (INSTR(l.dnames, 'EXCR',1,1)+4),20)
                   WHEN l.dnames = 'EX' AND l.ditems LIKE '%/%' THEN REPLACE(SUBSTR(l.ditems, 1,INSTR(l.ditems, '/',1)-1),'SINV')
                   WHEN l.dnames = 'EX' THEN REGEXP_REPLACE(REPLACE(REPLACE(l.ditems,'SINV'),'INV'), '[0-9]','')
                   WHEN l.dnames LIKE 'EX%' THEN SUBSTR(l.dnames, INSTR(l.dnames, 'EX',1)+2 ,20)
              END)
            WHEN 'BA' THEN 'FIRST'
            WHEN 'BG' THEN 'FIRST'
            WHEN 'BT' THEN 'First'
            WHEN 'BW' THEN 'Second'
            WHEN 'PF' THEN 'Second'
            WHEN 'SS' THEN 'Second'
            WHEN 'TC' THEN 'Third'
            WHEN 'NE' THEN 'Third'
        END) AS DGROUP
SELECT (CASE WHEN FieldName IN ('BA', 'BG', 'BT') THEN 'First'
             WHEN FieldName IN ('BW', 'PF', 'SS') THEN 'Second'
             WHEN FieldName IN ('TC', 'NE') THEN 'Third'
        END) AS DGROUP
FROM (SELECT (CASE WHEN l.dnames = 'II-SH' THEN 'IW' 
                   WHEN l.dnames = 'IIC-SH' THEN 'IW' 
                   WHEN l.dnames = 'CD' THEN 'RPFX' 
                   WHEN l.dnames LIKE '%-%' THEN SUBSTR(l.dnames, INSTR(l.dnames, '-',2,1)+1, 20 )
                   WHEN l.dnames LIKE 'EXCR%' THEN SUBSTR(l.dnames,  (INSTR(l.dnames, 'EXCR',1,1)+4),20)
                   WHEN l.dnames = 'EX' AND l.ditems LIKE '%/%' THEN REPLACE(SUBSTR(l.ditems, 1,INSTR(l.ditems, '/',1)-1),'SINV')
                   WHEN l.dnames = 'EX' THEN REGEXP_REPLACE(REPLACE(REPLACE(l.ditems,'SINV'),'INV'), '[0-9]','')
                   WHEN l.dnames LIKE 'EX%' THEN SUBSTR(l.dnames, INSTR(l.dnames, 'EX',1)+2 ,20)
              END) as FieldName

teradata SQL tuning pundits - SELECT Failed. 3771: Illegal expression in WHEN clause of CASE expression


By : Aadithya Bhardwaj
Date : March 29 2020, 07:55 AM
will be helpful for those in need Teradata doesn't like EXISTS in Correlated Scalar Subqueries within a CASE, but you can rewrite it like this:
code :
select
  ( CASE
       WHEN C1 = ( select MIN(C1) from VolatileTable Dtb1 
                   where Dtb1.c1=ft.C1)
       THEN ft."CustomColumName" 
       ELSE 'ALL OTHER'
    END  ) (NAMED "CustomColumName" )
from
"Db"."FACTTablew5MillionRows" as ft
select
   ft.*,
   CASE WHEN Dtb1.c1 IS NOT NULL 
        THEN ft."CustomColumName" 
        ELSE  'ALL OTHER' 
   end as "CustomColumName" 
from "Db"."FACTTablew5MillionRows" as ft
left join VolatileTable Dtb1 
on Dtb1.c1=ft.C1
from "Db"."FACTTablew5MillionRows" as ft
left join (select distinct C1 from VolatileTable) Dtb1 
on Dtb1.c1=ft.C1

Postgres CASE expression ELSE clause affects performance even when clause 'true'


By : user3420760
Date : March 29 2020, 07:55 AM
I hope this helps you . The difference is Filter vs. One-Time Filter.
In the first query, the condition in the CASE expression depends on phonecalls.phone_id from the sequential scan (even if that branch is never executed), so the filter will be applies to all 10000 result rows.

Return the exact portion string passing the same portion that is all lower case


By : user3646202
Date : March 29 2020, 07:55 AM
will be helpful for those in need You could lowercase the reference string and then use indexOf to see if the substring is contained. If so you can then use substr to pull out the subset of the original string.
code :
function getReferencePortion(reference, portion) {
  let index = reference.toLowerCase().indexOf(portion);

  if (index !== -1) {
    return reference.substr(index, portion.length);
  }
}
Related Posts Related Posts :
  • Excel - Generate INSERT Statements and Handle Single Quote
  • How can I return the first N number of characters ending with a complete word from a dash-delimited string?
  • Get Ids not exists in another table
  • SQL Server group by 2 columns - generate sequence number
  • Is the SQL Server data type called "money" a binary-fixed-point or decimal-fixed-point type?
  • Compare XML to string?
  • Insert multiple rows into an SQL table using a select statement?
  • Receiving "not enough storage" error while reading big Excel file
  • Can joins effectively ignore field indexes if they are a constant?
  • Form pivot table for more than two dynamic column
  • Create a dynamic SQL Where clause that both parts of the where statement are passed in parameters? i.e. Where Variable1
  • WinSCP script works fine manually, but failes when executed by SQL Server Agent Job
  • Select data from multiple tables with same column names and structure using sql
  • Which is preferred SELECT or SET
  • SSIS - Query table using id's from other database
  • SQL Server query without using cursor
  • Copy table data from Azure SQL Database to another Azure SQL Database?
  • Make names anonymous in reports SQL
  • Un-nest the field in SQL Server 2014
  • Unexpected duplicate rows in SQL Server
  • Extract text from a column
  • conversion failed when converting date from character message
  • Convert old SQL Database in compatibility mode
  • Access Upsizing - Property 'Attributes' already exists for 'table'
  • SQL Server decimal division not rounding the result
  • How does IDENTITY not equal record number in SQL Server?
  • How to filter recursive CTE SQL queries
  • conversion of a varchar data type to a datetime data type resulted in an out-of-range value: ONLY for leap year day
  • mdf can not open because version is 661 and my database support 665
  • Appropriate Windows O/S pagefile size for SQL Server
  • Have you ever encountered a query that SQL Server could not execute because it referenced too many tables?
  • How do I unit test persistence?
  • Can I logically reorder columns in a table?
  • What is the best way to copy a database?
  • Is Windows Server 2008 "Server Core" appropriate for a SQL Server instance?
  • Client collation and SQL Server 2005
  • Editing database records by multiple users
  • Deploying SQL Server Databases from Test to Live
  • Upgrading SQL Server 6.5
  • Versioning SQL Server database
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org