SQL & PL/SQL :: Create Select Query That Will Get Data From 18 Months Ago Based On Sysdate?

Sep 9, 2013

I have a table with columns job_id, jan, feb, mar ... , and year

I need to create a select query that will get the data from 18 months ago based on sysdate.

So something like:

Select to_char(add_months(sysdate, -18),'MON') from table1 where job_id = 56947 and year = to_char(add_months(sysdate, -18),'YYYY');

However I need the result of to_char(add_months(sysdate, -18),'MON') to actually act as a column name, not a string result.

View 23 Replies


ADVERTISEMENT

SQL & PL/SQL :: Taking Months Off Sysdate?

Feb 25, 2010

I have a table called transaction_dw and I need to select all records that have an account balance that has been below 0 in the past 6 months. initial query I tried was:

select account_balance, timestamp
from transaction_dw
where account_balance < 0
and timestamp between sysdate and sysdate - 6;

but this is only taking 6 days off the sysdate rather than months, how I can get it to take off 6 months?

View 3 Replies View Related

SQL & PL/SQL :: Create Materialized Views Depending On Sysdate With Enable Query Rewrite Option

Mar 28, 2011

I want to create a materialized view for the last 10 days with the enable query rewrite option.

e.g. i want to create a view with the list of employees who joined the company in the last 10 days.

create materialized view M_Employee
refresh fast on commit
enable query rewrite as
select joining_date , name
from
employee
where joining_datde < TRUNC(sysdate) - 10

I seem to get the error SQL Error: ORA-30353: expression not supported for query rewrite

30353. 00000 - "expression not supported for query rewrite"
*Cause: The select clause referenced UID, USER, ROWNUM, SYSDATE.

This error is self explanatory , but is there any work around to have a query liek this to list the employees based on sysdate.

View 23 Replies View Related

Application Express :: Select Some Data From Table Base On SYSDATE?

Sep 6, 2012

I'm trying to select some data from table base on SYSDATE. The below query does not return any data.

My query is:

select count(TICKET_ID) "ECEMEA" from QTMT_TICKETS where STATUS_ID=1 and TEAM_ID=3 and RECEIVED_DATE=sysdate

why this does not work? Date format in my application is: DD-MMM-YY (16-AUG-12). In the database the dates are stored in this format: MM/DD/YYYY (08/25/2012). Does it mean, that I have to play with the format?

View 6 Replies View Related

SQL & PL/SQL :: Getting Query For Current Year Data Up To Sysdate?

Jun 19, 2012

I have a table for exampl,

emp sal date
111 200 03-mar-2011
100 200 03-mar-2012
15 200 06-mar-2012
17 200 03-mar-2003
178 200 03-mar-2004
11 200 11-jun-2012
101 200 19-jun-2012

i need sql querry to get current year records upto sysdate.i.e for example this year 2012 so i need all the 2012 year records upto sysdate.

my output like this.....

emp sal date
100 200 03-mar-2012
15 200 06-mar-2012
11 200 11-jun-2012
101 200 19-jun-2012(upto sysdate)

View 2 Replies View Related

SQL & PL/SQL :: Select Query To Get Top Two Rows Based On Status Date

Oct 29, 2012

I want to get top two rows based on ACCT_UNIT & order by status_date, if there is only one row on acct_unit, get one row. IF more than two rows available, want to get the top two rows based on status_date.

SELECT ACTIVE_STATUS, ACCT_UNIT, DESCRIPTION, DIRECTOR, DIRECTOR2, STATUS_DATE, OBJ_ID, STATUS_FLAG, SUR_KEY
FROM STSI

View 10 Replies View Related

Application Express :: Tabular Form (select List Query Based LOV)

Jul 10, 2012

I have this situation:

a tabular form with two "select list (query based LOV)" fields. The first one must affect elements in the second one. And so the second query based LOV field should has a "where attr1 = first_list_selected_value" in the query.

View 3 Replies View Related

SQL & PL/SQL :: Conditional Select - Query To Returns Results Based On Both City And Country Passed

Sep 17, 2010

Table A

Id Country city
1 US
2 US Boston
3 Boston
4 US Newyork
5 London
6 Japan Tokyo

Im looking for a query which returns results based on both city and country passed.

If i pass country US and city Boston it should return row2 with US and Boston row
If i pass country null and city Boston it should return row3
If i pass country UK and city Boston it should return row3
If i pass country UK and city London it should return row5

i.e. If country/city combination exists in DB return that row Else city row should be returned.

View 5 Replies View Related

Select Data Based On Date

Apr 27, 2010

I have a script which is used to run a job based on the users choice. For example: I have two table, Files and Requests

User select the files to be executed for each request. This data will be stored in Requests table.

Table 1: Files
files
======
file-1
file-2
file-3
..
..
file-n

Table 2: Requests

request file lup_date
==================================
request-1 file1,file2,file3 04-JAN-2009
request-2 file1,file4,file5 06-JAN-2009
request-3 file6,file2 021-JAN-2009
request-4 file1,file2 04-FEB-2009
request-5 file1,file2 08-JAN-2009
request-6 file1,file2 04-MAR-2009
.......... ........... ................
request-n-1 file6,file2,file4 04-DEC-2009
request-n file6,file3,file4 04-DEC-2009

how to get the output in below format. Count how many times each file is selected in a month.

Output format should be like below..
==============================================
File_Name Jan Feb Mar Apr ---------- Dec
==============================================
file1 2 1 3 0 ---------- 2
file2 1 0 2 1 ---------- 3
file-n 8 2 3 0 ---------- 2

View 2 Replies View Related

SQL & PL/SQL :: Show Or Select Data Based On 2 Criteria

Mar 23, 2013

I have a complex requirement to be resolved, i have one table of quantities from where i want to show or select the data based on 2 criteria.

1) if the ps_qty is greater than 1000 then there should be two lines , like the qty should not be displayed greater than 1000, instead it should be lesser than 1000.
2) The weight should not be more than 50,000, it should be less than 50000 if its more it should be displayed as 2 lines.

The following is the test case.

CREATE TABLE OW_STAG_SHIP (PS_CODE VARCHAR2(12),PS_DESC VARCHAR2(30),PS_QTY NUMBER,PS_WT NUMBER);
INSERT INTO OW_STAG_SHIP VALUES ('A','AAA',400,30000); -- this will be displayed as its because qty and wt are ok
INSERT INTO OW_STAG_SHIP VALUES ('B','BBB',1100,4000); --Need to be displaed in two lines as qty is more than 1000
INSERT INTO OW_STAG_SHIP VALUES ('C','CCC',2500,6000); --Need to be displayed in three lines as qty is more
INSERT INTO OW_STAG_SHIP VALUES ('D','DDD',600,60000); --Need to be displaed in two lines as wt is more.
select * from ow_stag_ship

PS_CODEPS_DESCPS_QTYPS_WT
AAAA40030000
BBBB11004000
CCCC25006000
DDDD60060000

Output what i want is as below

PS_CODEPS_DESCPS_QTYPS_WT
AAAA 40030000
BBBB10002000
BBBB 1002000
CCCC10002000
CCCC10002000
CCCC 5002000
DDDD 500 50000
DDDD 100 10000

View 16 Replies View Related

Create Table From Select Query?

Sep 13, 2004

Can we create a table from a Select query ?

View 5 Replies View Related

SQL & PL/SQL :: Create A Function Which Can Be Used In A Select Query?

Jun 11, 2008

I have a table structure like :-

Create table test(A varchar2(50),B NUMBER);

The data in that table is like that:-

A B
----------------------
2*3
2*4*5
4*5
column B contain no data.

I want to create a function which can be used in a select query,and the output should come like that :-

A B
----------------------
2*3 6
2*4*5 40
4*5 20

Means column B contains the resultant value of column A.And the above output should come through a select statement.You can use any function inside the select statement.

View 20 Replies View Related

SQL & PL/SQL :: Create Nested Query Within Select Of Attributes

Dec 21, 2011

I'm trying to create a sort of nested-query within my select of attributes . i.e.

select A.a,
B.b,
(select count(C.*)
from C
where C. = B.d
group by C.y)
from A a,
B b
where A.d = B.d
and ...

Over-simplifying my query:

select B.desc "Location",
F.desc "Source",
A.amt "Amount",
sum(G.G_CNT) "No. Units",
c.desc "Status"
[code]....

I need to incorporate a count of the number of units from TableG that have a certain status. I tried the following but when I tried to run it, I get an error saying that it's not a Group By expression -the red part is highlighted in TOAD.

select B.desc "Location",
F.desc "Source",
A.amt "Amount",
sum(G.G_CNT) "No. Units",
(select count(*)
from TableG G2
where G2.D_ID = D.ID
and G2.status = 10
group by G2.D_ID)"Count",
c.desc "Status"
[code]....

Any thoughts how I can incorporate a query in my select of attributes?how to Group By something.

View 8 Replies View Related

PL/SQL :: Create A Procedure For SELECT Query - Error?

Nov 1, 2013

how do I create a procedure for a SELECT query like the following?

When I create a procedure; I get an error "Error(80,1): PLS-00428: an INTO clause is expected in this SELECT statement" PROCEDURE  MyProcISBEGINselect 'Dakota' as ALIAS      ,A.StartDate      ,B.EndDatefrom Customer A    ,Clients bwhere  a.cType = b.cTypeand b.Active =0ORDER BY StartDate, EndDateEND  MyProc;

View 17 Replies View Related

SQL & PL/SQL :: Select Sysdate From Dual

Aug 30, 2012

when i run this nls qusery i got this error

E:oracleproduct10.2.0db_1BIN>sqlplus

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Aug 30 11:45:59 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select value from v$nls_parameters where parameter= 'NLS_DATE_FORMAT';

VALUE
----------------------------------------------------------------
DD-MON-RR

SQL> select sysdate from dual;

SYSDATE
----------------
30-????? -12

View 11 Replies View Related

PL/SQL :: Select Time From Sysdate

Sep 20, 2013

I have to concatenate a date(not sysdate) with the system time and store it in a column having datatype as DATE... So I tried like this....

SELECT TO_CHAR (C_DATE, 'DD-MON-YYYY')      || ' '    || TO_CHAR (SYSDATE, 'HH:MI:SS PM')    FROM   DUAL; 

But while inserting the output of the above query in my table it throws error like this... ORA-01830: date format picture ends before converting entire input string Is there any possibility to achieve this.

View 6 Replies View Related

Application Express :: LOV Select List / How To Create Query With Begin And End

Jun 11, 2012

i am using Apex 3.2 ver.

i want to use below code in LOV select list

BEGIN
IF  UPPER(:P23_SERVICE_TYPE) like 'GUIDE%' THEN
SELECT NAME D, CODE R FROM SPECIAL_SERV_MAS
WHERE NVL(ACTIVE_FLG,'N') = 'Y'
AND NVL(GUIDE_FLAG,'N') = 'Y'
and CITY_CODE LIKE NVL (:P23_CITY_CODE, '%')

[code]....

When i put this code in my LOV Select list Section then display me Error

Not Found The requested URL /pls/apex/f was not found on this server.

Oracle-Application-Server-10g/10.1.2.0.2 Oracle-HTTP-Server Server at tidevserv1 Port 7777

View 3 Replies View Related

SQL & PL/SQL :: Building Correlated Subquery Select Statement By Aggregating Timestamps To Months?

Jun 11, 2010

I have 3 tables, user_login_event, person and resource_viewed_event. What I want to do have a report for each month, users logged in our application and then show for each month, how many records were created in table person and how many resource views events were logged in resource_viewed_event.

Lets only worry about the timestamp fields in these tables now as I want to use them to join the tables together or at least build correlated subqueries along the months. I have tried several options, all not leading to a desired result:

