error expecting a union - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

error expecting a union

Author

Topic

gavakie
Posting Yak Master

221 Posts

Posted - 2009-02-10 : 16:32:31

Im getting a wierd error expecint a union after the keyword 'S' its not giving me a line number or anything.

Select totalbuyer as buyer,
case when nondistributor_sku_count is null then 0 else nondistributor_sku_count end as nondistributor_sku_count,
total_skus
From
(Select Count(distinct ttCa.pro_sku) as nondistributor_sku_count,
buyer
From
(
SELECT PRO.pro_id
,PRI.pri_id
,'SHORT SKU' sku_level
,PRO.pro_sku
,CA.*
FROM edw_batch_views.dd_compare_at CA
INNER JOIN edw_batch_views.dd_product PRO ON CA.ca_pro_id = PRO.pro_id
LEFT JOIN edw_batch_views.dd_pro_info PRI ON PRO.pro_id = PRI.pri_pro_od AND CA.ca_pri_id = PRI.pri_id
WHERE CA.ca_status = 'VERIFIED'
AND CA.ca_pri_id IS NULL
AND CAST(CA.ca_expiration_date AS DATE) >= current_date

UNION

SELECT PRO.pro_id
,PRI.pri_id
,'FULL SKU' sku_level
,PRO.pro_sku || '-' || PRI.pri_oa_sku
,CA.*
FROM edw_batch_views.dd_compare_at CA
INNER JOIN edw_batch_views.dd_product PRO ON CA.ca_pro_id = PRO.pro_id
INNER JOIN edw_batch_views.dd_pro_info PRI ON PRO.pro_id = PRI.pri_pro_od AND CA.ca_pri_id = PRI.pri_id
WHERE ca_status = 'VERIFIED'
AND CAST(ca_expiration_date AS DATE) >= current_date)ttCA

Inner Join

(

SELECT distinct item_stat.ssku as sku ,alt_buyer_name as buyer

FROM
(
SELECT I.item_sku_num ssku
,CAL.calendar_date hist_stat_dt
,alt_buyer_name
FROM edw_access_views.item I
INNER JOIN edw_access_views.item_dimension IDIM ON I.item_id = IDIM.item_id AND I.item_typ_cd = 'SKU'

INNER JOIN edw_access_views.item_status STAT ON I.item_id = STAT.item_id AND STAT.item_status_typ_cd =2
INNER JOIN edw_access_views.calendar CAL ON (CAL.calendar_date BETWEEN CAST(STAT.item_status_start_dttm AS DATE) AND COALESCE(CAST(STAT.item_status_end_dttm AS DATE), CURRENT_DATE))
WHERE
calendar_date = current_date
And idim.partner_name in ( 'BAKER & TAYLOR' ,
'BAKER & TAYLOR INC ' ,
'D AND H DISTRIBUTING',
'DSI SYSTEMS ',

'INGRAM BOOK COMPANY ',
'INGRAM ENTERTAINMENT INC',
'INGRAM MICRO ',

'PETRA INDUSTRIES ',
'ZAPPOSCOM INC ')
) ITEM_STAT
INNER JOIN
(
SELECT I.item_sku_num ssku
,I.item_full_sku_num full_sku
,CAL.calendar_date hist_inv_date
,INV.item_invntry_on_hand_unit_qty hist_qoh
FROM edw_access_views.item I
INNER JOIN item_dimension IDIM ON I.item_id=IDIM.item_id

INNER JOIN edw_access_views.item_invntry INV ON I.item_id = INV.item_id AND I.item_typ_cd = 'Full' AND INV.item_invntry_on_hand_unit_qty > 0 AND INV.item_invntry_vwpnt_typ_cd = 'B2C' AND INV.item_invntry_start_dt <> '1999-01-01'
LEFT JOIN edw_access_views.calendar CAL ON (CAL.calendar_date BETWEEN INV.item_invntry_start_dt AND COALESCE(INV.item_invntry_end_dt, CURRENT_DATE))
WHERE calendar_date =current_date
And idim.partner_name in ( 'BAKER & TAYLOR' ,
'BAKER & TAYLOR INC ' ,
'D AND H DISTRIBUTING',
'DSI SYSTEMS ',

'INGRAM BOOK COMPANY ',
'INGRAM ENTERTAINMENT INC',
'INGRAM MICRO ',

'PETRA INDUSTRIES ',
'ZAPPOSCOM INC ')
) HIST_INV ON ITEM_STAT.ssku = HIST_INV.ssku AND ITEM_STAT.hist_stat_dt = HIST_INV.hist_inv_date

)ttBuyer on ttCA.pro_sku = ttBuyer.sku
Group By 2
)tttemp

