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):
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:
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:
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.
In your results, do you care why the row was selected? If you need to know if the row met the "='333777'" clause or the "!='33777'" clause, you're going to have to add another column to your result set so you can tell them apart.
And even then, I'm not sure it's possible from a quick look at what you're trying to do. Well, you can just do a trivial "(long query1) UNION (long query2)". But that won't be any faster than what you're running now, and might in fact be slower because you might have to spend time pulling the results apart into the two separate sets that you're getting with two queries.
But if you do figure out a way to combine the queries, is it worth it? They're already complex enough that you need help to combine them. What happens after you combine them and find you need to modify them?
Why do you think you need to do this? Is it using CPU cycles and time on the database server? Well, that's why that database server was bought in the first place. If you're not using all the CPU cycles on it your company wasted money.
This seems like a bad idea to me from what you've posted. It's already very complex, but it works now. Making it more complex in ways you don't understand is going to help how? What happens if the more complex query breaks?
Just because you write code on your job doesn't mean every problem is best solved by writing more code.
Hello achenle.
I respect (and partially agree) your point of view. I need one query because I have to put it into a complex bash script that analyze unique output in some way. What you said is partially true. From one side the complexity make hard to join into one, from other side I'm kindly expecting feedback from other users to increase query speed and usability. For example another user from another community suggest me to use the analytic functions. As you can see below, two queries are joined into one, and the result is a very fast query.
I'm still discussing with him because seem the counters are not respected, so, after the query I got some field1 occurrences lower then 100 or 150. Btw, maybe I need only to fix some things, and the result is in a fast query, many more then mine. That's why I'm asking to internet communities!
...
This is what I have into the DB:
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:
33377 = 4 occurencies
Others = 5 occurences
First threshold exceeded the count so, report all rows.
...
---------- Post updated at 12:49 PM ---------- Previous update was at 03:09 AM ----------
Assuming:
(a) your data is in a table called "t" and
(b) you want to use the additional filter and join conditions you posted in your original post,
here's how you could tweak the new query to fit those in:
This User Gave Thanks to durden_tyler For This Post:
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)