logo
down
shadow

Can we Use "Case" in a ColdFusion Query-of-Query


Can we Use "Case" in a ColdFusion Query-of-Query

Content Index :

Can we Use "Case" in a ColdFusion Query-of-Query
Tag : sql , By : toma
Date : November 25 2020, 01:01 AM

hope this fix your issue Update:
The original suggestion isn't going to work due to it only looking at a single row. Really you need to loop through your all_employees recordset and apply it to each individual row.
<cfoutput>
    <cfloop query="all_employees">
     <cfif isNumeric(all_employees.userdefined)>
      #Right('00000000'&all_employees.userdefined,8)#
     <cfelse>
      #all_employees.userdefined#
     <cfif>
    </cfloop>
</cfoutput>
<cfquery name="qEmployees1" dbtype="query">
SELECT 
<cfif isNumeric([all_employees].[u.userdefined])>
  right('00000000'+u.userdefined,8) 
<cfelse>
 u.userdefined
</cfif> AS hello
FROM all_employees
ORDER by hello
</cfquery>

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Need help understanding a query involving "case" and "select into" clauses


Tag : sql , By : kema
Date : March 29 2020, 07:55 AM
wish of those help To insert into an existing table, you need INSERT INTO dbo.ModifiedLocation SELECT ...
The SELECT ... INTO dbo.ModifiedLocation FROM ... syntax is for creating the table as well as inserting into it.

If statement to distinguish which "Case" query should be added to select query


Tag : mysql , By : FarmerDave
Date : March 29 2020, 07:55 AM
I wish did fix the issue. I'm stacked on following query. I need to create an if statement or something similar to distinguish which "CASE" should be added to my select query. , You can nest CASE clauses like this:
SELECT
    CASE
        WHEN (page_type IN ('Region', 'City')) THEN
            CASE
                WHEN landing_page LIKE ... THEN 'True'
                ELSE 'False'
            END
        WHEN (page_type = 'Item') THEN
            CASE
                ...
            END
        WHEN (page_type = 'POI (Alle)') THEN
            CASE
                ...
            END
    END AS lp,
    MONTHNAME(k.crawl_date) As 'Month', -- other fields
    ...
SELECT
    IF ( (page_type IN ('Region', 'City')),
        -- if (page_type IN ('Region', 'City')) then
        CASE
            WHEN landing_page LIKE ... THEN 'True'
            ELSE 'False'
        END,
        -- else
        IF ( (page_type = 'Item'),
            -- if (page_type = 'Item') then
            CASE
                ...
            END,
            -- else
            IF ( page_type = 'POI (Alle)',
                -- if (page_type = 'POI (Alle)') then
                CASE
                    ...
                END,
                -- else
                NULL
            )
        )
    ) AS lp,
    MONTHNAME(k.crawl_date) As 'Month', -- other fields
    ...
SELECT CASE
    WHEN landing_page LIKE ... THEN 'True'
    ELSE 'False'
END AS alias ;

-- strictly equivalent to

SELECT IF(
    landing_page LIKE ...,
    'True',
    'False'
) AS alias ;
CASE
    WHEN k.pos = 0 THEN 'No Rank'
    WHEN k.pos < 4 THEN 'Top 3'
    ELSE CONCAT('Page ', (k.pos -1) DIV 10 +1 )
END AS 'Page' 

OR Query mongodb from java with "like" and "line break" and "case insensitive" at the same


Tag : java , By : ugufugu
Date : March 29 2020, 07:55 AM
With these it helps It is possible to do a regular expression that achieves what you want. You can also use a single regular expression rather using $or.
I'm using the shell for a quick example and wanting to search for boxer or cat. First insert the test data:
db.test.drop()
db.test.insert([
{ "a" : "Boxer One" },
{ "a" : "A boxer dog" },
{ "a" : "A box shouldn't match" },
{ "a" : "should match BOXER" },
{ "a" : "wont match as this it the plural BOXERs" },
{ "a" : "also match on cat" }])
                                       
      /(^|\b)(boxer|cat)(\b|$)/i       
       +---+ +-------+  +---+         
          |       |        |           
          |       |        |           
   Start or space |       Space or end 
                  |                    
              Search terms
                                      
db.test.find({a: /(^|\b)(boxer|cat)(\b|$)/i})
{ "_id" : ObjectId("555f18eee7b6d1b7e622de36"), "a" : "Boxer One" }
{ "_id" : ObjectId("555f18eee7b6d1b7e622de37"), "a" : "A boxer dog" }
{ "_id" : ObjectId("555f18eee7b6d1b7e622de39"), "a" : "should match BOXER" }
{ "_id" : ObjectId("555f18eee7b6d1b7e622de3b"), "a" : "also match on cat" }
StringBuilder singularizedTerms = new StringBuilder();
for (String term : terms) {
    singularizedTerms.append("|").append(stprocess.singularize(term));
}
String regexPattern = format("(^|\\b)(%s)(\\b|$)", singularizedTerms.substring(1));
Pattern regex = Pattern.compile(regexPattern, Pattern.CASE_INSENSITIVE);
db.test.createIndex( { a: "text" } )
db.test.find({ $text: { $search: "boxer cat"}})