Full Join

(

Select buyer as totalbuyer, sum(sku) as total_skus
From
(Select buyer, count(sku) as sku
From
(Select tttemp2.buyer,tttemp2.sku
From
(SELECT BUYER, SKU

FROM
(SELECT I.ITEM_SKU_NUM SKU,
COALESCE(SOLD.PC, CASE WHEN D.PARTNER_IND = 'Y' THEN 0 ELSE 1 END) PC,
MIN(IST.ITEM_STATUS_START_DTTM (DATE)) FIRSTONSITE,
CASE WHEN (SOLD = 0 OR SOLD IS NULL) THEN 1 ELSE 0 END NOSALES, D.STORE, D.ALT_BUYER_NAME AS BUYER

FROM EDW_ACCESS_VIEWS.ITEM I

INNER JOIN ITEM_DIMENSION D ON (I.ITEM_ID = D.ITEM_ID AND I.ITEM_TYP_CD = 'FULL' )

INNER JOIN ITEM_STATUS IST ON I.PRNT_ITEM_ID = IST.ITEM_ID
AND IST.ITEM_STATUS_TYP_CD = '2'
AND IST.ITEM_STATUS_START_DTTM (DATE) <= current_date
AND (IST.ITEM_STATUS_END_DTTM IS NULL OR IST.ITEM_STATUS_END_DTTM (DATE) >=current_date)

INNER JOIN ITEM_INVNTRY INV ON INV.ITEM_ID = I.ITEM_ID
AND INV.ITEM_INVNTRY_VWPNT_TYP_CD = 'B2C'
AND INV.ITEM_INVNTRY_ON_HAND_UNIT_QTY > 0
AND INV.ITEM_INVNTRY_START_DT <= current_date
AND (INV.ITEM_INVNTRY_END_DT IS NULL OR ITEM_INVNTRY_END_DT >=(current_date)-7)

LEFT OUTER JOIN
(
SELECT I.ITEM_SKU_NUM SKU,
CASE WHEN (COALESCE(LIJ.TRANS_LINE_ITEM_3RD_ID,0)=0) THEN 1 ELSE 0 END "PC",
SUM(CASE WHEN (LIJ.TRANS_LINE_STATUS_TYP_CD <> 'CANCMPLT'
AND TRIM(LIJ.ORDER_TYP) NOT IN ('ADMIN','B2B')) THEN LIJ.ITEM_QTY ELSE 0 END) 'SOLD'
FROM EDW_ACCESS_VIEWS.LINE_ITEM_JUICE LIJ
JOIN SLS_TRANS_LINE STL ON (STL.SLS_TRANS_LINE_ID=LIJ.SLS_TRANS_LINE_ID AND STL.SLS_TRANS_ID=LIJ.SLS_TRANS_ID
AND STL.SLS_TRANS_DT = LIJ.SLS_TRANS_DT)
JOIN ITEM I ON I.ITEM_ID=STL.ITEM_ID
WHERE I.ITEM_END_DT IS NULL
AND I.ITEM_TYP_CD = 'FULL'
AND I.ITEM_STATUS_TYP_CD <> 5
AND STL.SLS_TRANS_DT BETWEEN (current_date)-7 AND current_date
GROUP BY 1,2
)SOLD
ON SOLD.SKU = I.ITEM_SKU_NUM
GROUP BY 1,2,4,5,6) NoSales

UNION

SELECT Buyer, Sku
FROM
(SELECT I.ITEM_SKU_NUM SKU,
CASE WHEN D.PARTNER_IND = 'Y' THEN 0 ELSE 1 END PC,
MIN(IST.ITEM_STATUS_START_DTTM (DATE)) FIRSTONSITE,
D.STORE, D.ALT_BUYER_NAME AS BUYER

FROM EDW_ACCESS_VIEWS.ITEM I

INNER JOIN ITEM_DIMENSION D ON (I.ITEM_ID = D.ITEM_ID AND I.ITEM_TYP_CD = 'FULL' )

INNER JOIN ITEM_STATUS IST ON I.PRNT_ITEM_ID = IST.ITEM_ID
AND IST.ITEM_STATUS_TYP_CD = '2'
AND IST.ITEM_STATUS_START_DTTM (DATE) <= current_date
AND (IST.ITEM_STATUS_END_DTTM IS NULL OR IST.ITEM_STATUS_END_DTTM (DATE) >=current_date)

INNER JOIN ITEM_INVNTRY INV ON INV.ITEM_ID = I.ITEM_ID
AND INV.ITEM_INVNTRY_VWPNT_TYP_CD = 'B2C'
AND INV.ITEM_INVNTRY_ON_HAND_UNIT_QTY > 0
AND INV.ITEM_INVNTRY_START_DT <= current_date
AND (INV.ITEM_INVNTRY_END_DT IS NULL OR ITEM_INVNTRY_END_DT >=current_date)

GROUP BY 1,2,4,5)TotalAndNewSKUS)ttTemp2
)ttcount

Where sku in
(
SELECT
EDW_ACCESS_VIEWS.ITEM.ITEM_SKU_NUM
FROM
EDW_ACCESS_VIEWS.ITEM INNER JOIN EDW_ACCESS_VIEWS.ITEM_DIMENSION ON (EDW_ACCESS_VIEWS.ITEM.ITEM_ID=EDW_ACCESS_VIEWS.ITEM_DIMENSION.ITEM_ID)

WHERE
( EDW_ACCESS_VIEWS.ITEM.ITEM_TYP_CD='FULL' )
AND ( EDW_ACCESS_VIEWS.ITEM.ITEM_END_DT Is Null )
AND (EDW_ACCESS_VIEWS.ITEM.ITEM_TYP_CD = 'FULL' and EDW_ACCESS_VIEWS.ITEM.ITEM_STATUS_TYP_CD <> 5)
AND
EDW_ACCESS_VIEWS.ITEM_DIMENSION.Partner_Name IN ('BAKER & TAYLOR ', 'BAKER & TAYLOR INC ', 'D AND H DISTRIBUTING ', 'DSI SYSTEMS ', 'INGRAM BOOK COMPANY ', 'INGRAM ENTERTAINMENT INC ', 'INGRAM MICRO ', 'PETRA INDUSTRIES ', 'ZAPPOSCOM INC ')
)
Group By 1

Union All

Select buyer, sum(crskus)
from
(SELECT * FROM
(
SELECT ALT_BUYER_NAME BUYER, COUNT(*) CRSKUS
FROM (
SELECT IC.ITEM_ID, ITEM_CREATION_DT, ALT_BUYER_NAME,
CASE WHEN ITD.PARTNER_IND = 'Y' THEN 0 ELSE 1 END PC
FROM EDW_ACCESS_VIEWS.ITEM_CREATIVE IC
JOIN ITEM_DIMENSION ITD ON IC.item_ID = ITD.item_ID
WHERE item_creation_dt BETWEEN (current_date)-7 AND current_date
And ITD.Partner_Name IN ('BAKER & TAYLOR ', 'BAKER & TAYLOR INC ', 'D AND H DISTRIBUTING ', 'DSI SYSTEMS ', 'INGRAM BOOK COMPANY ', 'INGRAM ENTERTAINMENT INC ', 'INGRAM MICRO ', 'PETRA INDUSTRIES ', 'ZAPPOSCOM INC ')
GROUP BY 1,2,3,4
) Creative
GROUP BY 1
)StartedCreative

UNION

SELECT * FROM
(
SELECT ALT_BUYER_NAME BUYER, 0 SKUS
FROM EDW_ACCESS_VIEWS.ITEM_DIMENSION
GROUP BY 1,2
)BuyersList)ttSku
Group By 1
Having sum(crskus) <> 0)ttCount
Group By 1

)ttTotal

