this is my sample code;- Answer Imagine writing a query for your new view. Your column names – are you asking for a column called SUM(purch_amt), or are you trying to do a sum on a column called purch_amt? You need to provide a valid column name for the view, either implicitly or explicitly. Adding an a…
Tag: oracle
SQL Query Exercise Oracle 18c
I’m trying to resolve this exercises: Visualize the actors who have had more than twice the compensation of the media of their compatriots. FILM (CODFILM,TITLE,YEAR,GENRE) –Year is NUMBER, it contains only the Year ACTOR (CODACTOR,NAME,SURNAME,NAZIONALITY) CAST (CODFILM,CODACTOR, COMPENSATION). I&…
oracle sql, id’s, group by one column, mulitple distinct in other column
I am trying to figure out how to write a query to do this. I have two ID’s in the table ID1 and ID2. Below is what I want. Want ID2 has count(ID2)>1 count of distinct ID1 >1 I am not sure how to do this with Oracle SQL. I did write code to do the group by. However, I
concatenating one to many values to one line separated by commas in sql join
I have a join which shares a one to many relationship to one table. I would to in stead of returning this value: I want to return one line like this: Here is the sql i am running now: How would I end up with the correct output? Answer You need to use a function for that. See LISTAGG EDIT:
Comparing Substring Against Multiple Columns
I have a table which has 20 similar text attribute columns, text1..text20. These columns are of type CLOB. I am looking to find rows where one of these text attribute columns contain a specific phrase, such as ‘%unemployed%’. I need to know 2 things, which rows match, and which column was matched …
why i got error ora-00904 invalid identifier in insert into table?
I have 2 tables MR_FILES and WEBSITE_USERS i need to insert data from first table to secode table but i got the error ora-00904 website_users.patient_no invalid identifier , but the column already exist in the both table with same datatype number(12), this is the SELECT statement : what is the error ? Answer …
SQL concatenate – INSERT MANY VALUES TO ONE ROW
I’m currently trying to add 3 rows in to 1 row so that the information is separated by a ‘,’like using a concatenation. However, I’m new to this and any help will be useful. Below is my current code and what I have tried. This code below does not work: I have also tried this code below…
get null values with decode
I have a table with names, where the name can be null. If I want to get all the rows with null names, I do the following: but I want to make sure that if I specify a value it will get the non-null values and if it won’t get the null values So I get the values that are
Input parameters in procedure
Tell me how to solve such a case: There is a predicate in the stored procedure that searches for input_param by the input parameter: When calling this procedure, I need to pass two or much parameters: It naturally doesn’t work Are there any solutions? Answer Would this help? Sample data: Procedure:
Max analytical function plus windowed ordering is not working as expected
I have a table with status and location, the following is the data. I’d like to get max status partitioned by location using custom ordering. Any idea what needs to change? Right now it is giving only max value, irrespective of the ordering i mentioned. The custom ordering is 1 > 3 > 2 Expected re…