SQL & PL/SQL :: Query To Break Date And Connect By Level

Nov 7, 2012

I have following table

CREATE TABLE MAMALIK.DTE
(
FRM_DTE DATE,
TOO_DTE DATE
)
Insert into DTE
(FRM_DTE, TOO_DTE)
Values
[code]........

Result is

FRM_DTE TOO_DTE

01/07/201201/07/2012
03/07/201207/07/2012
11/07/201215/07/2012
31/07/201201/08/2012

i want to write query which should return data as

01-jul-2012
03-jul-2012
04-jul-2012
05-jul-2012
06-jul-2012
07-jul-2012
11-jul-2012
12-jul-2012
13-jul-2012
14-jul-2012
15-jul-2012
31-Jul-2012
01-Aug-2012

i want to generate dates between frm_Dte and too_dte in single column

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: Query To Replace Command (break On Column Name)

Apr 29, 2010

I dosn't want to use the command break on <column name> / how to write sql to replace the break command.

View 8 Replies View Related

Connect By Level Using Field From Record Instead Of Constant?

May 20, 2011

I am generating a start date, stop date, count and frequency from data in one of my tables.

The result is as follows:

Id,RecType,RecCode,PeriodCode,StartDate,StopDate,Cnt,Freq

10S1M6/1/200711/30/20081812
10S2M11/30/20089/30/20091012
10S2Q11/30/20108/18/201134
10L8A6/1/20079/30/200931
10L8A11/30/20108/18/201111

From this data, I need a record for each individual month,quarter,etc.:

select y.*,MonthNo,Add_Months(StartDate,MonthNo*Frequency) from (
... Code to generate data ...
) y,(select rownum MonthNo from dual connect by level <= Cnt)

This returns ORA-00904: "CNT": Invalid Identifier. I don't get an error if I use a constant:

select y.*,MonthNo,Add_Months(StartDate,MonthNo*Frequency) from (
... Code to generate data ...
) y,(select rownum MonthNo from dual connect by level <= 3)

How can I get this to work using the "CNT" value instead of a constant?

View 7 Replies View Related

Get Different Level Using Hierarchical Query In Sql?

Jan 27, 2009

I'm trying to get different level using hierarchical query in sql.my table is

item_id child_item_id
------------------------------
p21 p25
p21 p22
p22 p23
p22 p24
p25 p27
p25 p26
p27 p28
p27 p29
p30 p31
p30 p32

I want to display result with respective levels.
for example p21 ,p30 are coming under first level .
p22,p25 ,P31,P32are 2nd level.
p23,p24,p26,p27 are 3rd level
p28,p29 are FOURTH level item_id's.

Already I 'VE tried using CONNECT BY PRIOR clause.BUT STILL I COULDN'T GET THE RESULT.

View 2 Replies View Related

PL/SQL :: Get Only Last Level In SQL Hierarchy Query

Jun 4, 2013

How to get only the last level in Oracle SQL Hierarchy Query?

View 2 Replies View Related

Visual Studio :: Set Query Window Default Date Format To Full Date?

Oct 24, 2013

When I run a query form the the Query Window in Visuial Studios 2012 all the date fields truncated to 'mm/dd/yyyy', but i need the full date returned. I am able to get full date from  TO_char(MyDateField, 'yyyy-mm-dd hh24:mi:ss'), but if I do TO_DATE(MyDateField, 'yyyy-mm-dd hh24:mi:ss')  it only returns  'mm/dd/yyyy'. I'm sure this is a simple setting in Visual studios but I cant find it to save my life. Is there there a way to have the full date returned by default?

View 0 Replies View Related

Application Express :: Select List From Query With Row Level Where Clause?

May 10, 2013

I am trying to create a tabular form based on a SQL query, that has a query-based select list with a where clause that references a column in the originating SQL query.

