sql - HIVE Group By in Multiple Tables

Two tables - 'salaries' and 'master1'The salaries is by year, and I can group them to get the sums for each player using SELECT playerID, sum(salary) as sal FROM salaries GROUP BY playerID ORDER BY sal DESC LIMIT 10; This returns the playerID and sum of salary, but I need the names of the players from the 'master1' table under column 'nameFirst' and 'nameLast'. They have the common column of 'playerID' in both 'master1' and 'salaries' but when I try to runSELECT master1.nameFirst, master1.nameLast, sum(salary) as sal FROM salaries, master1 GRO...Read more

sql - What to get the data From a column based on the JSON Tag Stored in the Column

I have a Hive table which stores the Data as JSON file name in one column and Full JSON on the Other Column.lets say col1 has data.json and col 2 has JSON in it { "ID": "1", "Name": "ABC", "Dept":"market" }I want to Build a Query where i can get the name from JSON on the basis of ID, Consider below as an example for the query which I wantselect (SHOULD GIVE JSON TAG **NAME**) from temp where col1=data.json and col2 (JSON ID is 1)It should Return ABC for the Above JSON Stored in Col2....Read more

Teradata SQL - Conditions depending on conditions in WHERE clause

This question is a variation on an older post The conditionsIf Customer_country = ‘A’then Ship_country must = ‘A’ and Customer_number <> ‘A2’ (i.e. exclude A2)and date is between x and y. (same for all Customer country)The same logic goes for all Customer_country, where respective B2, C2, D2 Customer_numbers are excluded. I am confused with using CASE…WHEN because it is expected to return a value.But I am not returning any value. Please help with this query....Read more

amazon redshift - SQL rollup on sessions

I have an impression event table that has a bunch of timestamps and marked start/end boundaries. I am trying to roll it up to have a metric that says "this session contains at least 1 impression with feature x". I'm not sure how exactly to do this. Any help would be appreciated. Thanks.I want to roll this up into something that looks like:account, session_start, session_end, interacted_with_feature3004514, 2018-02-23 13:43:35.475, 2018-02-23 13:43:47.377, FALSEwhere it is simple for me to say if this session had any interactions with the featur...Read more

sql - Inserting Data into Hive Table

I am new to hive. I have successfully setup a single node hadoop cluster for development purpose and on top of it, I have installed hive and pig.I created a dummy table in hive:create table foo (id int, name string);Now, I want to insert data into this table. Can I add data just like sql one record at a time? kindly help me with an analogous command to:insert into foo (id, name) VALUES (12,"xyz);Also, I have a csv file which contains data in the format:1,name12,name2......1000,name1000How can I load this data into the dummy table?...Read more

hadoop - Hive insert query like SQL

I am new to hive, and want to know if there is anyway to insert data into hive table like we do in SQL. I want to insert my data into hive likeINSERT INTO tablename VALUES (value1,value2..)I have read that you can load the data from a file to hive table or you can import data from one table to hive table but is there any way to append the data as in SQL?...Read more

sql - Split single row columns into multiple columns in Hive

I have a Hive table like thisID1 Name1 ID2 Name2 ID3 Name3....1 ABC 2 MNP 3 XYZ11 LMP 12 PLL 13 UIPThis table may have any no. of columns pair(i.e. ID and Name)I need to convert the above table into a new Hive table having only 2 columns ID and Name like belowID Name1 ABC2 MNP3 XYZ11 LMP12 PLL13 UIPPlease suggest how can I achieve this in Hive.Thanks...Read more

Search text in stored procedure in SQL Server

I want to search a text from all my database stored procedures. I use the below SQL:SELECT DISTINCT o.name AS Object_Name, o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id = o.object_id WHERE m.definition Like '%[ABD]%';I want to search for [ABD] in all stored procedures including square brackets, but it's not giving the proper result. How can I change my query to achieve this?...Read more

sql server - SQL update from one Table to another based on a ID match

I have a database with account numbers and card numbers. I match these to a file to update any card numbers to the account number, so that I am only working with account numbers. I created a view linking the table to the account/card database to return the Table ID and the related account number, and now I need to update those records where the ID matches with the Account Number.This is the Sales_Import table, where the account number field needs to be updated:LeadID AccountNumber147 5807811235150 5807811326185 70061001...Read more

Reset identity seed after deleting records in SQL Server

I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each table has to have a primary key and identity defined. But since I have to delete some records from the table, the identity seed for those tables will be disturbed and the index column (which is auto-generated with an increment of 1) will get disturbed.How can I reset the identity column after I deleted the records so that the column has sequence in a...Read more

sql server - Parameterize an SQL IN clause

How do I parameterize a query containing an IN clause with a variable number of arguments, like this one?SELECT * FROM Tags WHERE Name IN ('ruby','rails','scruffy','rubyonrails')ORDER BY Count DESCIn this query, the number of arguments could be anywhere from 1 to 5.I would prefer not to use a dedicated stored procedure for this (or XML), but if there is some elegant way specific to SQL Server 2008, I am open to that....Read more

sql - Active Directory: Convert canonicalName node value from string to integer

Are there any methods available to convert the string text value contained within the AD canonicalName attribute to an incremented integer value? Or, does this need to be performed manually?For example:canonicalName (what I am getting) hierarchyNode (what I need)\domain.com\ /1/\domain.com\Corporate /1/1/\domain.com\Corporate\Hr /1/1/1/\domain.com\Corporate\Accounting /1/1/2/\domain.com\Users\ /1/2/\domain.com\Users\Sales ...Read more

sql - I keep getting an error when i run this query. Anyone see where i messed up at?

SELECT [property usage], [total], [todate], [todateorder], [purpose of loan], [marital status], [agegroup], [race], [sex], Datepart(mm, loandate) LoanMonth, Datepart(yy, loandate) LoanYear, Avg(CONVERT(DECIMAL(10, 2), monthlyincome)) AvgMonthlyIncome, Avg(CONVERT(DECIMAL(10, 2), loanamount)) AvgLoanAmount, Sum(CONVERT(DECIMAL(10, 2), monthlyincome) / CONVERT(DECIMAL(10, 2), loanamount...Read more