How can i display the sum/difference of 2 columns?

HelloIf i have an sql which looks something likeselect (select count(*) from A), (select count(*) from B)from DUALHow can i display a 3rd column which will be the difference between the 1st and 2nd column? Will using column aliases work? likeselect (select count(*) from A) as Col1, (select count(*) from

as Col2, Col1-Col2 from DUALPlease help?

Find more posts tagged with

Comments

eLink User

Message from Mohsin Jessa via eLinkHi,I don't think using column aliases will work. Aliases are used to providecustom column names/titles. You can't use them in run-time computations.The following applies only if you are actually trying to perform "selectcount(*) from table". If not can you provide me with the actual SQL you aretrying to perform and I'll try and help you.You have 3 options that will work though. This is an oracle specificsolution (I assuming you are using oracle: since you are using the dualpseudo table).Option 1: This is very inefficient in terms of d/b performance.select (select count(*) from t1) as "C_t1", (select count(*) from t2) as"C_t2",((select count(*) from t2) - (select count(*) from t1)) as "D_t2t1"from dual;Option 2: (if you are trying to use this in a LiveReport it may not work asyou can't call a stored procedure). LiveReport experts please correct me ifI am wrong:First create a stored procedure (you can get away with anonymous block too)with the following script (need to run this once only).create or replace procedure sum1 ass1 number;s2 number;beginexecute immediate 'select count(*) from t1' into s1;execute immediate 'select count(*) from t2' into s2;dbms_output.put_line (' Count t1'||' '||' Count t2'||''||' Diff t2-t1');dbms_output.put_line (s1||' '||s2||' '(s2-s1);end;Then you execute the stored procedure with the following SQL commands.SQL> set serveroutput on;SQL> execute sum1;The first command allows sql_plus to display the output from the dbms_outputpackage.Option 3: this option exploits a col in the user_tables view which has acolumn "num_rows". By default this col is blank. It is only populated whenthe table is analyzed but the value is not updated when more dml(insert/delete) are performed on the table (i.e. the value of "num_rows"gets stale over time). So if you want the most accurate value then you mustanalyze the table. The advantage of this option is that it will avoid fulltablescans of the tables for which you are performing the count(*). Forsmall tables this may not be a problem, but when you have tables in theorder of 10's of millions of records and a busy d/b then you'll definitelywant to avoid this. The cost of analyzing the table is much less than thecost of doing full tablescans. However in an OLTP environment queriescurrently executing against these tables will behave differently and maycause performance related issues somewhere in the system (assuming you arerunning in rule based mode and don't generate statistics on the schemaobjects).analyze table t1 compute statistics;analyze table t2 compute statistics;select (select num_rows from user_tables where table_name='T1') as "C_t1" ,(select num_rows from user_tables where table_name='T2') as "C_t2",((selectnum_rows from user_tables where table_name='T2')-(select num_rows fromuser_tables where table_name='T1'))as "D_t2t1" from dual;analyze table t1 delete statistics;analyze table t2 delete statistics;Summary:Option 3 is the best for large tables but you must analyze if you wantaccurate results.Option 2 is the next best since it does full tablescans of all the tablesinvolved once.Option 1 is the worst, since it will perform full tablescans on all thetables twice.Hope this helps.Mohsin> -----Original Message-----> From: eLink Discussion: Livelink LiveReports Discussion> [mailto:livereportsdiscussion@elinkkc.opentext.com]> Sent: Thursday, July 11, 2002 11:14 PM> To: eLink Recipient> Subject: How can i display the sum/difference of 2 columns?>>> How can i display the sum/difference of 2 columns?> Posted by hpglc on 07/11/2002 11:13 PM>> Hello>> If i have an sql which looks something like>> select (select count(*) from A), (select count(*) from B)from DUAL>> How can i display a 3rd column which will be the difference> between the 1st and 2nd column? Will using column aliases work? like>> select (select count(*) from A) as Col1, (select count(*) from

> as Col2, Col1-Col2 from DUAL>> Please help?>>> [To reply to this thread, use your normal e-mail reply function.]>> ============================================================>> Discussion: Livelink LiveReports Discussion> https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objI> d=2249677&objAction=view>> Livelink Server:> https://knowledge.opentext.com/knowledge/livelink.exe>>>

eLink User

Message from Marie Lindsay <<A HREF="mailto:marie@opentext.com">marie@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

Mohsin wonders about executing stored procedures. I found in working with MSSQL (and from a hint posted elsewhere in this discussion) that I could execute a stored procedure from a LiveReport, by just naming the stored procedure (without an EXEC) in the SQL field and then naming any parameters I needed to pass to the procedure.

I also found that the report displayed results only if its attempt to "output" was in the form of a single SELECT. For example, I couldn't see the results of a stored procedure that contained PRINT commands, but I could execute one that built a temporary table and then performed a SELECT on that table.

I haven't tried this under Oracle, but I have heard that it is indeed possible to do, and maybe someone who uses Oracle more often than I do will confirm this.

-----Original Message-----
From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Friday, July 12, 2002 11:52 AM
To: eLink Recipient
Subject: RE How can i display the sum/difference of 2 columns?

RE How can i display the sum/difference of 2 columns? Posted by eLink on 07/12/2002 12:51 PM
In reply to: How can i display the sum/difference of 2 columns? Posted by hpglc on 07/11/2002 11:13 PM

Message from Mohsin Jessa <mjessa@opentext.com> via eLink

Hi,

I don't think using column aliases will work. Aliases are used to provide
custom column names/titles. You can't use them in run-time computations.

The following applies only if you are actually trying to perform "select
count(*) from table". If not can you provide me with the actual SQL you are
trying to perform and I'll try and help you.

You have 3 options that will work though. This is an oracle specific
solution (I assuming you are using oracle: since you are using the dual
pseudo table).

Option 1: This is very inefficient in terms of d/b performance.

select (select count(*) from t1) as "C_t1", (select count(*) from t2) as
"C_t2",((select count(*) from t2) - (select count(*) from t1)) as "D_t2t1"
from dual;

Option 2: (if you are trying to use this in a LiveReport it may not work as
you can't call a stored procedure). LiveReport experts please correct me if
I am wrong:

First create a stored procedure (you can get away with anonymous block too)
with the following script (need to run this once only).

create or replace procedure sum1 as
s1 number;
s2 number;
begin
execute immediate 'select count(*) from t1' into s1;
execute immediate 'select count(*) from t2' into s2;
dbms_output.put_line (' Count t1'||' '||' Count t2'||'
'||' Diff t2-t1');
dbms_output.put_line (s1||' '||s2||' '(s2-s1);
end;

Then you execute the stored procedure with the following SQL commands.

SQL> set serveroutput on;
SQL> execute sum1;

The first command allows sql_plus to display the output from the dbms_output
package.

Option 3: this option exploits a col in the user_tables view which has a
column "num_rows". By default this col is blank. It is only populated when
the table is analyzed but the value is not updated when more dml
(insert/delete) are performed on the table (i.e. the value of "num_rows"
gets stale over time). So if you want the most accurate value then you must
analyze the table. The advantage of this option is that it will avoid full
tablescans of the tables for which you are performing the count(*). For
small tables this may not be a problem, but when you have tables in the
order of 10's of millions of records and a busy d/b then you'll definitely
want to avoid this. The cost of analyzing the table is much less than the
cost of doing full tablescans. However in an OLTP environment queries
currently executing against these tables will behave differently and may
cause performance related issues somewhere in the system (assuming you are
running in rule based mode and don't generate statistics on the schema
objects).

analyze table t1 compute statistics;
analyze table t2 compute statistics;
select (select num_rows from user_tables where table_name='T1') as "C_t1" ,
(select num_rows from user_tables where table_name='T2') as "C_t2",((select
num_rows from user_tables where table_name='T2')-(select num_rows from
user_tables where table_name='T1'))as "D_t2t1" from dual;
analyze table t1 delete statistics;
analyze table t2 delete statistics;

Summary:

Option 3 is the best for large tables but you must analyze if you want
accurate results.
Option 2 is the next best since it does full tablescans of all the tables
involved once.
Option 1 is the worst, since it will perform full tablescans on all the
tables twice.

Hope this helps.
Mohsin

> -----Original Message-----
> From: eLink Discussion: Livelink LiveReports Discussion
> [mailto:livereportsdiscussion@elinkkc.opentext.com]
> Sent: Thursday, July 11, 2002 11:14 PM
> To: eLink Recipient
> Subject: How can i display the sum/difference of 2 columns?
>
>
> How can i display the sum/difference of 2 columns?
> Posted by hpglc on 07/11/2002 11:13 PM
>
> Hello
>
> If i have an sql which looks something like
>
> select (select count(*) from A), (select count(*) from B)from DUAL
>
> How can i display a 3rd column which will be the difference
> between the 1st and 2nd column? Will using column aliases work? like
>
> select (select count(*) from A) as Col1, (select count(*) from
> as Col2, Col1-Col2 from DUAL
>
> Please help?
>
>
> [To reply to this thread, use your normal e-mail reply function.]
>
> ============================================================
>
> Discussion: Livelink LiveReports Discussion
> https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objI
> d=2249677&objAction=view
>
> Livelink Server:
> https://knowledge.opentext.com/knowledge/livelink.exe
>
>
>

eLink User

Message from Mohsin Jessa via eLinkHi there again:I just discovered a new way to do what you want but my previous argumentabout the performance implications of full tablescans still holds true.So here is the sql for option 4:select a.tt1,b.tt2,b.tt2-a.tt1 from (select count(*) tt1 from t1) a, (selectcount(*) tt2 from t2) bHope this helps.Mohsin> -----Original Message-----> From: eLink Discussion: Livelink LiveReports Discussion> [mailto:livereportsdiscussion@elinkkc.opentext.com]> Sent: Friday, July 12, 2002 12:52 PM> To: eLink Recipient> Subject: RE How can i display the sum/difference of 2 columns?>>> RE How can i display the sum/difference of 2 columns?> Posted by eLink on 07/12/2002 12:51 PM>> Message from Mohsin Jessa via eLink>> Hi,>> I don't think using column aliases will work. Aliases are used to provide> custom column names/titles. You can't use them in run-time computations.>> The following applies only if you are actually trying to perform "select> count(*) from table". If not can you provide me with the actual> SQL you are> trying to perform and I'll try and help you.>> You have 3 options that will work though. This is an oracle specific> solution (I assuming you are using oracle: since you are using the dual> pseudo table).>> Option 1: This is very inefficient in terms of d/b performance.>> select (select count(*) from t1) as "C_t1", (select count(*) from t2) as> "C_t2",((select count(*) from t2) - (select count(*) from t1)) as "D_t2t1"> from dual;>> Option 2: (if you are trying to use this in a LiveReport it may> not work as> you can't call a stored procedure). LiveReport experts please> correct me if> I am wrong:>> First create a stored procedure (you can get away with anonymous> block too)> with the following script (need to run this once only).>> create or replace procedure sum1 as> s1 number;> s2 number;> begin> execute immediate 'select count(*) from t1' into s1;> execute immediate 'select count(*) from t2' into s2;> dbms_output.put_line (' Count t1'||' '||' Count t2'||'> '||' Diff t2-t1');> dbms_output.put_line (s1||' '||s2||' '(s2-s1);> end;>> Then you execute the stored procedure with the following SQL commands.>> SQL> set serveroutput on;> SQL> execute sum1;>> The first command allows sql_plus to display the output from the> dbms_output> package.>> Option 3: this option exploits a col in the user_tables view which has a> column "num_rows". By default this col is blank. It is only populated when> the table is analyzed but the value is not updated when more dml> (insert/delete) are performed on the table (i.e. the value of "num_rows"> gets stale over time). So if you want the most accurate value> then you must> analyze the table. The advantage of this option is that it will avoid full> tablescans of the tables for which you are performing the count(*). For> small tables this may not be a problem, but when you have tables in the> order of 10's of millions of records and a busy d/b then you'll definitely> want to avoid this. The cost of analyzing the table is much less than the> cost of doing full tablescans. However in an OLTP environment queries> currently executing against these tables will behave differently and may> cause performance related issues somewhere in the system (assuming you are> running in rule based mode and don't generate statistics on the schema> objects).>> analyze table t1 compute statistics;> analyze table t2 compute statistics;> select (select num_rows from user_tables where table_name='T1')> as "C_t1" ,> (select num_rows from user_tables where table_name='T2') as> "C_t2",((select> num_rows from user_tables where table_name='T2')-(select num_rows from> user_tables where table_name='T1'))as "D_t2t1" from dual;> analyze table t1 delete statistics;> analyze table t2 delete statistics;>> Summary:>> Option 3 is the best for large tables but you must analyze if you want> accurate results.> Option 2 is the next best since it does full tablescans of all the tables> involved once.> Option 1 is the worst, since it will perform full tablescans on all the> tables twice.>> Hope this helps.> Mohsin>>> > -----Original Message-----> > From: eLink Discussion: Livelink LiveReports Discussion> > [mailto:livereportsdiscussion@elinkkc.opentext.com]> > Sent: Thursday, July 11, 2002 11:14 PM> > To: eLink Recipient> > Subject: How can i display the sum/difference of 2 columns?> >> >> > How can i display the sum/difference of 2 columns?> > Posted by hpglc on 07/11/2002 11:13 PM> >> > Hello> >> > If i have an sql which looks something like> >> > select (select count(*) from A), (select count(*) from B)from DUAL> >> > How can i display a 3rd column which will be the difference> > between the 1st and 2nd column? Will using column aliases work? like> >> > select (select count(*) from A) as Col1, (select count(*) from

> > as Col2, Col1-Col2 from DUAL> >> > Please help?> >> >> > [To reply to this thread, use your normal e-mail reply function.]> >> > ============================================================> >> > Discussion: Livelink LiveReports Discussion> > https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objI> > d=2249677&objAction=view> >> > Livelink Server:> > https://knowledge.opentext.com/knowledge/livelink.exe> >> >> >>> [To reply to this thread, use your normal e-mail reply function.]>> ============================================================>> Topic: How can i display the sum/difference of 2 columns?> https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2786356&objAction=viewDiscussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe

Paraskevas_Dalianis_(dsiadmin_-_(deleted))

I have found two different opinions in the discussion list.Is it finally possible to call a stored procedure from LiveReports (SQL Server)?If yes are there any constraints or prerequisites?If not what can we do?Thanks

Victoria_Freihofer_(rgsinc01admin_-_(deleted))

Here is the SQL from a report that we use that creates a column based on calculation of the difference between two other columns. (#Days Duration) It works fine without any noticable performance problems.SELECT SubWork_Title TITLE, to_char (Work_DateInitiated,'MM/DD/YYYY') INITIATED, to_char (Work_DateCompleted,'MM/DD/YYYY') COMPLETED, (Work_DateCompleted-Work_DateInitiated+1) "#DAYS DURATION" FROM WWork, WSubWork, DTree, WMap WHERE (dtree.dataid=wmap.map_mapobjid) AND (WSubWork.SubWork_WorkID=WWork.Work_WorkID) AND subwork_mapid=map_mapid AND dtree.dataid IN('977166', '977341', '1867754', '1255850', '1257044', '2585978') AND work_dateinitiated >= %1 and work_dateinitiated <= %2 AND Work_Status= -3 ORDER BY Work_DateInitiated%1 and %2 are date inputs for the userdtree.dataid numbers are object ids for workflow templates-----------------