Home » SQL & PL/SQL » SQL & PL/SQL » Outer query (Oracle11g)
Outer query [message #648991] Wed, 09 March 2016 09:35 Go to next message
ssyr22
Messages: 38
Registered: December 2015
Location: PUNE
Member
select date_id,
       sum(case
             when fmu.ip is not null then
              impressions
             else
              0
           end) as glam_internal_ip,
       sum(case
             when fmu.ip is null then
              impressions
             else
              0
           end) as glam_external_ip
select date_id,fmu.ip
from   fact_media_user_day fmu, glam_internal_ip gi
where  fmu.ip = gi.ip_address(+)
and    date_id = 20160301
--and    date_id <= 20160307
group  by date_id;


In abode query I have to display sum(impresions) based on join condition of IP column.Also I have to display sum(impresions)for non matching of IP column .HOw can i display it?For matchind rows i have to used outer join in fmu table.
Can You please tell me?
Re: Outer query [message #648993 is a reply to message #648991] Wed, 09 March 2016 09:42 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I am not sure what code is posted above, but I strongly suspect that it is NOT valid SQL syntax.

How, what, & why does SELECT appear twice above?

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide

Only a fool or amateur stores DATE datatype as NUMBER as evidenced by below:
>and date_id = 20160301
Re: Outer query [message #648994 is a reply to message #648993] Wed, 09 March 2016 09:45 Go to previous messageGo to next message
ssyr22
Messages: 38
Registered: December 2015
Location: PUNE
Member
yes. Date_id is number datatype in my table.I have to mach IP column value in both table.
Re: Outer query [message #648995 is a reply to message #648994] Wed, 09 March 2016 09:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
we don't have your tables.
we don't have your data.
we don't have your requirements.
I do not know what the expected/desire results should look like.
we can't run, test, correct your code without all of the above.
If you expect us to help you, then you need to help us by providing what we require.
Re: Outer query [message #648996 is a reply to message #648991] Wed, 09 March 2016 10:17 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Please provide a Test Case to receive help.
Re: Outer query [message #648997 is a reply to message #648996] Wed, 09 March 2016 10:36 Go to previous messageGo to next message
ssyr22
Messages: 38
Registered: December 2015
Location: PUNE
Member
 Table Data For : Glam_internal_ip
OFFICE	IP	IP_ADDRESS
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.53
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.54
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.55
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.56
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.57
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.58
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.59
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.60
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.61
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.62
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.63
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.64
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.65
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.66
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.67
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.68
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.69
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.70
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.71
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.72
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.73
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.74
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.75
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.76
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.77
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.78
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.79
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.80
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.81
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.82
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.83
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.84
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.85
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.86
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.87
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.88
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.89
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.90
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.91
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.92
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.93
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.94
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.95
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.96
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.97
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.98
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.99
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.100
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.101
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.102
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.103
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.104
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.105
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.106
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.107
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.108
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.109
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.110
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.111
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.112
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.113
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.114
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.115
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.116
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.117
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.118
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.119
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.120
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.121
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.122
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.123
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.124
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.125
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.126
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.127
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.128
SF colo (Gogrid / Datapipe)	216.121.20.0/23	216.121.20.129

Re: Outer query [message #648999 is a reply to message #648997] Wed, 09 March 2016 10:41 Go to previous messageGo to next message
ssyr22
Messages: 38
Registered: December 2015
Location: PUNE
Member
Data for Fact)media_user_day
DATE_ID	IMPRESSIONS	IP
20160301.00	2.00	172.56.18.116
20160301.00	1.00	108.255.65.49
20160301.00	24.00	210.133.223.252
20160301.00	1.00	81.152.116.135
20160301.00	1.00	85.255.233.3
20160301.00	7.00	76.124.253.238
20160301.00	1.00	162.228.120.27
20160301.00	1.00	193.81.146.140
20160301.00	5.00	96.28.61.7
20160301.00	3.00	116.65.120.159
20160301.00	1.00	82.219.30.13
20160301.00	1.00	175.223.26.223
20160301.00	1.00	172.56.5.202
20160301.00	8.00	78.49.60.58
20160301.00	1.00	54.255.178.219
20160301.00	5.00	73.33.139.254
20160301.00	1.00	80.12.51.130
20160301.00	2.00	112.71.6.101
20160301.00	1.00	109.235.67.83
20160301.00	5.00	178.189.197.111
20160301.00	1.00	172.56.18.79
20160301.00	3.00	76.176.136.30
20160301.00	1.00	24.246.70.222
20160301.00	1.00	50.173.27.220
20160301.00	1.00	67.52.165.42
20160301.00	1.00	75.176.139.174
20160301.00	2.00	184.13.26.128
20160301.00	1.00	89.241.53.202
20160301.00	2.00	209.65.177.130
20160301.00	1.00	185.69.144.84

Re: Outer query [message #649001 is a reply to message #648999] Wed, 09 March 2016 10:53 Go to previous messageGo to next message
ssyr22
Messages: 38
Registered: December 2015
Location: PUNE
Member
Give me some hint? please
Re: Outer query [message #649002 is a reply to message #649001] Wed, 09 March 2016 10:57 Go to previous message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
A test case consists of DDL scripts to create any required tables or indexes, and DML scripts to populate those tables with data.
Previous Topic: dependency question
Next Topic: Understanding locking
Goto Forum:
  


Current Time: Thu Mar 28 06:32:51 CDT 2024