Left outer join. Works but its incredibly slow:

SELECT
distinct to_char(ule.TIMESTAMP,'YYYY-MM') as "YYYY-MM",
count(distinct ule.id) as "User Logins",
count(distinct ule.user_id) as "Users logged on",
count(distinct p2.id) as "Existing Users",
count(distinct p1.id) as "New Users",
count(distinct r1.id) as "Resources created"

[code]....

Tried the same with left outer joins of temporary tables created through select statements:

select
distinct ule.month as "Month",
count(distinct p1.user_id) as "Users created",
count (ule.id) as "Logins",
count (distinct ule.user_id) as "Users logged in",
count(rv.id) as "Resource Views",
count(distinct rv.resource_id) as "Resources Viewed"

[code]....

Tried the same with left outer joins of temporary tables created through select statements:

select
distinct ule.month as "Month",
count(distinct p1.user_id) as "Users created",
count (ule.id) as "Logins",
count (distinct ule.user_id) as "Users logged in",
count(rv.id) as "Resource Views",
count(distinct rv.resource_id) as "Resources Viewed"

[code]....

another approach is to create my own temporary tables using select statements and create fixed Month values which I can use to directly link the sets together.

select
distinct ule.loginday as "Month",
count(distinct ule.id) as "Logins",
count(distinct ule.user_id) as "Users logged in",
count(distinct p1.user_id) as "Users created",
count(distinct p2.user_id) as "Existing users1"

[code]....

performance is OK with 2 tables but the example above takes forever to execute.

Tried an approach with union but this creates new rows for each table

SELECT DISTINCT p1.MONTH AS "Month",
COUNT(DISTINCT p1.user_id) AS "Users created",
NULL AS "Logins",
NULL AS "Users Logged in",
NULL AS "Resource views",
NULL AS "Resources viewed"
FROM (SELECT To_char(person.created_on_date, 'YYYY-MM') AS MONTH,

[code]....

View 4 Replies View Related

Application Express :: Select List That Displays Next 12 Months Worth Of Mondays

Aug 24, 2012

I recently needed a select list that would allow you to choose from a years worth on Mondays starting with the current week's Monday. It will return the date of that Monday. I thought others might be able to use this too... Plus I wanted to be able to find this in the future.

SELECT DISTINCT
to_char(trunc(next_day(trunc(SYSDATE,'day'),2)-1+LEVEL,'iw'),'FMMonth DD, YYYY') AS Display
,trunc(next_day(trunc(SYSDATE,'day'),2)-1+LEVEL,'iw') as Return
FROM dual
CONNECT BY LEVEL <=((add_months(trunc(SYSDATE,'day'),12)-1)-(trunc(SYSDATE,'day')))
ORDER BY 2;

As of today (Friday, August 24th) the above would return:

DISPLAY RETURN
August 20, 2012 20-AUG-12
August 27, 2012 27-AUG-12
September 3, 2012 03-SEP-12
September 10, 2012 10-SEP-12
September 17, 2012 17-SEP-12

[Code]....

View 2 Replies View Related

PL/SQL :: How To Get All Months With SQL Query

Aug 8, 2013

I want to display all months (JAN till DEC) in 1st column and their counts in 2nd column but as there are no records in JAN, FEB and march, so they are not coming up.

Get JAN, FEB etc with counts as 0?I know it can be done with NVL function but I don't know where to put it. I'm using oracle 10g on Windows XP. [code]SELECT '',    

CASE      WHEN b.mon= '01' THEN 'JAN' WHEN b.mon= '02' THEN 'FEB' WHEN b.mon= '03' THEN 'MAR'      WHEN b.mon= '04' THEN 'APR' WHEN b.mon= '05' THEN 'MAY' WHEN b.mon= '06' THEN 'JUN'      WHEN b.mon= '07' THEN 'JUL' WHEN b.mon= '08' THEN 'AUG' WHEN b.mon= '09' THEN 'SEP'      WHEN b.mon= '10' THEN 'OCT' WHEN b.mon= '11' THEN 'NOV' WHEN b.mon= '12' THEN 'DEC'    END Months, count(*) cnt  FROM    (SELECT con.fst_name first_name, con.last_name, usr.login USER_ID,        app.appl_src_cd Registration_Source,to_date(usr.created,'DD-MON-YY') Created_Date,      TO_CHAR(usr.created,'MM') mon      from siebel.s_user usr, siebel.s_contact con, siebel.S_PER_PRTNRAPPL app      where con.par_row_id = usr.row_id      and app.row_id = con.row_id      and app.appl_src_cd = 'Siebel eService'    AND TO_CHAR(usr.created,'YYYY') = :P415_YEAR    ) bgroup by b.monorder by 3 desc[/code] 

Right now getting output as....MONTHSCNTAPR4818JUL4543JUN4295MAY4190AUG541MAR20 What Jan, Feb, March etc in it too with count as 0.

View 3 Replies View Related

Reports & Discoverer :: Select Weeks Number Per Months And Year As 1 To 52 On Matrix Report 6i?

Aug 27, 2010

wanna to make matrix report retrieve months year and the number of weeks from dual table it supposes to the weeks number is 52 or 53 week months and weeks on the rows please find attach pic it show what i need to do for exmple

month name: January February March April May June July August September Nov October December
no of week : 1-2-3-4-5-6-7-8-9-10-11-12-13-14-15-16-17-18-20-21-22....... TO 52 week

View 5 Replies View Related

SQL & PL/SQL :: Find Logic To Query Data Based On A Condition

Aug 18, 2013

find the logic to query data based on a condition like..I am having a table dummy

USERNAME RESP_NAME FUNC_NAME MESSAGE
-------- --------- ------------------ ------------
TEST1 SYS_ADMIN CONCURRENT_PROGRAM AAAAAAAAAAAA
TEST1 SYS_ADMIN % BBBBBBBBBBBB
TEST1 % CONCURRENT_PROGRAM CCCCCCCCCCCC
TEST1 % % DDDDDDDDDDDD
% SYS_ADMIN CONCURRENT_PROGRAM EEEEEEEEEEEE
% SYS_ADMIN % FFFFFFFFFFFF
% % CONCURRENT_PROGRAM GGGGGGGGGGGG
% % % HHHHHHHHHHHH

The above 8 are the possible ways to key in the data using a form.The logic is like based on a particular condition the message should display in the report.If they specify a particular USER and RESP_NAME and FUNC_NAME then it should display AAAAAAAAAAA (This should take priority first). The priority is in order the default should be the last one.

View 22 Replies View Related

PL/SQL :: Query To Total Attendance By Months

Jul 21, 2012

I need the following totals for each student based on the schools that they are enrolled for each month July through June.

Total Days Suspended from each school

Attendance Table Columns - Absent Date, Absent Code, Pupil_Number, School (Absent Codes for suspended = 3)

Total Days Present at each school - Days Enrolled at each school minus Days Suspended minus Absences

Totals Takes the first day that a student is enrolled (counts the days that the school is in session and the student is not marked absent) Student must be enrolled during the time frame for that month.

Total Days Absent - Total days absent minus suspensions

Examples
Student enrolls in school 11111 on 7/15/2011
Student withdraws from School 11111 on 11/15/2011
Student enrolls in school 22222 on 11/20/2011

Total Days Present should be totaled for each student for each month that they are in a school.

I need 1 row of data for the above student for each month that contains total days suspended, total days present and total days absent.
Pupil Number School Month Totals Days Suspended Total Days Absent Total Days Present

1234 11111 07 0 0 0
1234 11111 08 0 0 4 (school days 4)
1234 11111 09 3 1 16 (schools days 20 - 3-1)
1234 11111 10 0 1 18 (school days 19 - 1)
1234 11111 11 1 1 7 (student enrolled at school A 9 days -1-1)
1234 22222 11 0 1 10 (student enrolled at school B 11 days -1)
1234 22222 12 1 2 17 (school B 20-1-2)
1234 22222 01 0 0 20

View 2 Replies View Related

SQL & PL/SQL :: Create Trigger To Change Every Insert In Table To Sysdate

Feb 17, 2011

I have table 'A' with column 'ID','NAME','IN_DATE','PHONE','EMAIL'

Now I have to create a trigger such that on every insert in the table 'A' the value of column 'IN_DATE' changes to sysdate.I m not good in PL/SQL

View 4 Replies View Related

SQL & PL/SQL :: Last 6 Months Data

Jan 5, 2011

I need query to fetch last six months dyanamically based on the mon-yyyy passed by user

Input to query-JUN-2010

Result-
JAN-2010
FEB-2010
MAR-2010
APR-2010
MAY-2010
JUN-2010

View 3 Replies View Related

SQL & PL/SQL :: Printing Months Even Having No Data

Feb 26, 2012

I have table data in which we are entering Visitor's information.We need a monthly report with the count .There are some months which dont have data .So Names of month are not coming .Can i Print Month Name with 0 visitor.

Sample Data

Quote:ID Name Visit_date
1 ABC 01/02/2011
2 DEF 03/04/2011

im using query

Quote:SELECT count(ID) Nos ,TO_CHAR(visit_date,'MONTH') Month FROM DELEGATE_DETAILS WHERE visit_date BETWEEN '01/02/2011' AND '01/04/2011'
GROUP BY TO_CHAR(ARR_DATE,'MONTH')
ORDER BY TO_CHAR(ARR_DATE,'MONTH');
Output
Quote:Nos Month
1 February
1 April

Desired Output:-
Quote:Nos Month
1 February
0 March
1 April

View 2 Replies View Related

PL/SQL Hierarchical Query - Select Data In Specific Way

Apr 19, 2013

I have a table that has hierarchical data within it. I need to select this data in a specific way, showing the hierarchy.

E.g. Data in table (Key is unique)

Lvl KeyParKey HasChild
1k101
1k200
1k301
1k401
2k34k10
2k22k11
2k24k10
2k13k30
2k52k30
2k35k30
2k13k30
2k11k40
3k56k220
3k109k221
3k67k220
4k61 k1090
Etc etc....

That�s generally the format the values would appear in the table if I just did a standard select. I want it displayed in a more hierarchical Parent � child way.

The format I need to get out is as follows:
Lvl KeyParKey hasCh
1k101
2k34k10
2k22k11
3k56k220
3k109k221
4k61 k1090
3k67k220
2k24k10
1k200
1k301
2k13k30
2k52k30
2k35k30
2k13k30
1k401
2k11k40

View 1 Replies View Related

SQL & PL/SQL :: Select Statement - Inner Query Returns Data

Aug 23, 2012

I am new to oracle. I am creating a view, where in the inner query returns below kind data.

column1 column2
-------------------
a y
a y
b y
b n
c y
d n

I want to extract all column1 values which always has "y" in column2.

In this case, output has to be

output
------
a
c

View 10 Replies View Related

PL/SQL :: Hierarchical Query To Select Data From One Table?

Sep 7, 2012

Here is my case,

create table t (id number,row_id number primary key ,value number);ID   row_id value
1     1     10
1     2     11
1     3     1
2     4     11
3     5     11
3     6     12
4     7     12
4     8     12
4     9     13
4     10     11
4     11     10Requirement is

1) To get all the ID that have the value 10

2) The row_ids for the ID I got in I

Can I do this with Hierarchical query. If not which one of below will be fast?

select * from t where id  in (select id from t where value=10);

or

select T2.* from t T1, t T2 where T1.ID=t2.id and T1.VALUE=10;

I have billion of rows so I am looking for either Hierarchical query to solve it or some other way . All the three column the index.

View 5 Replies View Related

SQL & PL/SQL :: Select Query Like Operator Getting Wrong Data

Dec 25, 2012

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

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved