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?
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.
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?
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.
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.
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:
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.
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
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?
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 ?
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
,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
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.
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.
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.
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:
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.
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.
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.
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;