{ "_id" : ObjectId("555f18eee7b6d1b7e622de3b"), "a" : "also match on cat" }
{ "_id" : ObjectId("555f18eee7b6d1b7e622de3a"), "a" : "wont match as this it the plural BOXERs" }
{ "_id" : ObjectId("555f18eee7b6d1b7e622de36"), "a" : "Boxer One" }
{ "_id" : ObjectId("555f18eee7b6d1b7e622de37"), "a" : "A boxer dog" }
{ "_id" : ObjectId("555f18eee7b6d1b7e622de39"), "a" : "should match BOXER" }

"Case statement" in a "group by query" with Entity Framework 6


Tag : chash , By : nickthecook
Date : March 29 2020, 07:55 AM
I wish this help you I'm working with Entity Framework 6 and SQL Server 2014. , To fix it in the LINQ statement change:
GroupBy(x => x.Products.NAME).
GroupBy(x => { if (x.Products.NAME.StartsWith("green")) return "green"; 
               if (x.Products.NAME.StartsWith("red")) return "red"; 
               return x.Products.NAME; }).
var groupedData = MyData.GroupBy(x => { 
          if(x.Products.NAME.StartsWith("green")) return "green";
          if (x.Products.NAME.StartsWith("red")) return "red"; 
          return x.Products.NAME; })
          .Select(g => new { Product = g.Key, Cnt = g.Count()} )
          .Concat(new[] { new { Product = "allColor", 
                                Cnt = MyData.Count() 
                                }
                          });

Specified "COLLATE Latin1_General_CS_AS", query result still "case insensitive"


Tag : sql , By : pacorro2000
Date : March 29 2020, 07:55 AM
seems to work fine According the SQL Server Books Online, the characters included in range searches depend on the sorting rules of the collation. Collation Latin1_General_CS_AS uses dictionary order so both upper and lower case characters of the specified range are included.
Specify a binary collation to get the behavior you want (the 26 ASCII characters in the 97-122 code point range):
SELECT empid, lastname
FROM HR.Employees
WHERE lastname COLLATE Latin1_General_BIN LIKE '[a-z]%';
SELECT empid, lastname
FROM HR.Employees
WHERE lastname COLLATE Latin1_General_BIN LIKE '[a-z]%'
    AND lastname LIKE '[a-z]%';
Related Posts Related QUESTIONS :
  • Multi Keyword search on multi-column sql
  • Computing the median of salaries under each manager in BigQuery SQL
  • How can insert value by selecting from another
  • Retrieving only the latest item for a set of data
  • update column with concatenating for that group only
  • SQL `SELECT 'some string value' AS fieldname` equivalent in Powershell?
  • How to determine the AVERAGE number of associated records
  • In Django, how can I build a set of criteria from a list?
  • Using replace() inside a loop
  • select user on create view
  • Calculate and aggregate column depending on a value in another colum (SQL)
  • Trigger to track all the insert or update on Oracle Table
  • Update column - remove first and last characters
  • T-SQL - Adding FK to table, make it do drop/null/efault automagically?
  • Get constraints and indexes on materialized views
  • SQL perform AVG after MAX
  • How do you join two tables A and B in order to get a non join column in A with data not in B?
  • Move Non-NULL Values Up Within Partition
  • Selecting rows based on two many-to-many relations
  • I have a table on which i want to do some advance calculation and get the table in a new shape
  • How to convert decimal to time in Oracle SQL?
  • Combine numbers to 1 row
  • Error: column does not exist when in fact it does? why?
  • How to fix: "Operation must use an updateable query" on update statement in Access VBA
  • Does information passes to a new table via CREATE TABLE update itself?
  • How to write a SQL statement which filters between two dates/times, with the date and time in separate columns
  • MS-Access-2016 Query Language 'ORDER BY' Clause
  • How to get totals for each age category?
  • get the first n characters of getdate()
  • Query to handle the dates
  • SQL grouping/counting on a string split function
  • ORDER BY and TOP statements in SQL works different on listing down the records and why?
  • SQL Server Computed Column: how to set it to NOT NULL, without persistence
  • Is nested select statement better than where statement?
  • Postgresql sequential scan slow performance on 500 million rows
  • Aggregate first order data to all orders - Microsoft SQL Server
  • Sorting comma delimited datasets in row
  • Question about the correct data structure
  • Showing two temp tables data in a single table
  • How can I find out the query execution time in Oracle SQL using Toad?
  • convert quarter number to month name in sql
  • Find max value from column that has a json object with key-value pairs
  • case statement based on 3 columns in 1 table
  • How to write a SQL query to select batsman wiht highest score than the previous year
  • Find Substring in SQL
  • Using VALUE to make temporary table
  • What is the correct way of writing an exclusive left-join on two tables?
  • Is it possible to run a make table query using a recordset as the source in MS Access (VBA)?
  • INSERT INTO select distinct null PostgreSQL
  • Why converting date from nvarchar(max) to date throws error?
  • how to fix wm_concat in oracle 12c
  • How to UNION a list of tables retrieved from another table with a single query?
  • Oracle query to get a relational dataset result from multimensional table
  • What algorithm is used in a hash partition that it can divide up the data into similar size partitions before seeing any
  • Why does my SQL return unwanted records with my OR in Where clause
  • SQL Multiple Joins Query-Query tuning
  • How to choose max of one column per other column
  • Query that returns rows that have both values in an IN statement
  • Select Unique records from table
  • SQL Query "Select latest record where active flag = 1"
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com