I would like to pass my 1Z0-047 certification, but I don't understand the limitation on the scalar subqueries, especially for the having clause.
Here is my scalar subquery because it returns only one value.
CODEselect avg(list_price)
from product_information
I use it in a having clause as a scalar subquery and it works
CODEselect status ,avg(list_price)
from product_information
group by status
having (select avg(list_price)
from product_information)
>= avg(list_price);
but it is documented that it can't works :
QUOTE There are also important restrictions on scalar subqueries. Scalar subqueries can�t be used for:
Default values for columns
RETURNING clauses
Hash expressions for clusters
Functional index expressions
CHECK constraints on columns
WHEN condition of triggers
GROUP BY and HAVING clauses
START WITH and CONNECT BY clauses
create table test_a (id number, b varchar2 (20)); create table test_b (id number, a number, b number, c number, d number, e number, f number); insert into test_a values (1,'Manu'); insert into test_a values (2,'Tanu'); insert into test_a values (3,'Anu');
[code].....
convert the query above using joins instead of scalar queries, as scalar queries decreasing the performance.
In mys store procedure I am using a subquery with INNER JOIN. This subquery calls a user defined function which takes main query fields as parameter values. But i am facing issue for accessing main query fields. my query is like below:
SELECT ID, Name, sub.Desc, sub.Date FROM MainTable main INNER JOIN ( SELECT * FROM RMF_GetData(main.ID) ) sub ON main.ContactID = sub.ContactID
I need data from a function in a table format based on some case conditions. Hence i need to join it with main table. But here oracle gives error as "invalid identifier" for main.ID parameter.
i was just working on one of my SQL assignments from my database management course, and thus far, this is the first that I just can't figure out. The question is:
Quote: Increase the credit limit of any customer who has any order that exceeds their credit limit. The new credit limit should be set to their maximim order amount plus $1,000. This must be done in 1 SQL statement
The bolded part is what I'm having trouble with.
What I have thus far:
UPDATE Customers SET CreditLimit = 1000 + (SELECT MAX(Amount) FROM Orders, Customers WHERE Cust = CustNum) WHERE CustNum IN ( SELECT Cust FROM Orders WHERE Cust = CustNum AND CreditLimit < Amount);
So there's two tables that I'll be working with, Customers (the table I'm updating), and Orders (the table where the order amount is found). With the code I have so far, it does seem to be updating the correct tables at the very least, but not with the correct values. It's essentially updating the CreditLimit column with the new value of 1000 + the maximum amount in the order table, which is very close to what I want it to do, but I want it to be 1000 + the maximum amount for that specific customer.
CustNum is the primary key for the Customers table, and Cust is the foreign key that links each together.
(about the formatting, it looked much prettier in SQL Worksheet Plus)
I have a scenario where I need to get field name upc_id that do not exist in other subquery.
q-1)WITH INITIALKEYCAT AS (SELECT UPC_ID,SALE_LOCATION_ID,MIN(APPROVAL_DATE) AS INITIALDATE FROM ITEM_KEYCAT WHERE APPROVAL_DATE IS NOT NULL GROUP BY UPC_ID,SALE_LOCATION_ID having MIN(APPROVAL_DATE)>=to_date('2011/01/01', 'yyyy/mm/dd') AND MIN(APPROVAL_DATE)<to_date ('2011/04/01','yyyy/mm/dd'))
[Code]....
Now from Q-2) I want to get UPC_ID that do not exist in UPC_ID of Q-1).How can I write this.
We have multiple environments and our dev and UAT ones are now different from staging and live (I know, but I am not in a position to get this fixed). I have a set of updates that need to go through to live and in some cases they reference rows that do not exist in the UAT environment, and yet they have to (rigid, dumb process) go through that environment.
Basically, the insert I need to do takes info from two tables and does an insert into a third. That target table has a not null constraint on the affected fields, so the insert fails, quite rightly.
There's lots of info available on how to do conditional inserts with single sub-queries, using DUAL and EXISTS (or rather NOT EXISTS, but that's easy to swap), but those don't seem to easily translate for this one.
The sql that works when everything exists is:
insert into wmcontent.wm_manda_corpserv_companies (wm_manda_company_code, wm_corp_company_code) values ( (select wm_company_code from wmcontent.wm_m_and_a_company where wm_company = 'SW'), (select min(oid) from wmcontent.wx_category where content_type = 2 and name = 'SW') );
In desperation I even tried using "log errors reject limit unlimited" but, no doubt due to my misunderstanding of how that works, I ended up getting the error "ORA-06550: line 38, column 1: PL/SQL: ORA-00972: identifier is too long" as a result.
I'm trying to create a query based upon an IAL (I'm using IFS). The IAL contains sales, grouped by month, for each customer. The output of the query should be as follows:
Cust_No Spend in Month Spend in Quarter Spend in Year
My first thought was to have three subqueries, summing data from the IAL where the month of sale was last month, in the last 3 months, and in the last 12 months. Is this the right way to go? And what is the syntax?
Tell me restriction on commit means where this keyword is not used....like i somewhere read in trigger we can't used commit...instead of that we use pragma autonomous_transaction..
but my confusion arise when i see commit used in trigger in our database table....
is commit used in trigger , if not then what will be use...
Another one is commit used while creating procedure or function?
Is there any way to apply the restriction on analytical functions, just like WHERE and HAVING .AS we know that we can apply the restriction on table by using WHERE and grouping functions by using HAVING clause .
For Ex: Departments wise count including all employees record :
SQL> select count(*) over(partition by deptno) dept_Count, ce.* 2 from scott.emp ce 3 order by deptno, job;
DEPT_COUNT EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ----- ---------- --------- ----- ----------- --------- --------- ------ 3 7934 MILLER CLERK 7782 1/23/1982 1300.00 10 3 7782 CLARK MANAGER 7839 6/9/1981 2450.00 10 3 7839 KING PRESIDENT 11/17/1981 5000.00 10 5 7788 SCOTT ANALYST 7566 4/19/1987 3000.00 20 [code]....
I want my user to be restricted for entering duplicate time within two times.
create table asd(dt_frm date,dt_to date);
insert into asd VALUES(to_date('01-04-2012 08:00','dd-mm-yyyy hh24:mi'),to_date('01-04-2012 10:00','dd-mm-yyyy hh24:mi')); insert into asd VALUES(to_date('01-04-2012 09:00','dd-mm-yyyy hh24:mi'),to_date('01-04-2012 11:00','dd-mm-yyyy hh24:mi'));
now in the second insertion I want to alert the entry user that 9am already falls in the saved record which is 8am to 10am and so that this record can't be saved.
I have one question regarding database access. I have one database server on which 3 databases are running. I want to restrict each database access for particular group only , so if anyone outside of this group try to access the database then they can not access the database.
Question is end user never login to Database server and access the database , they always connect to the database using different tool like Pl/sql dev,sqldev etc.
Is there any option through which I can make database access within group only ?
Currently, I am using oracle 10g and getting foolowing error
"PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records"
When trying to access attribute in record-collection inside FORALL. I know its prohibited in 10g but allowed in 11g. My questions is how can we achieve this in 10g. I will rephrase my problem:
I have three record-collections say rc1, rc2 and rc3 and I need to put all these record-collection inside final record collection say FC using FORALL
In my organization, I have a table and in that there is a column named "code".I want to restrict some insertion to that particular column. suppose that code column values are 12 and 1245 then i cant insert the value 12,1245, 1 ,124 and so on but i can insert 2 ,123,15,12456 and so on.
that means the new values should not be any substring of the existing data from left. making that column primary key and then I had a logic to compare the existing value which are longer than the new value and then to perform this.But dont know how to make it happen correctly.
I have an XMLType column that is validated via some XMLSchema. Now I want to remove this restriction and make it just a generic XMLType column. I tried bunch of alter table commands but I couldn't figure out the magic combination. Is this possible?
Here is the actual problem. Unfortunately, we ended up both local and global schemas using the same URL. We have customers out there with both schemas (early customers) or only global schema (new customers). Most of the tables were created before the global schema was added. So, they are referencing the local schema. Now we want to evolve our schema, and I am trying to write a sql script to clean-up this mess. if only global schema exists then (these are late customers)
- do nothingelse if both global and local schema exist then (these are early customers) - If any table or table.column has dependency on local schema (I can find this out from user_dependencies) I am going to mark those tables/columns as no schema validated. i.e. remove all references to local schema - delete and purge the local schema - modify those tables/columns and make them point to the global schema end if - evolve the schema (only global one is left)
Is there a way to modify a column definition and move its reference from local schema to global schema where both schemas have the same URL? Of course the main constraint is that we do not want to loose customer data. By the way, both local and global schemas are identical in terms of xsd.
is it possible to change XMLSchema/Element settings of an XMLType column from X to Y, X to null, or null to X?
i am populating 3 records in my data block.i do not want more than 3 rows.but when i click the plus icon in form menu one more row is getting generated which i do not want. i can restrict the new record addition through plus icon in menu bar for a particular block.
,in 11g,is there a way I could limit the number of open application actions by some os user ?We have an application where users are executing the same thing while the last is not yet finished,so we have several same things runninng at the same time executed by the same user.
Can we restrict that somehow through the database or that needs to be done through application?
