IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

DB2 Views with Multiple SQL & Columns


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 155

PostPosted: Wed Apr 12, 2023 10:07 pm
Reply with quote

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
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 1992
Location: USA

PostPosted: Wed Apr 12, 2023 11:03 pm
Reply with quote

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

icon_sad.gif icon_sad.gif icon_sad.gif
Back to top
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 155

PostPosted: Wed Apr 12, 2023 11:26 pm
Reply with quote

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
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 155

PostPosted: Wed Apr 12, 2023 11:37 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Apr 13, 2023 3:44 am
Reply with quote

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
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 1992
Location: USA

PostPosted: Thu Apr 13, 2023 4:23 am
Reply with quote

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
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 155

PostPosted: Thu Apr 13, 2023 12:23 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Apr 13, 2023 4:49 pm
Reply with quote

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
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 1992
Location: USA

PostPosted: Thu Apr 13, 2023 5:21 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts Remote Unload of CLOB Columns DB2 6
No new posts Multiple table unload using INZUTILB DB2 2
No new posts Grouping by multiple headers DFSORT/ICETOOL 7
No new posts Increase the number of columns in the... IBM Tools 3
Search our Forums:

Back to Top