on tttemp.buyer = tttotal.totalbuyer

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-10 : 16:48:35

When I copy/paste into SSMS and check the syntax, I get all sorts of errors:

quote:

Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword 'current_date'.
Msg 102, Level 15, State 1, Line 26
Incorrect syntax near '|'.
Msg 156, Level 15, State 1, Line 51
Incorrect syntax near the keyword 'CURRENT_DATE'.
Msg 156, Level 15, State 1, Line 76
Incorrect syntax near the keyword 'CURRENT_DATE'.
Msg 156, Level 15, State 1, Line 123
Incorrect syntax near the keyword 'current_date'.
Msg 195, Level 15, State 10, Line 137
'TRIM' is not a recognized built-in function name.
Msg 156, Level 15, State 1, Line 167
Incorrect syntax near the keyword 'current_date'.
Msg 156, Level 15, State 1, Line 193
Incorrect syntax near the keyword 'Group'.
Msg 156, Level 15, State 1, Line 207
Incorrect syntax near the keyword 'current_date'.
Msg 102, Level 15, State 1, Line 221
Incorrect syntax near ')'.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 22:52:30

are you using sql server? there's no function called current_date it should be either getdate() or current_timestamp.also what does below mean?
PRO.pro_sku || '-' || PRI.pri_oa_sku

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources