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:
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)
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!
Code:
SELECT field1, field2, field3
FROM (SELECT t.*,
SUM(CASE WHEN field3 LIKE '33377%' THEN 1 ELSE 0 END) OVER (PARTITION BY field1) as cnthave,
SUM(CASE WHEN field3 NOT LIKE '33377%' THEN 1 ELSE 0 END) OVER (PARTITION BY field1) as cntnothave
FROM TABLE t
WHERE timestamp between sysdate - interval '20' minute and sysdate - interval '2' minute
) t
WHERE (Other conditions here) AND
(cnthave > 100 or cntnothave > 150);
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)
33377 = 4 occurencies
Others = 5 occurences
First threshold exceeded the count so, report all rows.
...
Code:
test@PDBORA12C>
test@PDBORA12C> --
test@PDBORA12C> with t (field1, field2, field3) as
2 (
3 select 1234567314, 333776543585218, 333771434591151 from dual union all
4 select 1234567871, 333771451776784, 333771432365581 from dual union all
5 select 1234567314, 333776543585218, 333771240553976 from dual union all
6 select 1234567314, 333776543585218, 333773861003473 from dual union all
7 select 1234567314, 333776543585218, 333773861003473 from dual union all
8 select 1234567314, 333776543585218, 333023861003473 from dual union all
9 select 1234567314, 333776543585218, 333023861003473 from dual union all
10 select 1234567314, 333776543585218, 333023861003473 from dual union all
11 select 1234567337, 333773660813075, 333773650804767 from dual union all
12 select 1234567137, 333773660798439, 333771222628311 from dual union all
13 select 1234567319, 333776543585219, 333773660667594 from dual union all
14 select 1234567314, 333776543585218, 333901451463400 from dual union all
15 select 1234567314, 333776543585218, 333901451463400 from dual
16 ),
17 t1 as (
18 select field1, field2, field3,
19 case when field3 like '33377%' then 'COND1' else 'COND2' end as status,
20 count(*) over (partition by case when field3 like '33377%' then 'COND1' else 'COND2' end, field1) as count
21 from t
22 ),
23 t2 as (
24 select field1, field2, field3, status, count
25 from t1
26 where status = 'COND1' and count > 3
27 or status = 'COND2' and count > 10
28 )
29 select *
30 from t
31 where t.field1 in (select field1 from t2)
32 ;
FIELD1 FIELD2 FIELD3
--------------- --------------- ---------------
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
9 rows selected.
Elapsed: 00:00:00.05
test@PDBORA12C>
test@PDBORA12C>
test@PDBORA12C>
---------- 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:
Code:
--
with t1 as (
select field1, field2, field3,
case when field3 like '33377%' then 'COND1' else 'COND2' end as status,
count(*) over (partition by case when field3 like '33377%' then 'COND1' else 'COND2' end, field1) as count
from t
where timestamp between sysdate - interval '20' minute and sysdate - interval '2' minute
),
t2 as (
select field1, field2, field3, status, count
from t1
where status = 'COND1' and count > 3
or status = 'COND2' and count > 10
)
select t.*
from t
where exists ( select null
from t2
where t.field1 = t2.field1
-- and ...some other clauses who interacts with FIELD4,5,6,etc...
)
;
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)
03-15-2015
