Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Join 2 SQL queries into one
Top Forums Programming Join 2 SQL queries into one Post 302938366 by Lord Spectre on Sunday 15th of March 2015 07:27:48 AM
Old 03-15-2015
Join 2 SQL queries into one
Dear community,
could someone help me to join 2 queries into one?
Basically it's the same query with different clauses (please notice the FIELD3 filters and related counters into the subquery):
Code:
SELECT  A.FIELD1,A.FIELD2,A.FIELD3
   FROM 
   TABLE A 
   INNER JOIN
   (
    SELECT FIELD1, COUNT(1) 
    FROM TABLE
    where SUBSTR(FIELD3,1,5)='33377' and
    timestamp between sysdate - interval '20' minute and sysdate - interval '2' minute
    GROUP BY FIELD1
    HAVING COUNT(1) >= 100
    ) B
    ON A.FIELD1 = B.FIELD1 where ...some other clauses who interacts with FIELD4,5,6,etc... ;

Code:
SELECT  A.FIELD1,A.FIELD2,A.FIELD3
   FROM 
   TABLE A 
   INNER JOIN
   (
    SELECT FIELD1, COUNT(1) 
    FROM TABLE
    where SUBSTR(FIELD3,1,5) != '33377' and
    timestamp between sysdate - interval '20' minute and sysdate - interval '2' minute
    GROUP BY FIELD1
    HAVING COUNT(1) >= 150
    ) B
    ON A.FIELD1 = B.FIELD1 where ...some other clauses who interacts with FIELD4,5,6,etc... ;

What's my goal? Let me explain better.
I have an Oracle DB with a table with some fields, for this query a need only 3 fields as shown below.
I'm trying to make a query with multiple counts. What I need is a query that output a list who exceeded a specific count based on the first 5 digits of FIELD3. Let me do a specific example:

This is what I have into the DB:
Code:
FIELD1               FIELD2               FIELD3          
1234567314           333776543585218      333771434591151
1234567871           333771451776784      333771432365581
1234567314           333776543585218      333771240553976
1234567314           333776543585218      333773861003473
1234567314           333776543585218      333773861003473
1234567314           333776543585218      333023861003473
1234567314           333776543585218      333023861003473
1234567314           333776543585218      333023861003473
1234567337           333773660813075      333773650804767
1234567137           333773660798439      333771222628311
1234567319           333776543585219      333773660667594
1234567314           333776543585218      333901451463400
1234567314           333776543585218      333901451463400

Now, I want to output numbers in field1 in the following way:
OUTPUT FIELD1 (and related FIELD2 and FIELD3) who are exceeded a COUNT1 (EG: 3) based on FIELD3 having the same 5 first digits (33377)
OUTPUT FIELD1 (and related FIELD2 and FIELD3) who are exceeded a COUNT2 (EG: 10) based on FIELD3 NOT having the same 5 first digits (33377)

So, in the upper example, my output will be:
Code:
1234567314           333776543585218      333771434591151
1234567314           333776543585218      333771240553976
1234567314           333776543585218      333773861003473
1234567314           333776543585218      333773861003473
1234567314           333776543585218      333023861003473
1234567314           333776543585218      333023861003473
1234567314           333776543585218      333023861003473
1234567314           333776543585218      333901451463400
1234567314           333776543585218      333901451463400

33377 = 4 occurencies
Others = 5 occurences
First threshold exceeded the count so, report all rows.

Basically the 2 queries reported above works perfect, but I would like to join them into one to minimize query time and have an unique output.

Many thanks.
Lucas
 

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

SQL queries in background?

I have to query a DB2 database, and sometimes they take a long time to produce results. Can I run these queries in the background, and if so, where will the results appear? (1 Reply)
Discussion started by: jpprial
1 Replies

2. UNIX for Dummies Questions & Answers

shell script for sql queries

Hi All, I have written 4 sql queries . Now I want to write one SHELL SCRIPTING program for all these queries... i.e 1.select * from head; 2. select * from detail; 3. delete from head; 4. delete from detail; Please let me know how to write a shell script... Thank you (1 Reply)
Discussion started by: user71408
1 Replies

3. Shell Programming and Scripting

Select SQL Queries Option

count.sh#!/bin/ksh SQL1=`sqlplus -s usr/pwd @count.sql $1 $2 $3` SQL2=`sqlplus -s usr/pwd @selectall.sql $1 $2 $3` LIST="Count Select_All" select i in $LIST do if then echo $SQL1 elif then echo $SQL2 fi done (2 Replies)
Discussion started by: killboy
2 Replies

4. Programming

sql,multiple join,outer join issue

example sql: select a.a1,b.b1,c.c1,d.d1,e.e1 from a left outer join b on a.x=b.x left outer join c on b.y=c.y left outer join d on d.z=a.z inner join a.t=e.t I know how single outer or inner join works in sql. But I don't really understand when there are multiple of them. can... (0 Replies)
Discussion started by: robbiezr
0 Replies

5. Emergency UNIX and Linux Support

Insert data into sql queries from a file

Hello friends, I need to insert data from a file to another. I need this to form an sql query file which will consist of 50.000 INSERT INTO sentences. my sql query file will consist of 50.000 times the below line consecutively: insert into subscriber... (6 Replies)
Discussion started by: EAGL€
6 Replies

6. Shell Programming and Scripting

Script (with sql queries) not working using cron

Hi all, I have script, which performing sql queries and put output into file. When I run this script manually, its working fine, but when I want to schedule it with cron I am getting errors... I defined LD_LYBRARY_PATH and ,but no result. After I defined it, I am getting error: # more... (4 Replies)
Discussion started by: nypreH
4 Replies

7. Shell Programming and Scripting

Code needed to get sql queries

Hi i need code to get sql queries through a shell script for a text file input which contain the service ids iputfile I-H-2048-10GB-M I-H-4096-12GB-M I-H-2048-p1000-M the code should contain below queries among which service_id is replacable with value from input file. ... (4 Replies)
Discussion started by: surender reddy
4 Replies

8. AIX

Convert MS Access Queries to AIX SQL

Unix Team, I'm a recent college graduate entering in the " real world" and boy let me tell you it can quite challenging. I just got a job with a large fortune 50 company lets just say that my expectations and tasks have expanded since I first got hired. Last week I got assigned a pretty big... (9 Replies)
Discussion started by: techstudent01
9 Replies

9. Shell Programming and Scripting

Storing multiple sql queries output into variable by running sql command only once

Hi All, I want to run multiple sql queries and store the data in variable but i want to use sql command only once. Is there a way without running sql command twice and storing.Please advise. Eg : Select 'Query 1 output' from dual; Select 'Query 2 output' from dual; I want to... (3 Replies)
Discussion started by: Rokkesh
3 Replies

10. Shell Programming and Scripting

Join, merge, fill NULL the void columns of multiples files like sql "LEFT JOIN" by using awk

Hello, This post is already here but want to do this with another way Merge multiples files with multiples duplicates keys by filling "NULL" the void columns for anothers joinning files file1.csv: 1|abc 1|def 2|ghi 2|jkl 3|mno 3|pqr file2.csv: 1|123|jojo 1|NULL|bibi... (2 Replies)
Discussion started by: yjacknewton
2 Replies
All times are GMT -4. The time now is 01:48 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy