Sitemap

Amicuk Programming Answers

How to make the query in one single query? No Union please....

2015-10-11   Views:0

Advertisement

Hi, I have sets of around 18 queries which is finding counts in many scenarios. I wanted to have a single query for this. which diplays the result in a single row.Is there a way , I heared analytical functions are good options ,but I am new to Oracle

Hi,
I have sets of around 18 queries which is finding counts in many scenarios. I wanted to have a single query for this. which diplays the result in a single row.Is there a way , I heared analytical functions are good options ,but I am new to Oracle . Please help me.
select count(*) total_count from emp; --100
select coun(*) MGR_count from emp where job='MGR'-- 10
select count(*) dept_count from emp where deptno in(10 ,20)-- 75
output..
TOTAL_COUNT     MGR_COUNT      DEPT_COUNT
100                                   10             75
Thanks

The replay answer
Advertisement
This:
select count(*) total_Count
      ,count(decode(job,'MGR',1,null)) mgr_count
      ,count(case when deptno in (10,20) then
               1
             else
               null
             end
            ) dept_count
from emp;
untested

Go to See the other 3 answers

Query with union

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I have three table: Candidates: (candidate_id primary key) Purhcase_orders: (Po_number primary key) Bids: (bid_number primary key) Just wanted to retrieve candidate information like candidate_id, first_name etc, along with count of purchase order[More]

Returning a count from a query using Union

Category:DefaultRelease time:2015-10-11Views:130

Hi. I'm attempting to select a count from this query and then display the total in a message, but I'm getting a 'Too many rows returned' (ORA-01422). Can anyone tell me how I can achieve a total for this query?          SELECT nvl(count(*),0)       [More]

Select query with UNION clause in database adapter

Category:DefaultRelease time:2015-10-11Views:130

Friends, I have got a SQL query with two UNION clause withing it. like select a,b,c from a union select a,b,c from b The schema generated is like below in sequence <element>a</element> <element>b</element> <element>c</elem[More]

Query with Union to Show Ship to of Both Supplier & Customer BP's

Category:DefaultRelease time:2015-10-11Views:130

Hi Guys, I need to do a query that I can use in a formatted search, that will allow me to select from a list that shows both BP Ship to Addresses from The Customer and Supplier Tables. I know I have to use a union, however I have no clue where to sta[More]

Command Query using UNION

Category:DefaultRelease time:-0001-11-30Views:130

Hello, I may be barking up the wrong tree and asking this question in the wrong forum so please accept my apologies if this is the case. I have access to a MYSql database through a ODBC connection and would like to create a report based on four table[More]

Writing query using UNION Operator

Category:DefaultRelease time:-0001-11-30Views:130

Question - (1) Write an SQL Statement to list the following items: Customer ID, Customer Name, number of invoices, sum of total for invoices. Ensure that all customers are returned in the result set. Answer for the above is written as below by one pe[More]

Query using Union All and CTEs is slow

Category:DefaultRelease time:-0001-11-30Views:130

TypePatient [ednum] int NOT NULL,  PK [BackgroundID] int NOT NULL, FK [Patient_No] varchar(50) NULL, FK [Last_Name] varchar(30) NULL, [First_Name] varchar(30) NULL, [ADateTime] datetime NULL, Treat [ID] int NOT NULL, PK [Ednum] numeric(10, 0) NOT NUL[More]

Sql query using union

Category:DefaultRelease time:-0001-11-30Views:130

hi i need the query to fetch the data ... i have two table 1. plan 2. voidplan each table has id uniquely, and each table has rep_id but it is not unique. i retrieved the data from both table using union. now i need the data for the rep who is in bot[More]

Where to place ORDER BY clause when a query includes UNION

Category:DefaultRelease time:-0001-11-30Views:130

I have a query that has UNION in it. could you please tell me where to place the ORDER BY clause. Because it's throwing an error if i place the ORDER BY clause at the endBecause you are using the UNION set operator, you need to either specifically li[More]

Urgent! How to use push_pred to optimize query with UNION in 10g?

Category:DefaultRelease time:-0001-11-30Views:130

Hi, We are facing slow query performance in 10g database. Appreciate if anyone could advise on how to optimize the performance by using push pred? Or is there any other ways. Thanks in advance. Cheers, SCdont post duplicate postRead other 2 answers[More]

Query with UNION option

Category:DefaultRelease time:-0001-11-30Views:130

Hi , I have one partition table.The partitions are created datewise.Now when i have to fire select query on that talble i am firing below querry Select count(1) from table A where createdate between ('01-JAN-2012 00:00:00','DD-MON-YYYY HH24:MI:SS') A[More]

Hot
Hello, We have PI 7.1 with CTS+ configured. However, there was a condition that an object was deleted manually in PRD system not via CTS+. After this all the transports that were connected to that object and transported to PRD system were imported su [More]
Hi All Can some one tell me if we have an option to create a folders, if yes please guide me on this Regards Austin Solved! Go to Solution.Folder where? please be specific Click if you want to Thank someone. If Problem is resolved, so that others can [More]
I got charged for the whole season but only have one episode available to me and it's not showing up in my recently purchased file!Paying for them twice, I mean.  I've already paid for them once.Read other 2 answers [More]
A Long Numbers file can not get emailed properly (in a readable format) when it is emailed from the iPad using ios8. The entire file gets squeezed into oa ne page PDF when emailed. I don't want to go home and email the work from a desktop computer. I [More]
Hello, Any suggestions on calling a stored pl/sql function from a UIX page and returning the output of the function to the same page? Thanks, -JeffHi Jaap, I followed the instructions on the link you gave me and when I ran my test application I recei [More]
I guess I'm wondering if it will download now and the movie be on my PC for the next 30 days until I start to watch it? Or will I need internet access while I'm away to watch it. I'd rather rent now and not buy it.As far as I know rentals can only be [More]
We had an application running on Polite (Oracle Lite 5.0.1), on a windows2000 client, executing from Forms6i. This worked fine. Now (corporative requirements), it has to run on a client WindowsXp Profesional. We have installed Forms9i but we can't fi [More]
My Oracle revision is 10.2.0.4 I have a full backup of a Production database and I want to restore just ONE tablespace from the backupset to another database instance on a different LPAR. I have read the documentation on creating and updataing duplic [More]
I recently began transfering my sound libraries from a drive on my old G4 Dual 1.42Ghz to the drive on my "pre-owned" Dual 2.0Ghz G5. I had a folder on my G4 with Rex files in them. When I transfered them over to the G5, I noticed they had becom [More]
The insert is working fine from Oracle forms to the Sybase database. However, when I exit the application it does a rollback (removing all the inserts). I have tried putting NULL; in the ON-ROLLBACK and ON-SAVEPOINT triggers (as recommended in the do [More]