I meet a strange problem in hive The table a: The table b When I use: It works well But when I use I got return null. The b.domain is ‘%taobao\.com%’ or ‘%tmall\.com%’. What’s the different using them direct in SQL query? What makes the second query failed? Answer Without ‘…
Split single row into multiple rows based on Date and time in SQL Server
I want to split a single row into multiple rows based on time. below are the example. Expected output is below: Day start from 06:00 AM to next day 06:00 AM. When EndDate time is grated than 06:00 AM then split this date in two rows. first row end date is 2018-08-21 05:59:00.000 and next row start 2018-08-21 …
SQL BCP Unable to insert value for identity column
I am using bcp utility to import a tab delimited file. My table has an identity column, when I run bcp command I get error as Error = [Microsoft][SQL Server Native Client 11.0] Invalid date format Second column is datetime. When i use -E flag (as suggested by many), I get error as Error = [Microsoft][SQL Serv…
How to find string that completely matches total or part of input
I will like to write a query to search a table containing millions of records for a value that totally and best matches the search string or its substring only from the beginning. Performance is of …
SSRS : How to make a data set field appear in the footer?
I have a simple report with a dataset( Patient id, programid, name, address, phone). In my report footer, I am trying to write a condition where the footer value shouldn’t show up for specific program …
MYSQL: Error Code: 1054. Unknown column in ‘where clause’
I’m trying to pass a column from the outer query as shown below to the inner query in the WHERE clause and MySQL does not like it. I’m unsure how to rewrite this query to make it work. The error …
I have a result set of continuous days and a severity rating. I would like to aggregate these into StartDate, EndDate, Severity
In SQL Server I have a table with the following data Date Severity 2018-09-10 40 2018-09-11 40 2018-09-12 40 2018-09-13 40 2018-09-14 40 2018-09-15 40 2018-09-16 20 2018-09-17 20 2018-…
Using LEFT with LEN Turns Float Into Weird Format (e.g. 6.0871e)
I have about 70 million rows of data with a column that contains numbers, but it’s in a float format. I need to get rid of the last 4 digits of that column i.e. I need to turn this 60871003002001 …
SQL – min(date) with conditionals
We ran a promotion where users can receive their first subscription order free. Price = $0.00 when a user uses the promo. I am interested in the data from Example A. Example A – User 50 started with …
SQL – Convert ROW_NUMBER function with multiple order by
I am trying to convert this query to a subquery without the ROW_NUMBER function: SELECT InvestorFundId, AsOfDate, AddedOn FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY InvestorFundId ORDER BY …