|
View previous topic :: View next topic
|
| Author |
Message |
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
Hi Team,
I have 15 SQL's and each one is calculating the Metrics to be pulled up on. I need to create views in optimized Manner to have 4-5 Views Max. Having said i am getting error on duplicate columns on "Created_TS" timestamp column.
Created_TS & Ended_TS were two most mainly used columns for all SQL selects to pull metrics .
| Code: |
create or replace view `myview01`
as(
with call_volume as (
select
distinct DATE(connected_TS),
DATE(ended_TS),
count(call_id) as call_volume
from Test_Call_Log_Table
where Status <> ''
and connected_at is Not NULL
and ended_at is Not Null
group by 1,2
order by 1,2
),
va_volume as (
select
distinct DATE(connected_TS),
DATE(ended_TS),
count(call_id) as call_volume,
type
from Test_Call_Log_Table
where Status = Finished
and type = 'VA'
and connected_at is Not NULL
and ended_at is Not Null
group by 1,2,4
order by 1,2,4
)
select * from call_volume, va_volume)
;
|
| Code: |
Error: VIEW has duplicates on connected_TS
|
Please advise how to come out of Duplicate issue and also how we can optimize views .Since columns will be different in each SQL of total 15 SQL's.
Please help. |
|
| Back to top |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2023
Location: USA
|
|
|
|
Just to understand it, try to run your queries separately
| Code: |
. . . . . .
select * from call_volume |
and
| Code: |
. . . . . . .
select * from va_volume |
Review both results (or present them at this forum), then try to explain: what do you expect to receive from
| Code: |
. . . . . . . .
select * from call_volume, va_volume |
 |
|
| Back to top |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
Hi ,
Please find the expected output . Created_TS, Ended_TS may or may not be the same , but i want these to be presented in view like .
| Code: |
select * from my_view
Connected_TS Ended_TS call_volume Va_Volume
16-03-2023 16-03-2023 4 2
17-03-2023 17-03-2023 48 3
20-03-2023 20-03-2023 142 15
21-03-2023 21-03-2023 108 14
|
|
|
| Back to top |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
Hi,Please find the each SQL output
| Code: |
select * from call_volume
Connected_TS Ended_TS call_volume
16-03-2023 16-03-2023 4
17-03-2023 17-03-2023 48
20-03-2023 20-03-2023 142
21-03-2023 21-03-2023 108
;
select * from VA_Volume
Connected_TS Ended_TS Va_Volume
16-03-2023 16-03-2023 2
20-03-2023 20-03-2023 3
21-03-2023 21-03-2023 15
;
|
|
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
You can do Union for these two select queries in one view and get the results ..
Else define separate views as you want with unique names |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2023
Location: USA
|
|
|
|
| balaji81_k wrote: |
Hi,Please find the each SQL output
| Code: |
select * from call_volume
Connected_TS Ended_TS call_volume
16-03-2023 16-03-2023 4
17-03-2023 17-03-2023 48
20-03-2023 20-03-2023 142
21-03-2023 21-03-2023 108
;
select * from VA_Volume
Connected_TS Ended_TS Va_Volume
16-03-2023 16-03-2023 2
20-03-2023 20-03-2023 3
21-03-2023 21-03-2023 15
;
|
|
My intention was, you to guess that you may need some explicit operation to deal with two of your views/tables. But Rohit has already pointed it to you, that UNION is needed…
P.S.
Your question is very similar to another one from yourself, where LEFT JOIN is needed rather than UNION. Here it’s not obvious: what you really need for this particular task? |
|
| Back to top |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
| Rohit Umarjikar wrote: |
You can do Union for these two select queries in one view and get the results ..
Else define separate views as you want with unique names |
Hi Rohit, Yes you are correct by the problem for the Union the number of columns should be equal , Some times number of columns will vary for my case. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| If they vary then dummy out values for other selects where you don’t have to comply to UNION rules. This would still work .. and also add unique identifier for each select and use as needed . |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2023
Location: USA
|
|
|
|
The major point of this discussion is: this expression has no sense.
| Code: |
| select * from call_volume, va_volume |
You need to understand: how exactly you need to deal with two (different!) tables/views in your particular case?
We here do not know your goal, and you did not clarify it for us.
What is your expected result when the results of two separate queries above are somehow combined? |
|
| Back to top |
|
|
|
|
