Reports & Discoverer :: SQL Union All - Query Block Has Incorrect Number Of Result Columns

Feb 1, 2012

I have the following Union All query. It throws the following error in SQL plus

ERROR at line 27: ORA-01789: query block has incorrect number of result columns

After doing some google for the above error it suggests there are incorrect number of columns in the Union All query.I could not figure out the exact location well SQl Plus says error is on line 27 at the first opening bracket like

(Select distinct c.contact_code

Following is the SQL query

Select
tbl_contact.contact_code,
contact_title
||'.'||contact_name contact_name,
contact_address,
[Code] ......

View 1 Replies


ADVERTISEMENT

Reports & Discoverer :: Incorrect Page Number At Top Right Corner?

Jan 17, 2012

I am working on the Reports Builder 10.1.2 at the Database Oracle 11.2.0. I have developed a report , which shows the page number at top right cornet. So I have selected a field and set the Source as "page Number". While I run the report through Reports Builder,it is properly displayed the page number. But while I copied the report to unix and run through "$ORACLE_INSTANCE/config/reports/bin/rwrun.sh", the page number is showing as 65536 . By changing the Source of the field to "Total Page" or "Physical page Number" make no difference.Every times It is showing the page number 65536 in the pdf output of the report.

View 6 Replies View Related

Reports & Discoverer :: How To Add Union To Report Query

Feb 16, 2012

I am making a report in hrd regarding gross, deduction and there difference. I hv an attribute in the table as indicator whose value is addition and deduction. i want the sum of both in two diff column in a single rep. i want the report dept wise.

but i m getting fatal error at run time. which i don't understand.

View 3 Replies View Related

PL/SQL :: Incorrect Query Result Over Database Link

Jul 2, 2012

My problem is I have 3 tables (TEST_TBL1, TEST_TBL2, TEST_TBL3). TEST_TBL2 and TEST_TBL3 are in remote database and I use database link to join them. The following query returns incorrect result (I seems that it ignore the where clause)

SELECT * FROM TEST_TBL1 JOIN TEST_TBL2@db_remote USING (KEY1) JOIN TEST_TBL3@db_remote USING (KEY2) WHERE KEY1=XXX OR KEY2=YYY;

I am on 11R1 (11.1.0.7)

FOR EXAMPLE:

Local database:
CREATE TABLE TEST_TBL1
(
KEY1 NUMBER(5) NOT NULL,

[Code].....

View 8 Replies View Related

Parallel Query Block Of Union All Set

Sep 24, 2010

We have very large table having data more than 1000 millions rows. We divide this table into four physical tables say A, B, C and D. The physical horizontal partition of data of this original table is done based upon their business policy.

Each partitioned table has contained data of particular business entity. Further each table has partition and sub partitions based upon business rule.

We have to retrieve data from all these tables as follows:

select a1, a2, a3, a4, a5, a6
from A
where < logical filter condition>
union all
select b1, b2, b3, b4, b5, b6
[code].....

We observed that above each query block execute in serial one after another and individual each query block capable to process data in parallel from respective table.

How does this above query able to execute each query block in parallel?

View 14 Replies View Related

SQL & PL/SQL :: Different Number Of Result Sets While Adding Further Columns In Select Clause

May 4, 2010

The below sql is giving different number of result sets while adding further columns in select clause.i.e After adding the columns 4,5,6 in the below query its giving different number of result set.In this case the result set count would be 5.

Before adding the columns 4,5,6,the result set count was 11.

SELECT PAYMENT_METHOD_MAP.NETTINGGROUP_ID,
PAYMENT_METHOD_MAP.CREDITPAYMENTMETHOD_CD,
PAYMENT_METHOD_MAP.DEBITPAYMENTMETHOD_CD,
PAYMENT_METHOD_MAP.AGENT_ID,
SETTLEMENT.NETTINGGROUP_ID,
SETTLEMENT.SETTLEMENTDATE
[code]....

View 8 Replies View Related

Reports & Discoverer :: Create A Report By Using One Field / Text As Columns Name In Layout But Display All Columns

Jun 16, 2010

I want to create a report by using one field and one text as columns name in layout but display the all the columns. I mention the 5 column names in query.how can I write function in summary column.

View 4 Replies View Related

Forms :: Incorrect Result On Assignment Statement?

Mar 3, 2011

When I run a menu (which was working fine yesterday and hasn't been changed in months) I click on an item and it runs the following

"
DECLARE
module_name VARCHAR2(125);
BEGIN
module_name := :GLOBAL.FORMS_PATH || 'covenants';
Open_Form(module_name);
END;
"

The module_name variable should be assigned the value 'covenants', because :GLOBAL.FORMS_PATH is '' (nothing). I stepped thru the code and in the debug system values window there is nothing in :GLOBAL.FORMS_PATH. But instead, it gives me: 'FORM_NAMEcovenants' where 'FORM_NAME' is the form the menu was called from.

View 5 Replies View Related

Reports & Discoverer :: How To Find Page Number And Total Number Of Pages

Jan 26, 2010

i am using oracle developer 6i report builder i required this type of query

example

if (:page number LIKE '1')
then
srw.set_text_color('darkred');
end if;

return (TRUE);
end;

but page number is not my table database item how can i use builtan page &<pagenumber> use for conditional format.

View 34 Replies View Related

SQL & PL/SQL :: Get Result Query Of Three Columns And Pass Into Send-mail Proc?

May 10, 2011

I had never done this before, basically I need to get the result of the SQL query which had three columns and pass into thesend _mail proc, should I build a variable to hold the 3 value and send mail from it?

below is my send_mail proc

Quote:
CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_to varchar2,
msg_subject varchar2,
msg_text varchar2 )

[code]...

and below is my sql query
Quote:
select a.id, b.hr_info, c.details
from contact a, employees b, grid c
where a.id=b.id
and b.hc=c.hc

View 1 Replies View Related

Reports & Discoverer :: Averages Across The Columns

Jul 25, 2011

I have the following 6 columns in a report. And i need a 7th column which is a calculated field (average of the first 6 columns), the problem is some of the records have a '0' value in them so the aveage is not always (SUM)/6, If one of the fields have a 0 then the average is (SUM)/5, likewise if 2 fields have o's in them then the average will be (SUM)/4, see the sample data set.

A B C D E F G(Average)
83 83 33 0 0 100
83 83 33 0 0 100
0 67 67 100 17 92
83 83 100 67 50 100
83 83 100 83 50 100
67 0 83 100 17 100
83 83 83 67 67 100
83 83 83 50 50 90

[Code]....

View 7 Replies View Related

Reports & Discoverer :: Sorting On Different Columns

Sep 10, 2012

I have report where there are multiple columns and requirement is user may choose his own sorting order from form, any sample .rdf.

CREATE TABLE EMP (EMP_CODE VARCHAR2(12),EMP_NAME VARCHAR2(20),SAL NUMBER);
INSERT INTO EMP VALUES ('1','A',10);
INSERT INTO EMP VALUES ('2','C',3);
INSERT INTO EMP VALUES ('3','B',20);

[Code]..

--note order by is changing, how its doable in report 6i.

View 2 Replies View Related

Reports & Discoverer :: Dates As Columns

Feb 18, 2012

I have a small requirement for report, i want to develop a report based on dates and employee performance.Suppose i give the parameter from date as 01'st jan 2011 and end date as 31'st Jan 2011 then employee hrs will be as summary as rows distributed among days with the given date range as columns.How to make each day as column automatically.

View 5 Replies View Related

Reports & Discoverer :: Fake Columns In Matrix?

Aug 17, 2011

I have done a matrix group report which displays the department codes as columns and main account as rows for a month my problem is there are two groups and in one group there is data for 5 departments and in other group there is data for 4 departments and in display if there is no data for one department it should come as fixed columns in order to make it more readable

.Is there any way where i can fake some null data and bring it as column or any other way.

View 5 Replies View Related

Reports & Discoverer :: Multiple Columns In Param Form

Jun 4, 2012

I am adding a parameter to the parameter form of existing reports based on a select statement and has two columns. That part is fine. However, Oracle reports is adding a dash between the two values. Still fine, but I am also adding a UNION with ALL in case the user wants to run the report for all values. In the Parameter form it looks like this:

ALL -
RBC - 111
RRG - 234
TEB - 445

How do I get rid of the dash for "ALL" since I did not physically add it? Below is my select statement for the parameter I created:

SELECT code,
code_num
FROM codes
WHERE code_num <> 0
UNION
SELECT 'ALL', ' '
FROM dual
ORDER BY 1;

View 8 Replies View Related

How To Display Result From 2 Queries - UNION ALL

Oct 21, 2012

I am trying to display the results from 2 queries, one is supposed to display the count of the employees, per department, who win over the average of the entire company and the other one is supposed to display the count of the employees, per department, who win under the average of the entire company.

I used a UNION ALL, but all it does is merge the results from the ones that win over and under the average into one row, is there a way to separate them? I tried assigning names to each salary using AS but it only displays the first I put in.

sql
Original
- sql Code
(
SELECT DE.DEPARTMENT_NAME, COUNT (EM.EMPLOYEE_ID) AS MAYORES
FROM DEPARTMENTS DE, EMPLOYEES EM
WHERE DE.DEPARTMENT_ID = EM.DEPARTMENT_ID
AND EM.SALARY > (SELECT AVG(EM.SALARY) FROM EMPLOYEES EM)
GROUP BY DE.DEPARTMENT_NAME
[code].....

View 3 Replies View Related

Reports & Discoverer :: Building Reports In Dev - 10g With Ref Cursor Query?

Dec 27, 2011

i was searching for the example of subject mentioned and visited the following website

[URL]...

here is a tutorial to create such report, but i am confused as here in Report's Data Model they created 3 Ref Cursor Queries and did not Close the opened cursor, is there no need of this or any mistake or if it necessary, where have to Close the cursor?First, they have created following Packages

PACKAGE concl_cv IS
type conclass_rec is RECORD
(EMPLOYEE_ID NUMBER(6),
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25),

[code]...

then, created Functions which created in Ref Cursor Query in Data Model
function q_portdescRefCurDS return port_cv.portdesc_refcur is
temp_portdesc port_cv.portdesc_refcur;
begin
open temp_portdesc for select department_id, department_name from departments;

[code]...

nothing here any Close cursor statement.i am trying to explore this area of the development.

View 2 Replies View Related

Reports & Discoverer :: How To Compare Current Page Number With Total Page Number

Feb 19, 2013

I have a report created in Reports6i. I have two fields at the bottom

1.Page Total
2.Voucher Total

I do not want to print the Page Total if the total number of pages = 1.

How can I achieve this?

View 1 Replies View Related

Query To Know Number Of Columns In A Table?

Apr 4, 2008

query to know number of columns in a table i.e.

if I want to know how many number of colums are present in a specific table then what would be the query.

View 1 Replies View Related

Reports & Discoverer :: Ordering Columns And Rows In A Matrix Report

Jun 20, 2012

I am having a matrix report, which has its column cells from table A and row cells from table B when I run the report the columns and rows are not ordered I wrote the order by statement in the report query but there was no defference.

I also wrot a subquery like

select column1, column2, (select column3 from table1 order by code)
from .... but it seems I can not order in a subquery

Is there any way to do that

View 5 Replies View Related

Reports & Discoverer :: How To Skip Columns With Space Based On Parameters

Jan 10, 2011

I have Two Parameters like 'YES' & 'NO'

when parameter is YES, column having values
when Parameter is NO, column having no values

In this scenario, how can i skip the column with space when parameter is NO because the column exist in middle of columns in report and Present with space when parameter is YES.

View 9 Replies View Related

Reports & Discoverer :: Number Format

Oct 20, 2011

I have a value 233560.66 but in report its showing 233560.67 using format NNNGNNNGNNNGNN0D00 but when i remove format it shows 233560.665 but i want result 233560.66

View 2 Replies View Related

SQL & PL/SQL :: Query Two Columns With Datatypes As Number And Varchar

Jul 17, 2012

Have table with two columns with datatypes as number and varchar and the values in A column like 1,2,3 AND B column values like a,b,c. Now need to display data in a single column as 1,a,2,b,3,c.

View 4 Replies View Related

SQL & PL/SQL :: How To Pass Different Number Of Columns Dynamically To A Query

Apr 26, 2010

I want to pass Number of columns dynamically to a query. I got success in SQL.

SQL> select &column_list from emp;
Enter value for column_list: empno,ename,sal

EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1100
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300

14 rows selected.

But the same i need to achieve in pl/sql. I try with the Ref cursor, but not succeeded.

View 15 Replies View Related

Reports & Discoverer :: Page Number Change

Dec 3, 2011

During the Report Development for the Invoice Print the Page Number is required to display for the each Page like 1 of 3.The Report have the parameter of From Invoice Number and To Invoice Number. The Page display the Current page and Total Page. How to Split the Invoice Number wise page Number in the Report.

Presently

Invoice No Page No.
14001 1 of 4
14002 2 of 4
14002 3 of 4
14003 4 of 4

Requirement

Invoice No Page No.
14001 1 of 1
14002 1 of 2
14002 2 of 2
14003 1 of 1

View 2 Replies View Related

Reports & Discoverer :: Find Out Last Page Number?

Sep 29, 2011

I want to display a boilerplate item and their value only on the last page.

The item is under frame . So print object at last page doesn't work.

I could find the page number by the use of srw.get_page_num(n).

But not sure how to find the last page number.

View 2 Replies View Related

Reports & Discoverer :: Converting The Number To String

Dec 6, 2010

I wanna convert the amount of money from number to string such as 144.5 to be one hundred forty four point five is there any function or i have to write my function? How could i put new line in the string?

for example if i have 'SAB Bank' || 'Riyadh'

but i want SAB bank to be displayed in line and Riyadh in line.

View 2 Replies View Related

Reports & Discoverer :: Masking Number To Word

May 21, 2013

I just want to know whether we can mask salary column of number datatype into text ,like we used to_char(to_date(<column_name>,'j'),'jsp') in sql? for example

Name Salary
SMITH 800

to
Name Salary
SMITH EIGHT HUNDRED

View 5 Replies View Related

Reports & Discoverer :: Number Of Records On Run Time?

Oct 16, 2012

MY problem is in my reports i wish to have filter number of records on my requirement , when i call reports from form then ask me that how many number of records you want..

View 3 Replies View Related

Reports & Discoverer :: Excluding SQL Query Data When Linked With XML Query?

Apr 26, 2013

I have data in a table and another in XML file,I used SQL query to retrive the data placed on the table, and link this query with XML query that retrieves the data stored in the xml file. The data stored in the table and xml file sharing a key field, but the xml contents are less than what in the table.I want to show only the data shared between the two queries, how can I do that?

e.g.:

Table emp:

e_id | e_name | e_sal
023 | John | 6000
143 | Tom | 9000
876 | Chi | 4000
987 | Alen | 7800

XML File

<e_id>
143
876

So, I want the output to be:

e_id | e_name | e_sal | e_fee
143 | Tom | 9000 | 300
876 | Chi | 4000 | 100

View 2 Replies View Related







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