Syntax Problems 70-461 level - 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

Syntax Problems 70-461 level

Author

Topic

hellfire45
Starting Member

10 Posts

Posted - 2015-02-12 : 21:44:34

Hey guys. I'm very new to SQL. I bought a Groupon training program for $50 which includes training for 70-461 through 70-465. Anyway syntax problems below. Thanks!

In this first one, I'm trying to use my first created column "taxes" to calculate the second column "totalcost". However, it says the following: "Cannot convert a char value to money. The char value has incorrect syntax."

SELECT
	ProductType, price, 'taxes' = 
	CASE	
		WHEN price <$10.00 then price*.05
		WHEN price >= $10.00 then Price*.10
		Else price*.05
	END,
'TotalCost' =
	CASE
		WHEN price <$10.00 then price+'taxes'
		WHEN price >= $10.00 then price+'taxes'
		Else price+'taxes'
	END
	From 
		AnimeGoods

In this second one, it's saying "Invalid column name 'totalprice'."
I think I'm having problems using calculated columns to create other calculated columns. I'm sorry I'm very new but thank you very much!

SELECT
	Producttype, price, 'discount' = 
	CASE
		WHEN price >$15.00 then price*.20
		else 0
	END,
'TotalPrice' =
	CASE	
		WHEN price >$15.00 then price-(price*.20)
		else price
	end,
'PercentofOriginal' = 
	CASE
		WHEN price>totalprice then totalprice/price
		else 1005
	END
FROM
	Animegoods

waterduck
Aged Yak Warrior

982 Posts

Posted - 2015-02-12 : 22:48:25

word bolded is a string but data inside column price(underlined) is money data type. Microsoft not able to convert a string to money cause it assume that you wish to add these 2 value together instead of combining the 2 column into a string. So what you need to do is to convert the money column into a string.

SELECT
ProductType, price, 'taxes' =
CASE
WHEN price <$10.00 then price*.05
WHEN price >= $10.00 then Price*.10
Else price*.05
END,
'TotalCost' =
CASE
WHEN price <$10.00 then price+'taxes'
WHEN price >= $10.00 then price+'taxes'
Else price+'taxes'
END
From
AnimeGoods

hellfire45
Starting Member

10 Posts

Posted - 2015-02-13 : 08:08:42

Thank you for the information.

My intent is to add money and money. So I think I need to convert the string column (taxes) into a they money format.

How might I accomplish this?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-13 : 08:45:20

I think you may be missing one thing:

Within a SELECT, all the columns are in theory processed at the same time. In practice, of course, they may (depending on parallelism) be processed at different times. Because of this, none of the column aliases are available before the ORDER BY clause, if there is one. So you cannot write:


select a = 1, b = a + 1

However, you *can* do something similar using CROSS APPLY. e.g.


select a, b.b
from (values(1)) v(a)
cross apply (select a + 1) b(b)

Here, you use cross apply to compute sub-expressions. So your query might look like this:


SELECT ProductType
     , price
     , tax.es.total.cost
  FROM AnimeGoods
       CROSS APPLY(
    SELECT CASE
               WHEN price < $10.00
                   THEN price * .05
               WHEN price >= $10.00
                   THEN Price * .10
               ELSE price * .05	        -- unreachable
           END)tax(es)
                  CROSS APPLY(
    SELECT CASE
               WHEN price < $10.00
                   THEN price + tax.es
               WHEN price >= $10.00
                   THEN price + tax.es
               ELSE price + tax.es	-- unreachable
           END)total(cost);

Also, notice that the ELSE clause of the case statements is unreachable and can be removed.

hellfire45
Starting Member

10 Posts

Posted - 2015-02-14 : 21:26:50

Thanks! I really appreciate you taking the time to write all that.

I don't understand the concept of Cross Apply so I'm going to need to research it.

I'm like a 2 out of 100 on SQL knowledge right now.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-15 : 14:25:28

CROSS APPLY is like an INNER JOIN except that you don't have to specify an ON clause. The expressions in the CROSS APPLY are executed once for each row in the current row set.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources