I am pulling some results from MySQL database like below: GetJobCodes=paste0(“select EMPLID from jobCurrent where JOBCODE=’”,JOBCODE,”‘”) JOBCODES = dbGetQuery(connection,GetJobCodes) and I want …
Ordering an alias column in union query
I have a SQL query that utilises a union a few times, and within it I’ve created an alias column called ‘Category’. When I try a simple order by on the alias column it works fine. However the data in my alias column has numbers and they order like 1,10,11 instead of 1,2,3 etc.. When I try ch…
Comparing SQL datetime to C# DateTime.Now
I’ve been trying to find a proper solution for the following problem. I have an SQL table with a time column (in this format: hh:mm:ss). It contains a starting time for a program. I now want to …
Computed boolean column while creating table
I’m trying to create table with computed boolean values CREATE TABLE parts ( Id SERIAL PRIMARY KEY, Weight INTEGER NOT NULL CHECK(Weight > 0), Color VARCHAR(10), Price INTEGER NOT …
NULL handling enhancement in PL/SQL query
The below query doesn’t list out all records that contain null value in WFD_DETECTION_EPA when we pass in some value in S1_WFD_OPERATION_CODE variable. Only records with WFD_DETECTION_EPA is not null …
Hive: How do I INSERT data FROM a PARTITIONED table INTO a PARTITIONED table?
This is an extension of a previous question I asked: Is it possible to change an existing column’s metadata on an EXTERNAL table that is defined by an AVRO schema file? Question: In Hive 2.1.1, how do I INSERT data FROM a PARTITIONED table INTO a PARTITIONED table? What is the correct syntax? I have see…
Rewrite MS Access SQL statement to SQL Server
I wish to convert the following MS Access SQL statement to SQL Server. All of my attempts are resulting in different results from the old & original data. The new Query is: The problem caused by grouping date column in new statement, but in old one it is used in Last function to avoid grouping it and stil…
One if or multiple where?
Let’s say, I want to set a value to multiple rows depending on the value of another cell. So, I can make this in 2 ways: Select all items and set different values using IF: Update `table` set `a` = …
JSONB sort aggregation
I found this query that suits my needs thanks to this answer here in order to sort fields of data in a JSON document. (Fake, generated random data) SELECT jsonb_agg(elem) FROM ( SELECT * FROM …
Where clause in upsert conflict target
Suppose I have a table created like this: When attempting to insert a new row, there are three possibilties: No row for ‘name’ exists. The new row should be inserted. A row for ‘name’ exists that has a value of NULL. The existing row should be updated with the new value. A row for R…