Skip to content

Tag: case

SQL Nested Joins (Case Statement and Join)

Hive DBMS; Two tables — A and B Table A Table B Question –> Trying to execute a query where: Join table A with table B, first on prnt_id, if it’s “unknown”, then join on sub_id, if that is “unknown”, join on ac_nm Desired Output: Answer You must use LEFT joins of TableB to 3 copies of TableA and filter

Apply limit to CASE statement

I am trying to collect counts of different columns for players (you don’t need to know the ins and outs of all the columns). We have a player_1 and player_2 column so I have made it distinct to get total counts, as you can see below: I want to also get the frequency of goals for last 32 games for

Hive Query to insert a value conditionally

I have a Table1 containing some blacklisted names. Now suppose I receive a record “def”. The hive query should check if “def” is present in Table1 or not. If not the name_status should be set to blacklisted otherwise null. The name “def” will be inserted in both cases. The problem I am facing is that in hive we cannot use

SQL Case Order By specific order and Direction

I have a table that I need sorted on fields By @SortBy and @SortDirection, for ID,PriorityID,stateType (column type is int) result is OK, but for Title (nvarchar) Query Result is: Conversion failed when converting the nvarchar value ‘Title Column value’ to data type int. Query: Answer The types are different, and that is a problem for the case expressions. The

Set prefix in SQL CASE expression

I have problem setting a prefix in a case statement. Data set: When I run this query my result is this: Desired result is this: As one can see, the first zero is not shown in the result. How can I achieve this? Answer CASE expression would always return one type so, you need to do conversion: