I need help in this - 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

SQL Server 2000 Forums

SQL Server Development (2000)

I need help in this

Author

Topic

salukurian
Starting Member

4 Posts

Posted - 2005-12-06 : 17:12:58

I have a UNION ALL statement in a Query

Now i need to display records even when the other table dont have corresponding records

Same like LEFT JOIN

I need help

Can mail me on salukurian@gmail.com

SELECT
SPL.SPL_ID as [PlanningID],
SPL.SPL_Name AS [PlanningGroup],
IPG.IPG_Name AS [PromotedGroup],
PBS.PBS_ID as PBSID,
PBS.PBS_FSY_FiscalYearID as [Year],
PBS.PBS_FSQ_FiscalQuarterID as [Quarter],
1 as sort_order ,
'CY Plan' as [Flag]

FROM PBS_PLAN_BASE PBS INNER JOIN
PTS_PLAN_TACTIC_SCENARIO PTS ON PTS.PTS_PBS_ID = PBS.PBS_ID INNER JOIN
IPG_ITEM_PROMOTED_GROUP IPG ON PBS.PBS_IPG_ID=IPG.IPG_ID FULL JOIN
SPL_SALES_PLANNING_GROUP SPL ON PBS.PBS_SPL_ID = SPL.SPL_ID --INNER JOIN
-- PTC_PLAN_TACTIC PTC ON PTC.PTC_PBS_ID=PBS.PBS_ID
WHERE (SPL.SPL_ID=110788) AND (PBS.PBS_FSY_FiscalYearID =2006) AND (PBS.PBS_FSQ_FiscalQuarterID =3) AND (PTS.PTS_Selected = 1)
--GROUP BY IPG.IPG_Name,IPG.IPG_ID, SPL.SPL_ID,PTC.PTC_ID,PBS.PBS_ID,PBS.PBS_FSY_FiscalYearID, PBS.PBS_FSQ_FiscalQuarterID
GROUP BY SPL.SPL_ID,SPL.SPL_Name,IPG.IPG_Name,PBS.PBS_ID,PBS.PBS_Price,PBS.PBS_FSY_FiscalYearID, PBS.PBS_FSQ_FiscalQuarterID

UNION ALL

SELECT

SHQ.SHQ_SPL_ID as [PlanningID],
SPL.SPL_Name AS [PlanningGroup],
IPG.IPG_Name AS [PromotedGroup],
null as PBSID,
SHQ_FSY_FiscalYearID as [Year],
SHQ_FSQ_FiscalQuarterID as [Quarter],
2 as sort_order ,
'LY Actuals' As Flag

FROM
SHQ_SALES_HISTORY_QUARTERLY SHQ INNER JOIN
IPG_ITEM_PROMOTED_GROUP IPG ON SHQ.SHQ_IPG_ID = IPG.IPG_ID INNER JOIN
SPL_SALES_PLANNING_GROUP SPL ON SPL.SPL_ID=SHQ.SHQ_SPL_ID
WHERE SHQ_SPL_ID=110788 AND SHQ_FSY_FiscalYearID=2005 AND SHQ_FSQ_FiscalQuarterID=3
GROUP BY SHQ.SHQ_SPL_ID,SPL.SPL_Name,IPG.IPG_Name,SHQ_FSY_FiscalYearID,SHQ_FSQ_FiscalQuarterID

ORDER BY PlanningGroup,PromotedGroup, sort_order

SamC
White Water Yakist

3467 Posts

Posted - 2005-12-06 : 17:36:42

quote:
Originally posted by salukurian

I have a UNION ALL statement in a Query

Now i need to display records even when the other table dont have corresponding records

Same like LEFT JOIN

I don't understand the problem.

Sounds like you're under the impression that if UNION ALL connects an empty recordset that the entire recordset is empty? That's incorrect.

Why don't you post your query and we'll take a look.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-07 : 01:35:26

Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

salukurian
Starting Member

4 Posts

Posted - 2005-12-07 : 08:51:34

quote:
Originally posted by madhivanan

Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

I need that from the second part in the union returns some records which are not there in the first part and now i need is that even when there is no data in my first part i still need some dummy value so that i have it matching since i have the sort order which will take care of the dummy data .

Since i cant bring in all the sample data for all the tables .
I just need to know what approach should go with to achieve this

salukurian
Starting Member

4 Posts

Posted - 2005-12-13 : 09:05:22

hello I just did not get any reply

Can anyone help me in this query below.

Regards
Salu Kurian

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-13 : 09:15:00

Post some sample data (at least 10) from two queries and the result you want
Where do you want to show these data?

Madhivanan

Failing to plan is Planning to fail

salukurian
Starting Member

4 Posts

Posted - 2005-12-13 : 09:31:42

Result from Query 1 :------------------------------------------

AQUA FOODS 127535 DELI DELI RT SL BONELESS HAM 21295 2006 3 1 CY Plan 6.3900 1027.00000 3.4469 .00000 .0000000000 .000000 6.3900 46.00938 -.08993588441788
AQUA FOODS 127535 DELI DELI RT SL BEEF 21292 2006 3 1 CY Plan 7.6900 624.00000 4.1624 .00000 .0000000000 .000000 7.6900 45.90377 .05765904285989
AQUA FOODS 127535 DELI DELI RT SL FULLY CKED PLTRY 2006 3 1 CY Plan 6.3900 1573.00000 3.4366 .00000 .0000000000 .000000 6.3900 46.16588 -.09893499388931
AQUA FOODS 127535 SMKGRL HD BRYAN BEEF-L 27547 2006 3 1 CY Plan 4.0000 32500.00000 2.5000 .00000 .0000000000 .000000 4.0000 21.68674 -2.85680000000000
Query No 1:------------------------------------------------------

SELECT
SPL.SPL_Name AS [PlanningGroup],
SPG.SPG_Name AS [CategoryGroup],
IPG.IPG_Name AS [PromotedGroup],
PBS.PBS_ID as PBSID,
PBS.PBS_FSY_FiscalYearID as [Year],
PBS.PBS_FSQ_FiscalQuarterID as [Quarter],
1 as sort_order,
'CY Plan' as [Flag],
PBS.PBS_Price AS [BasePrice],
SUM(PTS.PTS_TotalVolume) AS [Volume lbs],
SUM(PBS.PBS_ListPrice) AS [List Price],
SUM(PTS.PTS_TradeRate) AS [Trade Rate],
SUM(PTS.PTS_TradeRate * PBS.PBS_PriceFactor) AS [Trade Rate / Unit],
SUM((PTS.PTS_TotalVolume * PTS.PTS_TradeRate) /( CASE WHEN (PBS.PBS_ListPrice * PBS.PBS_PriceFactor * PTS.PTS_TotalVolume) <> 0 THEN (PBS.PBS_ListPrice * PBS.PBS_PriceFactor * PTS.PTS_TotalVolume) ELSE 1 END)) AS [Percentage Of Revenue],
SUM(PBS.PBS_Price) AS [Retail Everday Shelf],
SUM(PTS.PTS_GPPercent) AS [Retail Profit],
SUM((PBS.PBS_ListPrice - PTS.PTS_ListPrice) /(CASE WHEN (PBS.PBS_ListPrice=0) THEN 1 ELSE PBS.PBS_ListPrice END) * 100) AS [Discount %]
FROM PBS_PLAN_BASE PBS INNER JOIN
PTS_PLAN_TACTIC_SCENARIO PTS ON PTS.PTS_PBS_ID = PBS.PBS_ID INNER JOIN
IPG_ITEM_PROMOTED_GROUP IPG ON PBS.PBS_IPG_ID = IPG.IPG_ID INNER JOIN
SPL_SALES_PLANNING_GROUP SPL ON PBS.PBS_SPL_ID = SPL.SPL_ID INNER JOIN
SPG_SALES_PREFERENCE_GROUP SPG ON SPG.SPG_ID=PBS.PBS_SPG_ID
WHERE SPL.SPL_ID =127535 AND (PBS.PBS_FSY_FiscalYearID =2006) AND (PBS.PBS_FSQ_FiscalQuarterID =3) AND (PTS.PTS_Selected = 1)
--GROUP BY IPG.IPG_Name,IPG.IPG_ID, SPL.SPL_ID,PTC.PTC_ID,PBS.PBS_ID,PBS.PBS_FSY_FiscalYearID, PBS.PBS_FSQ_FiscalQuarterID
GROUP BY SPL.SPL_Name,SPG.SPG_Name,IPG.IPG_Name,PBS.PBS_ID,PBS.PBS_Price,PBS.PBS_FSY_FiscalYearID, PBS.PBS_FSQ_FiscalQuarterID

Result from Query 2 :------------------------------------------

AQUA FOODS 127535 DELI RT SL BEEF NULL 2005 3 2 LY Actuals 0 92.58000 NULL .00000 .00000000000000000000 .000000 0 0 0
AQUA FOODS 127535 DELI RT SL BONELESS HAM NULL 2005 3 2 LY Actuals 0 483.02000 NULL .00000 .00000000000000000000 .000000 0 0 0
AQUA FOODS 127535 DELI RT SL FLY CKD PLTRY CHX-I NULL 2005 3 2 LY Actuals 0 42.29000 NULL .00000 .00000000000000000000 .000000 0 0 0
AQUA FOODS 127535 DELI RT SL FULLY CKED PLTRY NULL 2005 3 2 LY Actuals 0 768.34000 NULL .00000 .00000000000000000000 .000000 0 0 0
AQUA FOODS 127535 DELI RT SL PEPPERED BEEF NULL 2005 3 2 LY Actuals 0 .00000 NULL .00000 .00000000000000000000 .000000 0 0 0

Query No 2:------------------------------------------------------
SELECT
SPL.SPL_Name AS [PlanningGroup],
'' AS [CategoryGroup],
IPG.IPG_Name AS [PromotedGroup],
null as PBSID,
SHQ_FSY_FiscalYearID as [Year],
SHQ_FSQ_FiscalQuarterID as [Quarter],
2 as sort_order,
'LY Actuals' As Flag,
SUM(0) AS [BasePrice],
SUM(SHQ_Volume) AS [Volume lbs],
NULL AS [List Price],
SUM(SHQ_Trade) AS [Trade Rate],
SUM(CASE WHEN SHQ_Volume <> 0 THEN SHQ_Trade / SHQ_Volume ELSE 0 END) AS [Trade Rate / Unit],
SUM(CASE WHEN SHQ_Volume <> 0 AND SHQ_GrossSales <> 0 THEN SHQ_Volume * (SHQ_Trade / SHQ_Volume) / SHQ_GrossSales ELSE 0 END) AS [Percentage Of Revenue],
SUM(0) AS [Retail Everday Shelf],
SUM(0) AS [Retail Profit],
SUM(0) AS [Discount %]
FROM
SHQ_SALES_HISTORY_QUARTERLY SHQ INNER JOIN
IPG_ITEM_PROMOTED_GROUP IPG ON SHQ.SHQ_IPG_ID = IPG.IPG_ID INNER JOIN
SPL_SALES_PLANNING_GROUP SPL ON SPL.SPL_ID=SHQ.SHQ_SPL_ID
WHERE SHQ_FSY_FiscalYearID=2005 AND SHQ_FSQ_FiscalQuarterID=3 AND ( SHQ_SPL_ID =127535)

GROUP BY SPL.SPL_Name,IPG.IPG_Name,SHQ_FSY_FiscalYearID,SHQ_FSQ_FiscalQuarterID

When u order using "ORDER BY SPL.SPL_Name,IPG.IPG_Name, sort_order "

According to sort_order we get 1 sort order below 2 sort order accoring to the SPL_Name and IPG_Name.

I need to get the result if there is not "CY Plan" or 1 sort order for "LY Plan" or 2 sort order i need some dummy data so that there will be a 1 sort order will dummy data for all the 2 sort order with no corresponding data .

Hope this is what i can explain

If you still have some questions do let me know

Regards
Salu

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-14 : 00:06:18

If you use Front End Application, cant you do adding dummy data?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources