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…
Tag: sql
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 …
How to do group_concat with Integer columns?
This query displays performance of 3 endpoints combined (decided using some priority) over a series of days in the period of last 2 weeks. Just for display in the dashboard, I need to combine the values of the dataset using something like group_concat. This seems to be working fine for String type column. How…
How to transpose row to columns in Oracle as shown below using Unpivot?
Source Table Cost Category Cost Category Type Q1-2020 Q2-2020 Q3-2020 Q4-2020 Employee Impacted Period Cost 10 20 0 4000 Achieved Result Cost Category Cost Category Type Quarter Year Value Employee Impacted Period Cost Q1-2020 10 Employee Impacted Period Cost Q2-2020 20 Employee Impacted Period Cost Q3-2020 0…
Error Using the iif statement in MS access
The completion_metric field from this query I made in MS ACCESS keeps giving me zero percent for all the records. I think something is wrong with my IIF statement. Also, how would can I populate a text box on a ms access form with this query. For text-boxes there seems to not be an option to let my record sou…
How to solve “SQL aggregate function not allowed”?
I want to get the cases per million where a table contains “Province Population” and another table “Interim” contains a column that has unique Epid_ID values. I want to divide the count of these “Epid_ID” values with the population where the data type of population is big n…