Attached query is running fine if inline view B (Marked in Comments) returning value. If inline view B returns NULL then it fails to return result. I want if Inline view B is returning NULL then it should pass ZERO to main query.
View 2 RepliesI have been using With for many queries for readability. This most recent query seemed to be taking a bit longer to run and I didn't think much of it until a colleague showed me their version (totally different sql statement) which ran faster, but seemed more complicated and had more lines of code. I noticed that the other version did not use with. I moved the sql of the with into the join and the query ran faster dropping from 30 seconds to 798 msecs. The question is am I sacrificing speed for readability by using With, or it really depends on the overall sql statement.
Here is the query using WITH:
with prev as
(
select person_uid, id, name, academic_period,
case when enrolled_ind = 'Y' and registered_ind = 'N' and student_status = 'AS' then 0 else 1 end as enreg_stat,
[Code]....
I am working on this assignment question for class:
Write a SELECT statement that returns a single value that represents the sum of the largest unpaid invoices for each vendor (just one for each vendor). Use an inline view that returns MAX(invoice_total) grouped by vendor_id, filtering for invoices with a balance due
What I have coded so far is below
SELECT i.vendor_id,
To_char(SUM(invoice_total), '$9,999,999.99') AS sum_invoice_total
FROM invoices i
join (SELECT vendor_id,
[code]...
I am getting some results which I have attached. I'm not sure if I'm capturing the "largest unpaid invoices for each vendor" part of the question. Yielding to the knowledge the group and trying to use the proper posting etiquette. I have attached my output screen as well.
the execution steps of inline view?importance&advantage of inline view?.Also reference link where i can get more information about inline view(i.e. basics to advanced),exercises and interview questions.
View 3 Replies View Relatedbelow query, first query is giving proper output i.e. 0 while second is giving wrong output i.e. 1.
Why this happen , what's wrong in using count in inline view ?
selecT count(*)
from (select *
from dual
where 1 = 2
union
select * from dual where 1 = 2);
[Code]....
there is a diff. problem for me.when i create table through inline view then it shows 2246 records but if i check these records only in select statement then it shows 124 records. i cant understand how table shows 2246 records even then atual records in inline view shows only 124 records.
following is a query
create table sam as
select * from
((
select distinct stck.item_code
from (
select item_code,bal
[code]...
I am working on Oracle 10g and Below is my query which is taking 30min. to execute. I am using two inline view and then make joins on these inline view because of i think it Degrade Performance.
Query :-
SELECT LEVEL_USER,
TRIAL_NO,
UNIT_NO,
COUNTRY_CODE,
PERSONNEL_NO,
[code].......
Note :- i have Attached the explain Plan for the same .Please let me know how can i improve performance of this Query.
which one is better in performance point of view and why between inline view and correlated query.
View 5 Replies View RelatedCan I update inline view like below?
update
(select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 55) a,
(select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 3) b
where a.survey_id = b.survey_id and substr(a.question_uid , 3) = substr(b.question_uid, 2))
set b.answer = a.answer;
My second question is: The following query is giving error. What can I do?
update answers ans set (ans.answer) = (with a as (select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 55),
b as (select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 3)
select a.answer from a join b on a.survey_id = b.survey_id and substr(a.question_uid , 3) = substr(b.question_uid, 2) and b.answer_id = ans.answer_id) where ans.main_group_id = 3;
SQL Error: ORA-00904: "ANS"."ANSWER_ID":
Is there a function that allows the following?
select SOME_FUNCTION('N','E','S','W') from dual;
That returns
N
E
S
W
Currently I'm just doing the following
WITH direction AS
(SELECT 'N' dir FROM DUAL
UNION
SELECT 'E' FROM DUAL
UNION
SELECT 'S' FROM DUAL
UNION
SELECT 'W' FROM DUAL)
SELECT *
FROM direction;
Below is the query where I need to filter the data using window function.
I don't want to use inline view for mt query because i need to use the same query in OWB which it wont support.
Select a.Physical_Id,
a.Booking_Begin_Date,
a.Booking_End_Date,
[Code]....
I Cannot use below query in OWB Which is Inline View
----------------------------------------------------------------------------
Select b.* From
(Select a.Physical_Id,
a.Booking_Begin_Date,
[Code].....
After searching a lot on net the differences between inline view and subquery that i came across are
1) you can use order by in inline view but not in subquery.
2) you can use alias for inline view but not subquery.
i have to fetch the same outcome using both the functionality respectively, then what is the technical impact/better.
i am trying to update below statement that has multiple rows but giving the error like :
update test t
set (t.org_id) =
(select o.org_id
from organisation o inner join test t
on (o.name=t.full_name
or o.name=t.chart_name))
error:- single return sub query return more value.
how to write update join query base on multi[ple ow.
Just want to confirm, that where Oracle stores data fetched from inline view.
I am asking, as I am making an inline view on a very large table and its getting failed with TEMP space error.
I'm needing to return results per month counting records that meet a certain criteria. Some months there will be no results but I need to return a zero rather than having that month omitted from the result set.
This is the pre-existing query:
SELECT TO_CHAR(CRSCHED_DATE,'YYYY/MM'), Count(CPMA.RECORDNUMBER)
FROM CPMA.CPMA CPMA
WHERE (CPMA.CRSCHED_DATE Between TRUNC(ADD_MONTHS(SYSDATE,-12),'MM') And LAST_DAY(ADD_MONTHS(SYSDATE,-1))) AND (CPMA.CHGSTATUS='Duplicate')
GROUP BY TO_CHAR(CRSCHED_DATE,'YYYY/MM')
ORDER BY TO_CHAR(CRSCHED_DATE,'YYYY/MM')
The results returned are accurate, but any month(s) with no records meeting the specified criteria are skipped in the result set.
Why does is the code blow reuturning a null for a simple subtraction and division?I am trying to do 4-13/9 for one and 4-22/9 for 22
select unit,
term_1201,term_1202,r_slope,r_intercept,
(r_intercept - term_1201) / decode((r_slope),0,1) as ONE,
(r_intercept - term_1202) / decode((r_slope),0,1) as TWO
[code]...
I have a table with multiple rows for the KEY attribute(its not a primary key) and a Rank for each row.
I want a query which fetches one row per KEY attribute.The row with lesser Rank should be considered. But in-case if the value is null for any column the value for next Rank should be considered.
WITH TMP_TBL AS
(
SELECT * FROM (
SELECT 'A' DUN,'1' RNK,'A21' col1,NULL col2,'A41' col3,NULL col4 FROM dual
UNION ALL
SELECT 'A','2','A122','A23',NULL,NULL FROM dual
UNION ALL
SELECT 'A','3','A32','A33',NULL,'A35' FROM dual
[code].......
DUN is the KEY attribute . RNK is the Rank for each Row. COL1... COL4 are data attributes
The results I am expecting is
DUNCOL1 COL2 COL3 COL4
AA21 A23 A41 A35
BB12 B23 B15
CC12 C13 C33 C14
I want this to be done with SQL only. So I tried various ways but none were successful.Finally I created a Multi Row function row_nvl and it worked.
SELECT DUN,
row_nvl(rownvl_param_type(RNK,col1)),
row_nvl(rownvl_param_type(RNK,col2)),
row_nvl(rownvl_param_type(RNK,col3)),
row_nvl(rownvl_param_type(RNK,col4))
FROM TMP_TBL
GROUP BY DUN
But I don't think my manager will allow me to deploy a Multi Row function .
Im running the following query in DB - Prod2
select *
from t_act_rwrd_reimb_stay a
where reimbursed_amt < 0
and invoice_date = '01-JAN-1900'
and not exists ( select 'x' from t_act_rwrd_reimb_stay b
where a.GPM_ID_STAY = b.GPM_ID_STAY
and reimbursed_amt > 0 )
The above table t_act_rwrd_reimb_stay --> is from a view on the database Prod2 and this view is pulling the data from the database prod1 via dblink
When I run the same query on Prod1 it returns 3 rows. Is there any reason this is not fetching data on prod2 view? or whatz wrong with the above query
or for example if I run the above query as follows with the dblink "@prod1" it returns the data properly
select *
from t_act_rwrd_reimb_stay@Prod1 a
where reimbursed_amt < 0
and invoice_date = '01-JAN-1900'
and not exists ( select 'x' from t_act_rwrd_reimb_stay@prod1 b
where a.GPM_ID_STAY = b.GPM_ID_STAY
and reimbursed_amt > 0 )
I'm having trouble creating a view that has a not null column. Using this script you can see that the resulting table doesn't have a not null constraint for the first column even though both source columns for that row are not null. Is there anyway to force the view to mark that first column as not null? (I need it for ODP.NET otherwise I get an error there)
DROP TABLE MYTABLE;
CREATE table MYTABLE
( COL1 NUMBER(2) NOT NULL,
col2 number(2)) ;
drop table mytable2;
CREATE table MYTABLE2
[code]....
Here is the table structure I will be speaking of below:
SELECT lvl_1_id, lvl_1_dsc,
lvl_2_id, lvl_2_dsc,
lvl_3_id, lvl_3_dsc,
lvl_4_id, lvl_4_dsc,
lvl_5_id, lvl_5_dsc,
lvl_6_id, lvl_6_dsc,
lvl_7_id, lvl_7_dsc,
lvl_8_id, lvl_8_dsc
FROM item_hier_tbl;
I have a table that mimics an hierarchy of items (parent-child relationship). The top level (level 1) never has a NULL value, but it is possible for any of the lower values to have a NULL value. For example, levels 1, 2, 5, 7, & 8 may have values, but 3, 4, & 6 may all be NULL.
I need a View that can return the same data, but with out NULL values. The View would move the NOT NULL values up the levels, so there is consecutive data on each level starting at level. So using the example, the View would move value in 5 up to 3, 7 up to 4, and 8 up to 5. Then level 6 to 8 would have NULL.
I have done this already by creating a 2nd table and using a stored proc to copy the data over to the new table they I want it. But I was told a View or Materialized view "may" work, and perform quicker during the queries. I looked at the Oracle functions LAG and LEAD, but by definition they work by rows, I need to work by columns on the same row.
LAG (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
Is what I am asking possible in a View or Materialized View?
I have a dynamic query stored in a function that returns a customized SQL statement depending on the environment it is running in. I would like to create a Materialized View that uses this dynamic query.
View 1 Replies View RelatedIn Apex 4.2, the item validation of "Function Returning Boolean" and "Function Returning Error Text"; They seam to be backwards.
Is there a simple statement that can be used to fix this in the apex dictionary?
I have the following query:
select col_1,col_9 from
book_temp b
where b.col_1 is not null
order by to_number(b.col_16)
;
What I want to add is the following:
COL_9
=====
NULL
A
B
NULL
C
D
E
F
NULL
G
I need to connect the NON-NULL rows to the preceding NULL row.
we're having a few tables which queries about 10.000 articles. As we don't show them all at once we are using pagination and use the rownum to show only a limited number of the results.
Now as these queries are pretty complex we have to optimize them and since we use pagination we have to call our query twice (first we make a count(*) and then we call the small resultset of a few rows). Ofcourse we are looking for a solution to call it only once and still use the pagination. We could load the whole resultset of 10.000 results and let java show only a few but that makes our line between the oracle and webserver pretty heavy. Is there a way to call the total number of results and give back only a small resultset just in one query?
When to use inline query and to use join.
View 3 Replies View RelatedI am running a GROUP BY query on a few columns of enumerated data like:
select count(*), Condition, Size
group by Condition, Size;
COUNT(*) CONDITION SIZE
-------- ---------- --------
3 MINT L
2 FAIR L
4 FAIR M
1 MINT S
Well, let's say I also have a timestamp field in the database. I cannot run a group by with that involved because the time is recorded to the milisec and is unique for every record. Instead, I want to include this in my group by function based on whether or not it is NULL.
For example:
COUNT(*) CONDITION SIZE SOLDDATE
-------- ---------- -------- ----------
3 MINT L ISNULL
2 FAIR L NOTNULL
2 FAIR M NOTNULL
2 FAIR M ISNULL
1 MINT S ISNULL
I have a table which has a not null column. the column is date field. I am trying to change it to Null. But it is giving a error.
I am using below query.
ALTER TABLE T_test
modify (paid_to_date null)
how joins work with in-line views.I have a query and its explain plan as below:
SELECT e.ename,e.deptno,d.dname FROM
dept d,
emp e
WHERE e.deptno=d.deptno
AND e.deptno=20
[code]....
I do not find any difference in both the explain plans. Both are same. In my second query, the filtered rows will be joined to dept table. And hence the baggage will reduce.But how can I verify that in-line view has worked better?
I have created a few flash charts using APEX4.2. Is there a way for the user to view the chart and then email it as an inline image to someone? I know that the user can save the chart as PDF file, then send it as an attachment via Outlook ( or similar system), but can I integrate these manual steps to one ( template), similar to the IR report's download feature ?
View 0 Replies View Relatedcreate table test
(
id int ,
dat date
)
/
I want to implement a business rule such as we have for each id at most 1 dat null. So, I've created this unique index on test.
create unique index x_only_one_dat_cess_null on test(id, case when dat_cess is null then 'NULL' else to_char(dat_cess, 'dd/mm/yyyy') end);
insert into test values (1, sysdate);
insert into test values (1, sysdate - 1);
insert into test values (1, null);
insert into test values (1, null);
-- -----
insert into test values (2, sysdate);
insert into test values (2, sysdate - 1);
insert into test values (2, null);
The 4th insert will cause an error and this is what I wanted to implement. OK. Now the problem is that for non-null values of dat, we can't have data like this
iddat
------------
124/10/2013
123/10/2013
123/10/2013
1
because of the unique index (the 2nd and the 3rd row are equal). So just for learning purposes, how could we allow at most one null value of dat and allow duplicates for non-null values of dat.