Very SIMPLE query working in Access not working on Sql Server

Question

Post reply

Very SIMPLE query working in Access not working on Sql Server

dquirion78

Ten Centuries

Points: 1240
More actions
January 17, 2014 at 9:25 am

#288437

Hi eveyone
I am in the progress to convert mdb to sql but I've so much to do.
How use an expression/alias on other column ?
SELECT PrdOrder.Qte AS Q, PrdOrder.Prix AS P,
*[P] AS T
FROM PrdOrder;
invalid column name
thanks to answer the newbish sql question ever

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

dquirion78 Ten Centuries Points: 1240 More actions · Answer 1

I'm looking for an hour and after posted my question I finally found my answer...

select q*p as t , p,q from(

SELECT PrdOrder.Qte AS Q, PrdOrder.Prix AS P

FROM PrdOrder) as PO;

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

You could also use the column names in the original select instead of the alias.

SELECT po.Qte AS Q,

po.Prix AS P,

po.Qte *po.Prix AS T

FROM PrdOrder po;

SQL Server won't recognize column alias to reference columns in the column list from the select. (Did I made myself clear? or did it became even more confusing?)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

dquirion78 Ten Centuries Points: 1240 More actions · Answer 3

Yeah I know but my fields name are pretty long so it become hard to read in my query because I've around 15 fields to retrieve with many calculated long... expression

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Correct formatting and short table aliases should make the queries easier to read.

It's not the same to read:

SELECT [MyTable].[Long Column Name] AS A, [MyTable].[AnotherColumnName] AS B, [MyTable].[Long Column Name] *

[MyTable].[AnotherColumnName] AS Result FROM [MyTable]

As it is to read:

SELECT t.[Long Column Name] AS A,

t.[AnotherColumnName] AS B,

t.[Long Column Name] * t.[AnotherColumnName] AS Result

FROM [MyTable] t

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Luis Cazares (1/17/2014)
Correct formatting and short table aliases should make the queries easier to read.
It's not the same to read:
SELECT [MyTable].[Long Column Name] AS A, [MyTable].[AnotherColumnName] AS B, [MyTable].[Long Column Name] *
[MyTable].[AnotherColumnName] AS Result FROM [MyTable]
As it is to read:
SELECT t.[Long Column Name] AS A,
t.[AnotherColumnName] AS B,
t.[Long Column Name] * t.[AnotherColumnName] AS Result
FROM [MyTable] t

+1000

I would add to this that by putting each column of your query on a new line it makes maintenance a LOT easier too. If it so much easier to find a given in a query with a bunch of columns when they are separated out like that.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

And if you don't want to do all the formatting work yourself, you can use something like http://poorsql.com to give you a readable code.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

dquirion78 Ten Centuries Points: 1240 More actions · Answer 7

thanks everyone for input

Last question:

I've a query(french query) WORKING IN Access but NOT in Sql Server 2012 express...

SELECT PrdCmd.NoCommande AS NoCmd

,PrdCmd.DATE

,PrdCmd.Qte AS q

,PrdCmd.Prix AS p

,[p] *

AS Column1

,[column1] * 1000 AS Column2

,[column1] * 3 / 4 AS Column3

,[column1] * 0.01 AS Column4

FROM ProduitCommande AS PrdCmd;

here my best result so far in sql , any better query ??:

SELECT *

,column1 * 1000 AS column2

,column1 * 3 / 4 AS column3

,column1 * 0.01 AS column4

FROM (

SELECT PrdCmd.NoCommande AS NoCmd

,PrdCmd.DATE

,PrdCmd.Qte AS q

,PrdCmd.Prix AS p

,prdcmd.qte * prdcmd.prix AS column1

FROM ProduitCommande AS PrdCmd

) AS PrdCmdtotall

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

