Need help understanding a query involving "case" and "select into" clauses
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]%';
|
Boards Message :
Related QUESTIONS :