Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

union all and selects

Status
Not open for further replies.
timely

timely

Programmer
Jun 7, 2001
64
US
Can I do an outer select on a union all.

select fields
from(
select fields
from tables
where rules
union all
select fields
from tables
where rules
)
group by fields

 
Sort by date Sort by votes
Yes, give an alias name for your subquery result

select fields
from(
select fields
from tables
where rules
union all
select fields
from tables
where rules
) a
group by fields
 
Upvote 0 Downvote
Okay I tried this:

select a1, a2, a3,a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14
from(
select
CPN.PMO_DSC a1,
SUB.key_cde a2,
CPN.QTY_MAIL a3,
QTY_SOLD a4,
GRO_PCT a5,
0,0,0,0,
CAST(CPN.OTH_CPM AS DECIMAL(8,2)) a10,
CAST(CPN.TOTL_CST AS DECIMAL(8,2)) a11,
CAST(CPN.AVG_REV AS DECIMAL(8,2)) a12,
CAST(CPN.GRO_REV AS DECIMAL(8,2)) a13,
AVG(SUB.TERM) a14
from cirsub_m sub
LEFT join cpnpmo_m cpn on sub.key_cde = cpn.cpn_pmo1
left JOIN ARPPDH_M PDH ON SUB.ORD_NBR = PDH.DBT_NBR AND
SUB.PUB_CDE = PDH.SRC_REF
where cpn.pub_cde = 'FCM' AND substring(key_cde,1,1) = 'D' AND CTM_NBR = SRC_CTM AND
CPN.QTY_MAIL <> 0

union all

select
CPN.PMO_DSC a1,
SUB.key_cde a2,
0,0,0,
COUNT(SUB.KEY_CDE) a6,
cast(ROUND(count(sub.key_cde)/qty_sold * 100,2)as decimal(8,2)) a7,
qty_sold - count(sub.key_cde) a8,
100 - cast(ROUND(count(sub.key_cde)/qty_sold * 100,2)as decimal(8,2)) a9,
0,0,0,0,0
from cirsub_m sub
LEFT join cpnpmo_m cpn on sub.key_cde = cpn.cpn_pmo1
left JOIN ARPPDH_M PDH ON SUB.ORD_NBR = PDH.DBT_NBR AND
SUB.PUB_CDE = PDH.SRC_REF
where cpn.pub_cde = 'FCM' AND substring(key_cde,1,1) = 'D' AND SUB.BIL_STS = 'P' AND
CTM_NBR = SRC_CTM AND CPN.QTY_MAIL <> 0
)
group by a1, a2, a3,a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14

and got this error:
Server: Msg 156, Level 15, State 1, Line 40
Incorrect syntax near the keyword 'group'.
 
Upvote 0 Downvote
Try This

select a1, a2, a3,a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14
from(
select
CPN.PMO_DSC a1,
SUB.key_cde a2,
CPN.QTY_MAIL a3,
QTY_SOLD a4,
GRO_PCT a5,
0,0,0,0,
CAST(CPN.OTH_CPM AS DECIMAL(8,2)) a10,
CAST(CPN.TOTL_CST AS DECIMAL(8,2)) a11,
CAST(CPN.AVG_REV AS DECIMAL(8,2)) a12,
CAST(CPN.GRO_REV AS DECIMAL(8,2)) a13,
AVG(SUB.TERM) a14
from cirsub_m sub
LEFT join cpnpmo_m cpn on sub.key_cde = cpn.cpn_pmo1
left JOIN ARPPDH_M PDH ON SUB.ORD_NBR = PDH.DBT_NBR AND
SUB.PUB_CDE = PDH.SRC_REF
where cpn.pub_cde = 'FCM' AND substring(key_cde,1,1) = 'D' AND CTM_NBR = SRC_CTM AND
CPN.QTY_MAIL <> 0

union all

select
CPN.PMO_DSC a1,
SUB.key_cde a2,
0,0,0,
COUNT(SUB.KEY_CDE) a6,
cast(ROUND(count(sub.key_cde)/qty_sold * 100,2)as decimal(8,2)) a7,
qty_sold - count(sub.key_cde) a8,
100 - cast(ROUND(count(sub.key_cde)/qty_sold * 100,2)as decimal(8,2)) a9,
0,0,0,0,0
from cirsub_m sub
LEFT join cpnpmo_m cpn on sub.key_cde = cpn.cpn_pmo1
left JOIN ARPPDH_M PDH ON SUB.ORD_NBR = PDH.DBT_NBR AND
SUB.PUB_CDE = PDH.SRC_REF
where cpn.pub_cde = 'FCM' AND substring(key_cde,1,1) = 'D' AND SUB.BIL_STS = 'P' AND
CTM_NBR = SRC_CTM AND CPN.QTY_MAIL <> 0
) a
group by a1, a2, a3,a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14
 
Upvote 0 Downvote
Ok the only diffrence I see is the 'a' near the closing of the main select statement and I got this error:

Server: Msg 8155, Level 16, State 2, Line 1
No column was specified for column 6 of 'a'.
Server: Msg 8155, Level 16, State 1, Line 1
No column was specified for column 7 of 'a'.
Server: Msg 8155, Level 16, State 1, Line 1
No column was specified for column 8 of 'a'.
Server: Msg 8155, Level 16, State 1, Line 1
No column was specified for column 9 of 'a'.
 
Upvote 0 Downvote
UNion statements must have the exact same structure in each one of the union selects, Ie they must each have the same number of fields of the same datatype. The query will take it's names fromthe first selct inthe union. You didn;t have a column a6 named inthe first statement so it threw an error.
select a1, a2, a3,a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14
from(
select
CPN.PMO_DSC a1,
SUB.key_cde a2,
CPN.QTY_MAIL a3,
QTY_SOLD a4,
GRO_PCT a5,
0 a6,0 a7,0 a8,0 a9,
CAST(CPN.OTH_CPM AS DECIMAL(8,2)) a10,
CAST(CPN.TOTL_CST AS DECIMAL(8,2)) a11,
CAST(CPN.AVG_REV AS DECIMAL(8,2)) a12,
CAST(CPN.GRO_REV AS DECIMAL(8,2)) a13,
AVG(SUB.TERM) a14
from cirsub_m sub
LEFT join cpnpmo_m cpn on sub.key_cde = cpn.cpn_pmo1
left JOIN ARPPDH_M PDH ON SUB.ORD_NBR = PDH.DBT_NBR AND
SUB.PUB_CDE = PDH.SRC_REF
where cpn.pub_cde = 'FCM' AND substring(key_cde,1,1) = 'D' AND CTM_NBR = SRC_CTM AND
CPN.QTY_MAIL <> 0

union all

select
CPN.PMO_DSC,
SUB.key_cde,
0,0,0,
COUNT(SUB.KEY_CDE),
cast(ROUND(count(sub.key_cde)/qty_sold * 100,2)as decimal(8,2)),
qty_sold - count(sub.key_cde),
100 - cast(ROUND(count(sub.key_cde)/qty_sold * 100,2)as decimal(8,2)) ,
0,0,0,0,0
from cirsub_m sub
LEFT join cpnpmo_m cpn on sub.key_cde = cpn.cpn_pmo1
left JOIN ARPPDH_M PDH ON SUB.ORD_NBR = PDH.DBT_NBR AND
SUB.PUB_CDE = PDH.SRC_REF
where cpn.pub_cde = 'FCM' AND substring(key_cde,1,1) = 'D' AND SUB.BIL_STS = 'P' AND
CTM_NBR = SRC_CTM AND CPN.QTY_MAIL <> 0
) a
group by a1, a2, a3,a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14
 
Upvote 0 Downvote
I tried this and got that is wanted the actual names in the group by but then I put the names of the fields in the group by and it still did not like it.

select a1, a2, a3,a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14
from(
select
CPN.PMO_DSC a1,
SUB.key_cde a2,
CPN.QTY_MAIL a3,
QTY_SOLD a4,
GRO_PCT a5,
0 a6,0 a7,0 a8,0 a9,
CAST(CPN.OTH_CPM AS DECIMAL(8,2)) a10,
CAST(CPN.TOTL_CST AS DECIMAL(8,2)) a11,
CAST(CPN.AVG_REV AS DECIMAL(8,2)) a12,
CAST(CPN.GRO_REV AS DECIMAL(8,2)) a13,
AVG(SUB.TERM) a14
from cirsub_m sub
LEFT join cpnpmo_m cpn on sub.key_cde = cpn.cpn_pmo1
left JOIN ARPPDH_M PDH ON SUB.ORD_NBR = PDH.DBT_NBR AND
SUB.PUB_CDE = PDH.SRC_REF
where cpn.pub_cde = 'FCM' AND substring(key_cde,1,1) = 'D' AND CTM_NBR = SRC_CTM AND
CPN.QTY_MAIL <> 0

union all

select
CPN.PMO_DSC a1,
SUB.key_cde a2,
0 a3,0 a4,0 a5,
COUNT(SUB.KEY_CDE) a6,
cast(ROUND(count(sub.key_cde)/qty_sold * 100,2)as decimal(8,2)) a7,
qty_sold - count(sub.key_cde) a8,
100 - cast(ROUND(count(sub.key_cde)/qty_sold * 100,2)as decimal(8,2)) a9,
0 a10,0 a11,0 a12,0 a13,0 a14
from cirsub_m sub
LEFT join cpnpmo_m cpn on sub.key_cde = cpn.cpn_pmo1
left JOIN ARPPDH_M PDH ON SUB.ORD_NBR = PDH.DBT_NBR AND
SUB.PUB_CDE = PDH.SRC_REF
where cpn.pub_cde = 'FCM' AND substring(key_cde,1,1) = 'D' AND SUB.BIL_STS = 'P' AND
CTM_NBR = SRC_CTM AND CPN.QTY_MAIL <> 0
) a
group by a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14
 
Upvote 0 Downvote
Thanks on the help I posted a new one hoping to get more to look. I am not sure why these errors are happening.

Oh the learning curve for a diffrent version of sqls
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

peac3
  • Locked
  • Question
Gaps and islands
Replies
8
Views
599
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
Derive staus date time by aligning start and end datetime
Replies
7
Views
621
Andrzejek
Andrzejek
grnzbra
  • Locked
  • Question
Adding Text to and XML Field 1
Replies
2
Views
321
grnzbra
grnzbra
TheBugSlayer
  • Locked
  • Question
Transaction with SELECT and UPDATE with XLOCK causing deadlocks 2
Replies
10
Views
878
gmmastros
gmmastros
Andrzejek
  • Locked
  • Question
Data types and sizes
Replies
18
Views
929
gmmastros
gmmastros

Part and Inventory Search

Sponsor

Back
Top