I have a requirement in SQL that I have to number each row. Hence I thought of using ROWNUM. But the sql query I'm using uses UNION operator. Hence I used like this
select a,b,rownum as 'field1' from table1 union select c,d,1 as 'field1' from table2
I have a requirement to list the data month wise dynamically where month data is also in the same table, hopefully the below posts should bring more clarity to my requirements.
Using 11gR2, windows 7 client machine. I need to update the table missing_volume (below), where I need to calculate the estimated_missing column. The calculation of estimated_missing column for current month needs previous month numbers (as commented inside the code below). I want the output like the first table. Notice the records start from January, hence estimated_missing for January can't be calculated, but for the the rest of the months it can be done by simply changing 'yr' and 'mnth' (commented inside the code towards the end).
yr mnth location volume actual_missing expected_missing estimated_missing --------------------------------------------------------------------------------------------------------------------------------- 2013 January loc1 48037 24 57 2013 February loc1 47960 3660 53 24 2013 March loc1 55007 78 57 28 2013 April loc1 54345 72 58 77The code:
UPDATE missing_volume g
[Code]....
The code does calculate correct number for 'estimated_missing' as I run the code for each month, but the problem is while updating the current month it also erases the record for previous month. E.g. as can be seen below, after I updated April the column only has the record for April, previous month record is gone, similarly updating March removed February, etc. I can't understand why it's happening!! Here is the output I get:
yr mnth location volume actual_missing expected_missing estimated_missing --------------------------------------------------------------------------------------------------------------------------------- 2013 January loc1 48037 24 57 2013 February loc1 47960 3660 53 2013 March loc1 55007 78 57 2013 April loc1 54345 72 58 77
why it's happening (I mean where is the flaw in the code) and how to get the desired output (first table).
I need for each date sum the values from the begin of the year to present date. In January I will have the value of this month, on February I must sum the value of this month and the value of the month before, and so on, at the end of the year.
Date input
SELECT ID_CLIENT, DT_REG, VAL FROM ( SELECT 1 as ID_CLIENT, TO_DATE('20100101', 'YYYYMMDD') as DT_REG, 200 as VAL FROM DUAL UNION SELECT 1 as ID_CLIENT, TO_DATE('20100201', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION SELECT 1 as ID_CLIENT, TO_DATE('20100301', 'YYYYMMDD') as DT_REG, 200 as VAL FROM DUAL UNION SELECT 1 as ID_CLIENT, TO_DATE('20100401', 'YYYYMMDD') as DT_REG, 150 as VAL FROM DUAL UNION SELECT 1 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION SELECT 2 as ID_CLIENT, TO_DATE('20100101', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION SELECT 2 as ID_CLIENT, TO_DATE('20100301', 'YYYYMMDD') as DT_REG, 220 as VAL FROM DUAL UNION SELECT 2 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 500 as VAL FROM DUAL UNION SELECT 3 as ID_CLIENT, TO_DATE('20100201', 'YYYYMMDD') as DT_REG, 150 as VAL FROM DUAL UNION SELECT 3 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL);
I need to get create_user_id for different sale_location_id.Also create_user_id field will be having different values.This is part of my big query.I need to add this stmt in that.So taken that part and figuring it out.
create table it(sale_location_id number,create_user_id varchar2(10)); table IT created. insert into it values(1,'ISRA') 1 rows inserted. insert into it values(2,'USFA') 1 rows inserted.
select a.sale_location_id,decode(a.sale_location_id,1,a.create_user_id like 'IS%',a.create_user_id like 'U%') create_user_id from it a
given error as:
ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis"
I have a table in SQL , I am creating a column Of name in it , i want to restrict user to enter name in Capital only ,and i want to create this at table level . I tried Check Operator but failed .
1) Can we set a different symbol other than '' for escape operator. 2) If yes, how to see the current escape operator symbol. 3) How to find out the below name with escape operator?
Query - SELECT * FROM sysadm.ps_tmtl_post_vw a WHERE a.month_prepared_for = 'JUNE,2012' AND a.ca_status = 'P5 CUST GO AHEAD'
[code]...
When I try for the SQL-Tuning sets its throws error that
ADDITIONAL INFORMATION SECTION ------------------------------------------------------------------------------- - The optimizer could not merge the view at line ID 2 of the execution plan. The optimizer cannot merge a view that contains a set operator.
I read earlier forum where it says that optimizer unable to interpret the conditions like order by etc etc.Now there is one view which is getting used in the query when I did select * from vw it took more than 16 hrs to complete. (bad view).
Attached File(s)
exec_plan.txt ( 2.06MB ) Number of downloads: 1 view_def.txt ( 14.12K ) Number of downloads: 2
Want to understand difference between Concat function and "||" operator. I am getting the same result for both. Below is the test case for your reference.
Select 'H '||' S' From Dual; --Output H S
Select Concat('H ',' S') A From Dual; --Output H S
Select Length('H '||' S') A From Dual; --Output 6
Select Length(Concat('H ',' S')) A From Dual; --Output 6
I want to link to blocks using description as there is no relation , for example i have two tables with one field in common called description, and i want to link this field in two tables using like operator.
create table item ( item_code varchar2(12),item_name varchar2(30)); insert into item VALUES('A','HEA160'); insert into item VALUES('B','HEA180'); create table stk (sl_item varchar2(12),sl_desc varchar2(30),sl_qty number); insert into stk VALUES ('X','HEA160X1000',12); insert into stk VALUES ('X','HEA160X2000',4); insert into stk VALUES ('Y','HEA180X3000',10);
Suppose i click on item block item_desc with value on HEA160 all the items similar to that should appear in stk block like 'HEA160X1000' ,'HEA160X2000' , if i click on 'HEA180' on item then 'HEA180X3000' it should come.
I am using the following query with like 'T_%', i am getting 80 rows out of which the first table_name doesn't even have a beginning part 'T_%'.
the first table name has not started with 'T_', why is it appearing.
********************************************************************* SELECT 'Truncate table epic500.'||table_name FROM user_tables where table_name like 'T_%' order by table_name; ********************************************************************* output: Truncate table epic500.TEMP_ENC_DEL Truncate table epic500.T_ACCOMMODATION_CODE
Whey I try to run this query in TOAD I get an ORA-00920: invalid relational operator error. It's part of a 10g stored procedure. When I highlight it and run it it prompts me for the missing values and then the error pops up. The AND in line 4 is highlighted.
select CRIME_CLASSIFICATION_ID, crime_type, nvl(count(CRIME_CLASSIFICATION_ID),0) as CRIMECNTFrom vaps.vw_offenses where regexp_instr(valoc,to_char(location_id)) AND ( fromdate is null or offense_date between to_date(fromdate, 'mm/dd/yyyy') AND to_date(todate,'mm/dd/yyyy'))group by crime_classification_id, crime_type
I am running this query but am not getting data that is correct.
SELECT a.prod_id, a.prod_name, a.artist_name, COUNT(*) FROM po_my_purchase_tb a, cm_track_tb b WHERE a.prod_id = b.prod_id and b.GNR_CD = 'GR000017' AND a.purchase_date > '10-FEB-10' AND ROWNUM<50 GROUP BY a.prod_id, a.prod_name, a.artist_name, a.buy_seq ORDER BY COUNT(*) desc
Suppose I have a table in which I have first_name, last_name, dob. Now I have to fetch on the basis of first_name=some_value, last_name=some_value and dob=some_date. I want to sort it on the basis of exactly fetched values. Let me take an example-
test table contains-
first_name last_name dob ---------- --------- ---- Manu Batham 02-Feb-1988 Manu Sharma 01-Jul-1987 Avinash Pandey 03-Feb-1988 Ankit Gupta 02-Feb-1988 Manu Aggrawal 02-Feb-1988 Manu Batham 20-Jan-1985 Sikha Batham 17-Apr-1988
first_name last_name dob ---------- --------- ---- Manu Batham 02-Feb-1988 Manu Aggrawal 02-Feb-1988 Manu Batham 20-Jan-1985 Manu Sharma 01-Jul-1987 Ankit Gupta 02-Feb-1988 Sikha Batham 17-Apr-1988
My result is based on the approach- if matched first_name, last_name, dob --> 1st prefrence in order if matched first_name, dob --> 2nd prefrence in order if matched first_name, last_name --> 3rd prefrence in order if matched last_name, dob --> 4th prefrence in order if matched first_name --> 5th prefrence in order if matched last_name --> 6th prefrence in order if matched dob --> 7th prefrence in order
I designed the following query for the same-
Select first_name,last_name,dob,1 "Order" from test Where first_name='Manu' and last_name='Batham' and dob=to_date('02/02/1988','dd/mm/yyyy') union Select a,b,c,2 from test Where [code]......
I know that this is not the best possible solution as the table is very big and doing so many hits on that table will certainly decrease the performance.
