Using SQL Alieses or Variables

Hi Experts,

As you'll see in my query below, on lines 26-31 I'm calculating costs for individual categories (fam_cost, ad_cost, ...)

On lines 34-39, as I know I can't use aliases, I'm basically repeating my code to get total_cost, instead of simply doing [fam_cost] + [ad_cost] +..., which would be so much easier and quicker.

Now, even though above I'm answering my own question, I'm still wondering if I could use [fam_cost] + [ad_cost] +... in order to get total_cost, because in later calculations I need to use total_cost to calculate my tax and commissions for example, so my formulas will get longer and longer unnecessarily.

As I have used SQL Variables once or twice before, and I am not sure if they would apply here, but if there any way for me to accomplish shorter formulas?

SELECT fldReservID, fldUserInit, fldUser, fldTripType, fldPickupStat, fldAssocResID,
	   fldTripSubType, fldArchived, 
	
	/*DATE Format*/
	   CONVERT(VARCHAR(10), CAST([fldBookDate] AS DATE),120) AS [Booking_Date], 
	/*TIME Format*/
	   CONVERT(VARCHAR(10), CAST([fldBookTime] AS TIME),114) AS [Booking_Time], 
	
	   fldAgent, fldBookLoc, fldPickupLoc,
	
	/*DATE Format*/
	   CONVERT(VARCHAR(10), CAST([fldPickupDate] AS DATE),120) AS [Pickup_Date], 
	/*TIME Format*/
	   CONVERT(VARCHAR(10), CAST([fldPickupTime] AS TIME),114) AS [Pickup_Time],
	   
	   fldDriver, fldVehicle, fldLName, fldRoomNo, fldReservComment,
	   
	/*QUANTITIES*/
	  	 fldFamQty,		  fldAdultQty,   fldSenQty, fldStudQty, fldChildQty, fldInfQty,
		((fldFamQty * 4) + fldAdultQty + fldSenQty + fldStudQty + fldChildQty) AS [total_pax],

	/*RATES*/
	  	 fldFamRate, fldAdultRate, fldSenRate, fldStudRate, fldChildRate, fldInfRate,
	  	 
	/*COSTS*/
		(fldFamQty		* fldFamRate)	AS [fam_cost],
		(fldAdultQty	* fldAdultRate) AS [ad_cost],
		(fldSenQty		* fldSenRate)	AS [sn_cost],
		(fldStudQty		* fldStudRate)	AS [st_cost],
		(fldChildQty	* fldChildRate) AS [ch_cost],
		(fldInfQty		* fldInfRate)	AS [inf_cost],
		
		(
			(fldFamQty		* fldFamRate)	+
			(fldAdultQty	* fldAdultRate) +
			(fldSenQty		* fldSenRate)	+
			(fldStudQty		* fldStudRate)	+
			(fldChildQty	* fldChildRate) +
			(fldInfQty		* fldInfRate)	
		
		) AS [total_cost]
		
	
FROM tblReservations 
WHERE (fldTripType IS NOT NULL) OR (fldBookTime IS NOT NULL) OR (fldBookTime IS NOT NULL) 
	  OR (fldAgent IS NOT NULL)
ORDER BY fldReservID

Open in new window


Thank you
APD TorontoSoftware DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
Try the following. Obviously, I cannot test it but I know exactly what to do so you can reuse aliases. If there is an error, make a very simple query with fewer column names and one calculation so we can work on it:
Select fldReservID, fldUserInit, fldUser, fldTripType, fldPickupStat, fldAssocResID,
	   fldTripSubType, fldArchived, Booking_Date, Booking_Time, fldAgent, fldBookLoc, fldPickupLoc
	   , Pickup_Date, Pickup_Time, fldDriver, fldVehicle, fldLName, fldRoomNo, fldReservComment
	   , fldFamQty,		  fldAdultQty,   fldSenQty, fldStudQty, fldChildQty, fldInfQty, total_pax
	   , fam_cost + ad_cost + sn_cost + st_cost + ch_cost + inf_cost As total_cost
From (SELECT fldReservID, fldUserInit, fldUser, fldTripType, fldPickupStat, fldAssocResID,
	   fldTripSubType, fldArchived, 
	
	/*DATE Format*/
	   CONVERT(VARCHAR(10), CAST([fldBookDate] AS DATE),120) AS [Booking_Date], 
	/*TIME Format*/
	   CONVERT(VARCHAR(10), CAST([fldBookTime] AS TIME),114) AS [Booking_Time], 
	
	   fldAgent, fldBookLoc, fldPickupLoc,
	
	/*DATE Format*/
	   CONVERT(VARCHAR(10), CAST([fldPickupDate] AS DATE),120) AS [Pickup_Date], 
	/*TIME Format*/
	   CONVERT(VARCHAR(10), CAST([fldPickupTime] AS TIME),114) AS [Pickup_Time],
	   
	   fldDriver, fldVehicle, fldLName, fldRoomNo, fldReservComment,
	   
	/*QUANTITIES*/
	  	 fldFamQty,		  fldAdultQty,   fldSenQty, fldStudQty, fldChildQty, fldInfQty,
		((fldFamQty * 4) + fldAdultQty + fldSenQty + fldStudQty + fldChildQty) AS [total_pax],

	/*RATES*/
	  	 fldFamRate, fldAdultRate, fldSenRate, fldStudRate, fldChildRate, fldInfRate,
	  	 
	/*COSTS*/
		(fldFamQty		* fldFamRate)	AS [fam_cost],    
		(fldAdultQty	* fldAdultRate) AS [ad_cost],
		(fldSenQty		* fldSenRate)	AS [sn_cost],
		(fldStudQty		* fldStudRate)	AS [st_cost],
		(fldChildQty	* fldChildRate) AS [ch_cost],
		(fldInfQty		* fldInfRate)	AS [inf_cost],
		
		/*(
			(fldFamQty		* fldFamRate)	+
			(fldAdultQty	* fldAdultRate) +
			(fldSenQty		* fldSenRate)	+
			(fldStudQty		* fldStudRate)	+
			(fldChildQty	* fldChildRate) +
			(fldInfQty		* fldInfRate)	
		
		) AS [total_cost])*/
		
	
FROM tblReservations 
WHERE (fldTripType IS NOT NULL) OR (fldBookTime IS NOT NULL) OR (fldBookTime IS NOT NULL) 
	  OR (fldAgent IS NOT NULL)) As myTable (fldReservID, fldUserInit, fldUser, fldTripType, fldPickupStat, fldAssocResID,
	   fldTripSubType, fldArchived, Booking_Date, Booking_Time, fldAgent, fldBookLoc, fldPickupLoc
	   , Pickup_Date, Pickup_Time, fldDriver, fldVehicle, fldLName, fldRoomNo, fldReservComment
	   , fldFamQty,		  fldAdultQty,   fldSenQty, fldStudQty, fldChildQty, fldInfQty
	   , total_pax, fldFamRate, fldAdultRate, fldSenRate, fldStudRate, fldChildRate, fldInfRate
	   , fam_cost, ad_cost, sn_cost, st_cost, ch_cost, inf_cost)
