I’m trying to find the query in order to match similar customers. To simplify the situation consider this scenario: I have a table which contains a customer name and product purchased. customer name …
Left join using a temporary column
I want to do left join on two tables but the second table doesn’t have required column. So a temporary column (concat of two column values in second table) is needed in order to do the left join. …
Populating scd type 2 kind of dates for active inactive input data
I saw this question in one of the interviewing practice platforms and initially assumed its a gaps and islands problem, but I couldn’t go ahead with that logic. In ETL tool called Abinitio, there is a function called key_change using which we can solve these kind of problems. But, I am unable to do it i…
How to subtract from certain cell in Bigquery?
I have data date and a in this table my table How I can get b where the value is a subtract by a from previous date? Answer You may use the LAG analytic function here:
single-row subquery returns more than one row in oracle in HR schema
and I found an error like the following : Answer Looks like there are “many” employees whose job_id = ‘IT_PROG’. In that case, subquery returns “many rows” and your query results in an error. It depends on what you want to do in such a case; for example, you might want to r…
SQL unique index explaination
I have seen this for the first time. This is a section in liquibase for defining the unique columns in a table. But I don’t understand which columns are unique. Are all of the columns mentioned here unique? For example Or is only unique? How to read that? Thank you very much. Answer The unique index spe…
I want to mapping MultipartFile with userId
I’m trying to upload excel file then save it to Product DB, I have User, Product Tables but product mapped by user Table. so I got error when I try to upload. Since I need to check who upload the file. mapping is nessesary. I don’t know where should I put the code to map the userId. Editted post #…
SQL Query OneToMany Filtering with child entity but returning parent
Hey I need some help with an SQL Query I just can’t figure out. I got these two Tables with an OneToMany/ManyToOne relation: Now I got this select query to get me all Books that have been written before the current date: (I’m doing this in Java JPA via the Query Annotation) In this case I am retur…
JOIN is MUCH slower than a UNION, even with indexes
I have a query: All tables have indexes. Why is this query taking 4.5s across 6000 rows? Explain analyze output below: I have a UNION query that does the same thing, but is much quicker (0.001s). I want to know more about why my JOIN query is so much slower: Answer OR is always difficult for the optimiser(s).…
Return value from dynamic linked server stored procedure
I want to return a value from a dynamic linked server stored procedure. On the local server, how can I retrieve the value of @DocNumOut? I have trial many combination using EXEC, openquery, EXEC sp_executesql, but none can return the value into my local variable, @DocNumberOUT. Answer Use OUTPUT parameter on …