dquirion78 (1/17/2014)
thanks everyone for input
Last question:
I've a query(french query) WORKING IN Access but NOT in Sql Server 2012 express...
SELECT PrdCmd.NoCommande AS NoCmd
,PrdCmd.DATE
,PrdCmd.Qte AS q
,PrdCmd.Prix AS p
,[p] *
AS Column1
,[column1] * 1000 AS Column2
,[column1] * 3 / 4 AS Column3
,[column1] * 0.01 AS Column4
FROM ProduitCommande AS PrdCmd;
here my best result so far in sql , any better query ??:
SELECT *
,column1 * 1000 AS column2
,column1 * 3 / 4 AS column3
,column1 * 0.01 AS column4
FROM (
SELECT PrdCmd.NoCommande AS NoCmd
,PrdCmd.DATE
,PrdCmd.Qte AS q
,PrdCmd.Prix AS p
,prdcmd.qte * prdcmd.prix AS column1
FROM ProduitCommande AS PrdCmd
) AS PrdCmdtotall

This is exactly the same thing as your original. You can't reference derived columns by their alias.

Keep it simple.

SELECT PrdCmd.NoCommande AS NoCmd

,PrdCmd.DATE

,PrdCmd.Qte AS q

,PrdCmd.Prix AS p

,PrdCmd.Prix * PrdCmd.Qte AS Column1

,(PrdCmd.Prix * PrdCmd.Qte) * 1000 AS Column2

,(PrdCmd.Prix * PrdCmd.Qte) * 3 / 4 AS Column3

,(PrdCmd.Prix * PrdCmd.Qte) * 0.01 AS Column4

FROM ProduitCommande AS PrdCmd;

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

dquirion78 Ten Centuries Points: 1240 More actions · Answer 9

Yean but I want to avoid to repeat PrdCmd.Prix * PrdCmd.Qte so many times. This expression is simple but if it become pretty long and I need to change base(prix*qte) expression later I can forget to change a column.

example :

(PrdCmd.Prix * PrdCmd.Qte *100) * 1000 AS Column2

,(PrdCmd.Prix * PrdCmd.Qte *100) * 3 / 4 AS Column3

,(PrdCmd.Prix * PrdCmd.Qte) * 0.01 AS Column4 ' I forgot to change this one but I the query became so complex that I didn't see this one.

But If I've a common expression I can't forget it.

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

dquirion78 (1/17/2014)
Yean but I want to avoid to repeat PrdCmd.Prix * PrdCmd.Qte so many times. This expression is simple but if it become pretty long and I need to change base(prix*qte) expression later I can forget to change a column.
example :
(PrdCmd.Prix * PrdCmd.Qte *100) * 1000 AS Column2
,(PrdCmd.Prix * PrdCmd.Qte *100) * 3 / 4 AS Column3
,(PrdCmd.Prix * PrdCmd.Qte) * 0.01 AS Column4 ' I forgot to change this one but I the query became so complex that I didn't see this one.
But If I've a common expression I can't forget it.

If you need to reuse this calculation repeatedly maybe you should look into computed columns. http://technet.microsoft.com/en-us/library/ms191250.aspx

Also, I would not use select * anywhere in production code.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

dquirion78 Ten Centuries Points: 1240 More actions · Answer 11

wow thanks I just tried some test on computed columns... Sql server is so strong 🙂

thanks again

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 12

dquirion78 (1/17/2014)
wow thanks I just tried some test on computed columns... Sql server is so strong 🙂
thanks again

If you can't add PERSISTED Computed Columns to your table for some reason, one reasonable way to condense code is to use CROSS APPLY like the following...

SELECT PrdCmd.NoCommande AS NoCmd

,PrdCmd.DATE

,PrdCmd.Qte AS q

,PrdCmd.Prix AS p

,ca.PrixQteProduct AS Column1

,ca.PrixQteProduct * 1000 AS Column2

,ca.PrixQteProduct * 3 / 4 AS Column3

,ca.PrixQteProduct * 0.01 AS Column4

FROM dbo.ProduitCommande AS PrdCmd

CROSS APPLY (SELECT PrdCmd.Prix * PrdCmd.Qte) ca (PrixQteProduct)

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dquirion78 Ten Centuries Points: 1240 More actions · Answer 13

thanks exactly what I wanted but now I've 2 good solutions, computed and cross apply solutions

you are the best(all) !