ORDER BY fldReservID

Open in new window



Mike
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperCommented:
Here is simplified version of it:

Select ReservID
     , UserInit
     , UserName 
     , fam_cost
     , ad_cost, fam_cost + ad_cost As total_cost
From (SELECT fldReservID
           , fldUserInit
		   , fldUser
		   , (fldFamQty	* fldFamRate)	AS fam_cost
		   , (fldAdultQty	* fldAdultRate) AS ad_cost
	
FROM tblReservations 
WHERE (fldTripType IS NOT NULL) OR (fldBookTime IS NOT NULL) OR (fldBookTime IS NOT NULL) 
	  OR (fldAgent IS NOT NULL)) As myTable (ReservID
	                                       , UserInit
	                                       , UserName 
	                                       , fam_cost
	                                       , ad_cost)
ORDER BY fldReservID

Open in new window


This is called external derived table. Although a typical query is written in the following order:

Select
From
Where
Order By

But the logical order of execution is:
From
Where
Select
Order By

So, alias in from could be used in select.
Alias in select could be use in Order By  (not in where)
Therefor, if in From clause we could place a select ... as derived table, aliases within can be used in the outer query.

Note: In SQL Server, aliases defined in any select clause is not available for use in the same select clause. This is called all-at-once ...

However, in MS Access, I have tested it, aliases within the same select is available for use again.

Here is a simple example: http://logicalread.solarwinds.com/when-to-apply-sql-server-derived-tables-mc03/#.VUQwUCFVhBc

Mike
0
APD TorontoSoftware DeveloperAuthor Commented:
What about using SQL Variables?
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Mike EghtebasDatabase and Application DeveloperCommented:
APD_Toronto,

Why you want use variables if it could be done via a single query? The query type that is designed particularly for this purpose.

With variables, for re-usability,  you have to put all in a stored procedure more work and effort.

Are you looking for a solution or are interested in a solution using variables?  

Mike
0
APD TorontoSoftware DeveloperAuthor Commented:
Mike,

Your second example gives me access to ad_cost and fam_cost, so that I can use in the sub-query to get total_cost, OK.  Later, as per my original post, I need my total_cost to get my tax_cost and commission_cost, then a sub-sub-sub-query to get my net.  This can get out of hand.

As in programming, I thought you could simplify this using variables, but maybe SQL Variables do not work the same way?
0
APD TorontoSoftware DeveloperAuthor Commented:
I'm not sure how to accomblish this, but for example:

@fam_cost = fldFamQty * fldFamRate
@ad_cost = fldAdQty * fldAdRate

@tot_cost = @fam_cost + @ad_cost

SELECT fldReservID, @tot_cost AS [tot_cost], @fam_cost AS [fam_cost],  @ad_cost AS [tot_cost]

FROM ...
WHERE ...
ORDER BY ...

Open in new window

0
Mike EghtebasDatabase and Application DeveloperCommented:
In the derived table you get sub totals using:

/*COSTS*/
            (fldFamQty            * fldFamRate)      AS [fam_cost],    
            (fldAdultQty      * fldAdultRate) AS [ad_cost],
            (fldSenQty            * fldSenRate)      AS [sn_cost],
            (fldStudQty            * fldStudRate)      AS [st_cost],
            (fldChildQty      * fldChildRate) AS [ch_cost],
            (fldInfQty            * fldInfRate)      AS [inf_cost],

In the outer query, you sum them up to get total using:

fam_cost + ad_cost + sn_cost + st_cost + ch_cost + inf_cost As total_cost

So far all is under control, right?

We have two choices here to do the rest of the calculations (to perform your other tax calculations) in the outer query. Instead of using  total_cost use (fam_cost + ad_cost + sn_cost + st_cost + ch_cost + inf_cost ) for these calculations for now.

This is not as ideal but better than repeating calculation you had originally. After you do this, we can further optimize the process using multiple CTEs where we just use total_cost in the outer query not its extended version of it (fam_cost + ad_cost + sn_cost + st_cost + ch_cost + inf_cost ).

Mike
0
Mike EghtebasDatabase and Application DeveloperCommented:
Hi APD_Toronto,

You probably were looking for a solution and I was trying just to learn. What you have accepted could be improved doing CTEs or instead derived table and also using stored procedure. If you have the time and are interested new ways of doing it we can work together to lean something new. I am preparing for query certification and would like to do this if you want to play and learn.

Mike
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.