Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

T-SQL Union

Posted on 2009-12-21
10
Medium Priority
?
445 Views
Last Modified: 2012-05-08
The query Below has no Syntax error but it is not returning what I need

I am having a problem with the SalesPrice Column

This Statement returns a column with 0's pre filled: OK thats fine thats what I need since this column does not exist in the table
SELECT Territory, CustomerNo, ProductLine, ItemCode, p.List, CAST( 0 AS INT) AS SalesPrice FROM dbo.Tmp_Actual


This statement returns value 2010. This is not what I want
SELECT  Territory, CustomerNo, ProductLine, ItemCode, List, 2010_Sales_Price FROM dbo.Tmp_Forecast F

in this table there are values I want to return back from the table..

I created a dummy column on the first query because its a union and Columns must be equal. But I must be doing something wrong if its not letting me return the values that are in Forecast...

How can I combine both and have SalesPrice Return is true value instead of 2010

thanks!



;with P as 
(
SELECT ItemNumber, List, Contract, DateCode,
   ROW_NUMBER() OVER( PARTITION BY ItemNumber ORDER BY DateCode DESC ) rn
   FROM GSDatabase.dbo.Pricing
)
SELECT Combined.Territory, Combined.CustomerNo, Combined.ProductLine, Combined.ItemCode, Combined.List,Combined.SalesPrice,
       t1."Jan Units" as Actual_JAN, t2."JAN UNITS" as Forecast_JAN, t1."FEB Units" as Actual_FEB, t2."FEB UNITS" as Forecast_FEB, 
       t1."Mar Units" as Actual_MAR, t2."Mar Units" as Forecast_MAR, t1."APR UNITS" as Actual_APR, t2."APR UNITS" as Forecast_APR, 
       t2."MAY UNITS" as Actual_MAY, t2."MAY UNITS" as Forecast_MAY, t1."JUN Units" as Actual_JUN, t2."JUN UNITS" as Forecast_JUN,
       t1."Jul Units" as Actual_JUL, t2."JUL Units" as Forecast_JUL, t1."Aug Units" as Actual_AUG, t2."AUG Units" as Forecast_AUG,
       t1."Sep Units" as Actual_Sep, t2."Sep Units" as Forecast_SEP, t1."OCT Units" as Actual_OCT, t2."OCT Units" as Forecast_OCT,
       t1."NOV Units" as Actual_NOV, t2."NOV Units" as Forecast_NOV, t1."Dec Units" as Actual_Dec, t2."Dec Units" as Forecast_Dec
FROM  (SELECT Territory, CustomerNo, ProductLine, ItemCode, p.List, CAST( 0 AS INT) AS SalesPrice FROM dbo.Tmp_Actual A inner Join P on A.ItemCode = P.ItemNumber and p.rn=1 
       UNION
       SELECT Territory, CustomerNo, ProductLine, ItemCode, List, 2010_Sales_Price FROM dbo.Tmp_Forecast F inner join Product Pd on F.ItemCode = Pd.ItemNumber and pd.Quarter = 2010100 
       ) AS Combined
       Left join dbo.Tmp_Actual t1 on Combined.ItemCode = t1.ItemCode and Combined.CustomerNo = t1.CustomerNo and Combined.Territory = t1.Territory
       Left Join dbo.Tmp_Forecast t2 on Combined.ItemCode = t2.ItemCode and Combined.CustomerNo = t2.CustomerNo and Combined.Territory = t2.Territory
GROUP BY Combined.Territory, Combined.CustomerNo, Combined.ProductLine, Combined.ItemCode, Combined.List,Combined.SalesPrice,
         t1."Jan Units" ,t2."JAN UNITS", t1."FEB UNITS" ,t2."FEB UNITS", t1."Mar Units" ,t2."Mar Units", t1."APR UNITS", t2."APR UNITS", t1."MAY UNITS", t2."MAY UNITS",
         t1."JUN UNITS" ,t2."JUN UNITS", t1."Jul Units" ,t2."Jul Units", t1."Aug Units" ,t2."Aug Units", t1."Sep Units", t2."Sep Units", t1."Oct Units", t2."Oct Units",
         t1."Nov Units" ,t2."Nov Units", t1."Dec Units" ,t2."Dec Units"
Order by CustomerNo, ItemCode

Open in new window

0
Comment
Question by:Leo Torres
  • 6
  • 4
10 Comments
 
LVL 8

Author Comment

by:Leo Torres
ID: 26098671
thought this pic would help

The 2010 should be prices.. looks like its returning first 4 of the column for second query in Union..
T-SQl-Union.JPG
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 26098720
ltorres321,

Not quite sure I understand what you are saying is wrong with the above.  Just remember that UNION will eliminate duplicates, so if actuals and forecast have similar rows they will be reduced to just unique results.  

Since the other columns are the same, are you simply wanting to JOIN the actuals and forecast tables and have one column from one show with the values of the other ?

Just some initial thoughts.  For further help, please post some actual data values and what you expect on the UNION and we can try to assist from there.
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 26098734
Also, check the values in the 2010_Sales_Price column in the forecast table to ensure that the 2010 doesn't exist there.  If the query is as you have above, I don't see where else this could be coming in unless from in the data.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 8

Author Comment

by:Leo Torres
ID: 26099550
Yes the value 2010 does not exist in 2010_Sales_Price, but yes there are values in each query that may match..

So how can I get the values from the dbo.Tmp_Forecast only to display there sales price while the actual table values stay as 0's

any ideas???

Thanks for your help!!
0
 
LVL 61

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 26100042
You can try using UNION ALL instead which will treat the values separately.  If you can have duplicates within each of the queries just add back DISTINCT keyword there so you will get unique from each, but duplicates when appearing in both.

SELECT DISTINCT Territory, CustomerNo, ProductLine, ItemCode, p.List, CAST( 0 AS INT) AS SalesPrice FROM dbo.Tmp_Actual A inner Join P on A.ItemCode = P.ItemNumber and p.rn=1
       UNION ALL
SELECT DISTINCT Territory, CustomerNo, ProductLine, ItemCode, List, 2010_Sales_Price FROM dbo.Tmp_Forecast F inner join Product Pd on F.ItemCode = Pd.ItemNumber and pd.Quarter = 2010100
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 26105248
Wow, this one was weird!!

Kev, your solution did not work either sames result

I changed the Column name to Sales_Price_2010 and now it works for some reason it was returning the prices??

0
 
LVL 8

Author Closing Comment

by:Leo Torres
ID: 31668645
Thanks for the help N E ways.. You helped me in other ones so you deserve the points N E ways..
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 26105281
Happy Holidays..

Any clue to the bizarre behavior??
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 26105667
Couple things.
When column names start with numbers, they must be qualified; therefore, you would have to use : [2010_Sales_Price] or "2010_Sales_Price" or something else to that effect.
Possibly, instead of giving an error the 2010 was being used as a literal value with a column name of _Sales_Price (i.e., a space was there we didn't know about).  Changing the column name on the underlying table if I am understanding the solution correctly, removes both of these issues as you no longer start with a number and it would be noticeable on the space issue the other way around because Sales_Price_ would be invalid as a literal and the column alias of 2010 would be invalid without qualifiers.
Hope that helps.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 26105798
Great insight thank you..

Happy Holidays!!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question