The situation is, I have a table that stores client_id, source_id and build_id, lets call it client_source. I have a second table, build_source, that contains source_id and build_id, with a one to many relationship between the two (source_id of 1 could have build_id of 1-7).

Using a tabular form, I want to select the corresponding build_id to be used in client_source, but the select list must only contain the build_id's for that rows particular source_id.

Here is an example of the SQL source of my tabular form;

select
s.ROWID,
s.CLIENT_ID,
s.SOURCE_ID,
APEX_ITEM.SELECT_LIST_FROM_QUERY(1, s.BUILD_ID,
'select b.build_id display, b.build_id return from
build_source b where b.source_id = s.SOURCE_ID ') lst
from client_source s

... what I am trying to achieve is that the source_id fields in bold match. When the query is built this way I get an 'invalid identifier' Oracle error on s.SOURCE_ID at runtime.

Is there some special tags that need to be used to reference the outside column?I am running on Application Express 4.1.0.00.32, on a Oracle 10g release 10.2.0.4.0 database.

View 2 Replies View Related

SQL & PL/SQL :: Date Overlap And Merge Date Query

May 11, 2011

Lets say the contents of the table are like this -

key dte_start(number) dte_end(number)
1 20010101 20011231
1 20020101 20081231
1 20030601 20071231
1 20100101 20101231
2 20090101 20091231
2 20090401 20101231
2 20101231 20111231
2 20140101 20141231
3 20080101 20091231
3 20070101 20081231
3 20100101 20101231
3 20120101 20120630

I want the query to resolve overlapping dates as well as merge contiguous segment and leave non-contiguous segments as is. The final result of the query should be like this.

key dte_start(number) dte_end(number)
1 20010101 20081231
1 20100101 20101231
2 20090101 20111231
2 20140101 20141231
3 20070101 20101231
3 20120101 20120630

I tried using the lead over partition along with the standard overlap query and case logic to get the results but couldn't get it to work.

View 14 Replies View Related

SQL & PL/SQL :: Row Level To Column Level Data Transposition?

May 14, 2010

I have a data like,

1) manual_temp_master

auto_idbatch_id sec_idsec_id_type crrncy_cdcreate_Dt price_dt
------------------------------------------------------------------
11234ABC1CUSIPUSD14/05/201014/05/2010
23456XYZ1SEDOLGBP13/05/201013/05/2010

2)manual_temp_detl

auto_idbatch_id Price_bkt_cdscreate_Dtprice_date
---------------------------------------------------------
11234PS114/05/201014/05/2010
11234PS214/05/201014/05/2010
11234PS314/05/201014/05/2010
11234PS414/05/201014/05/2010

[code]....

I want to write a sql query which will fetch the data from manual_temp_master and manual_temp_detl.But from manual_temp_detl table, Price_bkt_cds columns should be displayed as columns. Like the should look like as below:

sec_idsec_id_type crrncy_cd COL_PS1 COL_PS2 COL_PS3 COL_PS4 COL_PS5COL_PS6price_date
--------------------------------------------------------------------------------------
ABC1CUSIPUSDPS1PS2PS3PS4PS5PS614/05/2010
XYZ1SEDOLGBPPS1PS2PS3PS4PS5PS613/05/2010

View 8 Replies View Related

SQL & PL/SQL :: DBMS_STATS For Table Level Vs Partition Level

Nov 17, 2010

what is the difference on DBMS_STATS for table level and partition level , which will provide the best optimizer . If the table xxxx is partitioned from 1 to 10 ,then running gather stats on table xxxx as whole table level or partition level which will provide best result on the performance.

View 1 Replies View Related

PL/SQL :: Query First Date Before Payment Date

Nov 25, 2012

How to get the payment date less than the sch date?

example:

1) for 07/24/2012 sch date the nearest payment date is 07/27/2012.

sch date should be less than the payment date.

Sch dates 07/24/2012 and 07/26/2012 are less than payment date 07/27/2012 but we take the least one.

2) sch date 07/27/2012 is less than the payment date 08/08/2012 so 07/27/2012

Below is the scripts and output needed.

create table cust_sch_tbl
(
customer_NUMBER     number,
c_CODE     char(5),
sch_DATE date
);

[Code]...

Output needed:

cust   cust code payment date      sch date
4611     c1     7/27/2012      7/24/2012
4611     c1     8/8/2012 16:57      7/27/2012
4611     c1     10/19/2012 16:37 10/11/2012
4611     c1     11/15/2012      10/30/

View 6 Replies View Related

SQL & PL/SQL :: Query With Connect Path

Nov 11, 2011

I am working on a query as below:

CREATE TABLE FDL_SNOP_SCDHUB.TEST_CONN
(
NAME1 VARCHAR2(100 ),
VALUE1 VARCHAR2(4000 ),
ID1 VARCHAR2(200 ) NOT NULL
);
[code]....

I have tried with sys_connect_by_path and wm_concat

with mst as (
select id1,name1,value1 from test_conn c
where ((c.name1='WORKFLOW_TYPE' and NVL2(:p_workflow_type,decode(:p_workflow_type,' SELECT ALL',' SELECT ALL',c.value1),0)=NVL2(:p_workflow_type,:p_workflow_type,0))
or (c.name1='SOURCE_TYPE' and NVL2(:p_source_type,decode(:p_source_type,' SELECT ALL',' SELECT
[code]....

but unable to group it by Id, all comes under one column

View 6 Replies View Related

SQL & PL/SQL :: Query To List Last 11 Days - Connect By

May 24, 2010

I want to write a query that lists last 11 days. I tried the following query using CONNECT BY...

SQL> SELECT MIN_DT + LEVEL - 1 DAYS FROM
(
SELECT TRUNC(SYSDATE -10) MIN_DT, TRUNC(SYSDATE) MAX_DT FROM DUAL
)
CONNECT BY LEVEL <= MAX_DT - MIN_DT + 1;

DAYS
--------------
20100514000000

For some reason it gives only the first day.. But this one works -

SQL> SELECT MIN_DT + LEVEL - 1 DAYS FROM
(
WITH MYDUAL AS (
SELECT 1 FROM DUAL
)
SELECT TRUNC(SYSDATE -10) MIN_DT, TRUNC(SYSDATE) MAX_DT FROM MYDUAL
)
CONNECT BY LEVEL <= MAX_DT - MIN_DT + 1;

DAYS
--------------
20100514000000
20100515000000
20100516000000
20100517000000
20100518000000
20100519000000
20100520000000
20100521000000
20100522000000
20100523000000
20100524000000

11 rows selected. I am not able to understand what is the difference between the two queries! Why is the second query able to do what the first query cannot?

View 5 Replies View Related

SQL & PL/SQL :: Numbering On Break

Mar 24, 2010

I have a query with output as:

CUST PROD
Customer1Product1
Customer1Product2
Customer1Product3
Customer2Product1
Customer2Product2
Customer3Product1The output required is

Number CUST PROD
1 Customer1Product1
Customer1Product2
Customer1Product3
2 Customer4Product1
Customer4Product2
3 Customer5Product1

For every change in customer i need to put a new serial number.

View 2 Replies View Related

Forms :: How To Connect Query With Spread Table

Nov 2, 2010

how to connect query with Spread Table or load table data in spreadtable by using forms 6i

View 6 Replies View Related

PL/SQL :: Month Wise Break

May 4, 2013

I have a requirement as follows :-

The user will submit the start date and end date. Based on the date parameters, the customized procedure should do some calculations month-wise.

For eg : start date - 01-Dec-2012 to end date - 31-Mar-2013

I want the break up as
Start date End date
01-dec-2012 31-dec-2012
01-jan-2013 31-jan-2013
01-feb-2013 28-feb-2013
01-mar-2013 31-mar-2013

How can i achieve this month-wise break ?

View 6 Replies View Related

SQL & PL/SQL :: ENTER (line Break) In Name Field Column?

Jun 12, 2013

There is a problem with name field like ex:

ENTER(line break) between 'HHD DDD PRIVATE' and 'LTD.

how to put a check in code to avoid these type of scenarios.

View 4 Replies View Related

Reports & Discoverer :: Page Break On A Group?

Jun 5, 2003

I have a report with 4 groups one above the other and there is data such that each group has certain number of records for a particular value. Now what I want is, to have a page break after each set of data for all the groups. Can this be achieved using format triggers ?

View 3 Replies View Related

Reports & Discoverer :: Break RTF Page After Every 8 Records?

Aug 6, 2010

how can i break my rtf page after every 8 record.

View 2 Replies View Related

PL/SQL :: Break Down Each Member Range By Record Per Month?

Mar 28, 2013

have a req like below

CREATE TABLE TEST_BRK
( EMP_ID VARCHAR2(20),
  BEG_DT DATE,
  END_DT DATE,
  DEPT_CODE VARCHAR2(10),
 
[code]...

  COMMIT;i need to break down the each member range by record per month and make a record as first day of that month . and dept and branch should have the same value as what the value it was in the range of source.

excepted ouput like below

  EMP_ID      MONTH_DAY_1    DEP_CODE    BRANCH
  AAAA        01-JAN-2010      02          A
  AAAA        01-FEB-2010      02          A
  AAAA        01-MAR-2010      02          A
  AAAA        01-APR-2010      02          A
  AAAA        01-MAY-2010      02          A
  AAAA        01-JUN-2010      05          B
  AAAA        01-JUL-2010      05          B
 
  .how can i code the logic to get my expected output above

View 3 Replies View Related

Application Express :: Interactive Report - Break Formatting Style?

Nov 14, 2013

,Is there a way to make Interactive reports break format work/look like classic reports break format? i.e.1st,2nd,3rd column option. The way IR breaks is that is reserves a whole row for the column used in the break, which is not what I want, I want the report to look like the following:

 city               emp           sal 
Chicago     John Miller  1500                
Mark Horton 2000
Denver       Rob Martino 1200 

I tried to use rollup queries which work fine except that it does not do exactly what I want when I sort columns through the front end , I am basically displaying a check box for grouped records ( and I want it to display at the first record of each group), so the order is important. for example: 

checkbox   City               emp           sal[ ]              
Chicago     John Miller    1500         
Mark Horton  2000[ ]             
Denver       Rob Martino  1200 

View 2 Replies View Related

Reports & Discoverer :: Oracle Report 10g - Sorting In Break Order

Nov 30, 2010

Is there a way to short a Matrix report? The query shorts correctly in PLSQL but not in the generated report file.

There are columns like Date,Device,Operator Id etc and the report should be able to short depending on the requirement. For example,

it should be able to short by device, or by Date or both. I tried sorting in the 'Break order' but doesn't come out as required.

View 6 Replies View Related

Application Express :: Control Break Appearance In Interactive Report

Sep 4, 2013

I'm using a control break on 3 columns in my report, but when doing this Apex shows the columns comma-separated, like: Column A: value, Column B: value, Column C: value But I would like it to look like:

Column A: value
Column B: value
Column C: value 

Is there any way of achieving this? I am currently working with the test environment Oracle supplied us, because we are testing if we can use APEX at our company. The version is Application Express 4.2.2.00.11 and it's running on Oracle 11g. Im using Firefox but it's just the same in IE. 

View 10 Replies View Related

Backup & Recovery :: Incremental Backup Level 1 Or Level 0 Without Archivelogs?

Oct 22, 2012

Can you take an incremental backup level 1 or level 0 without archivelogs?

syntax would bebackup as compressed backupset cummulative level 1 database.

The reason I ask is because when I run backup as compressed backupset cummulative level 1 database plus archivelogs # it runs fine, but when I run backup as compressed backupset cummulative level 1 database it just hangs.

View 20 Replies View Related

Client Tools :: Break And Compute Command Usage To Modify Output

Oct 14, 2010

I tried BRK and COMPUTE commands myself after reading the documentation but its not working...The output of my script is correct but What I want is the information to be displayed on a different way:

Original script for the report:

set serveroutput on size 1000000
set pages 10000
set lines 1000
set arraysize 1
set trimspool on
set heading off
[code]....

The output should be exactly as above as the requirement is this should be in this particular order and how many is the count in each thoroughfare or locality, as shown in the final output, above.

View 3 Replies View Related

Client Tools :: Different Charactersets Show Line-break In Select Result

Mar 22, 2010

i have a little curiosity in result of a select statement.

One database has following characterset settings:

NLS_CHARACTERSET WE8ISO8859P15
NLS_NCHAR_CHARACTERSET AL16UTF16

and the following select statement results in multiple lines:

SQL> select INSTANCE_NAME,STATUS from v$instance;
INSTANCE_NAME
------------------------------------------------
STATUS
------------------------------------
BUP
OPEN

Another database has following characterset settings:

NLS_CHARACTERSET UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16

and the same select shows only a 2 line result:

SQL> select INSTANCE_NAME,STATUS from v$instance;
INSTANCE_NAME STATUS
---------------- ------------------------------------
HPSMP OPEN

Both databases are version 10.2.0.4 and both are running on HPUX 11.31.Why do we get this differents in showing the result?

Our problem is that the package-start scripts from HP, to start a database, check the result of the select statement above and if we have the multiline result the check gives an error and stops the package again.

View 3 Replies View Related

JDeveloper, Java & XML :: Section Break Conflicts With Dynamic Header In RTF File

Jun 26, 2012

I have dynamic header in my rtf file. I have section break on start group of body. but it does not display dynamic header value. If i remove section break then it display dynamic header.

I have to display dynamic header and section break is also required there.

View 4 Replies View Related

SQL & PL/SQL :: Row Level And Statement Level

Apr 11, 2013

between statement level or row level trigger, which trigger will execute first.We have BEFORE_UPDATE_ROWLEVEL_TRIGGER and BEFORE_ UPDATE_ STATEMENT LEVEL_TRIGGER triggers on table product.

which will execute first on update DML event ?

View 2 Replies View Related

Query For Group By Date?

Apr 30, 2011

We have one oracle table, which has one column "Creation_Date" of the datatype date.

This column has data like
"14-Aug-2010 01:30:30"
"14-Aug-2010 03:30:30"
"15-Aug-2010 01:30:30"
"16-Aug-2010 01:30:30"
"16-Aug-2010 03:30:30"

We want to write SQL query which will group rows by date wise without considering time..I mean for above rows the date wise output should be

Count Date
------- -------
2 14-Aug-2010
1 15-Aug-2010
2 16-Aug-2010

View 2 Replies View Related

SQL & PL/SQL :: Query Related To Date

Feb 20, 2012

i have a view in a schema of my database 'vw_get_noti_calendar_data' and i want to select some field but it shows an error 'invalid number' and i have observed it but no effect,so where is the error.

my code is---------

SELECT *
FROM aepuser.vw_get_noti_calendar_data
WHERE TO_DATE (TO_CHAR (event_start_date, 'mm/dd/yyyy'), 'dd/mm/yyyy')
BETWEEN TO_DATE (TO_CHAR ('2/28/2012', 'mm/dd/yyyy'), 'dd/mm/yyyy')
AND TO_DATE (TO_CHAR ('2/28/2012', 'mm/dd/yyyy'), 'dd/mm/yyyy')
AND srnum BETWEEN 5 * 1 + 1 AND 5 * 1 + 5;

View 11 Replies View Related







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