Problem in Query
-
Hello all, I am trying to execute following query but getting an error as amount us not a valid column:
select itemname,rate,qty,discount,(rate * qty) as amount,(amount -(amount * (disount/100))) as Total from ItemMaster;
Then i try:
select itemname,rate,qty,discount,(rate * qty) as amount,((rate * qty) - ((rate * qty) * (disount/100))) as Total from ItemMaster;
and it worked fine. I need to execute queries which involves more complex expressions within them. My doubt is, is there any way to run query by first method so that we don't need to write complex expressions again and again? Thanks, Nagendra.
-
Hello all, I am trying to execute following query but getting an error as amount us not a valid column:
select itemname,rate,qty,discount,(rate * qty) as amount,(amount -(amount * (disount/100))) as Total from ItemMaster;
Then i try:
select itemname,rate,qty,discount,(rate * qty) as amount,((rate * qty) - ((rate * qty) * (disount/100))) as Total from ItemMaster;
and it worked fine. I need to execute queries which involves more complex expressions within them. My doubt is, is there any way to run query by first method so that we don't need to write complex expressions again and again? Thanks, Nagendra.
-
Hello all, I am trying to execute following query but getting an error as amount us not a valid column:
select itemname,rate,qty,discount,(rate * qty) as amount,(amount -(amount * (disount/100))) as Total from ItemMaster;
Then i try:
select itemname,rate,qty,discount,(rate * qty) as amount,((rate * qty) - ((rate * qty) * (disount/100))) as Total from ItemMaster;
and it worked fine. I need to execute queries which involves more complex expressions within them. My doubt is, is there any way to run query by first method so that we don't need to write complex expressions again and again? Thanks, Nagendra.
There are a number of methods to achieve a semblance of this, UDF is one, in the above simplistic sample on you could create a view that calcs the amount column, you could create a table variable to hold the equivalent of the view, this would only be valid for the current proc whereas a view persists to the database. I am in the habit of creating a view for my transaction tables, creating any calculated columns (amount) and joining to the foreign key tables (an example would be an OrderLine to Product table on the ProductID to get the product name in the view).
-
There are a number of methods to achieve a semblance of this, UDF is one, in the above simplistic sample on you could create a view that calcs the amount column, you could create a table variable to hold the equivalent of the view, this would only be valid for the current proc whereas a view persists to the database. I am in the habit of creating a view for my transaction tables, creating any calculated columns (amount) and joining to the foreign key tables (an example would be an OrderLine to Product table on the ProductID to get the product name in the view).
-
There are a number of methods to achieve a semblance of this, UDF is one, in the above simplistic sample on you could create a view that calcs the amount column, you could create a table variable to hold the equivalent of the view, this would only be valid for the current proc whereas a view persists to the database. I am in the habit of creating a view for my transaction tables, creating any calculated columns (amount) and joining to the foreign key tables (an example would be an OrderLine to Product table on the ProductID to get the product name in the view).