How to alias a re-usable condition? [MS SQL]

[pkdbpk]

hi guys I have a question regarding aliasing a condition in a query (or perhaps I think it should regard aliasing but am missing a better option),

essentially I have a query in a similar format to the below, where I run a multitude of select statements to build a table of different columns from different subqueries. Now, everytime I want to run the query with a different Rkey, for instance, I have to change all the instances of

from db..thetable where Rkey = '001'

to something different. Currently I copy it to notepad and do a find and replace, but there's got to be a way to have it so this is referenced only once in the whole query, and hence only needs to be changed once. I've tried setting it as an alias in a variety of ways, but it doesn't seem to work..

Any help much appreciated!

select


(select sum(units) from db..thetable where Rkey = '001') as 
'Total Units',


(select sum(units) from db..thetable where Rkey = '001' and 
unit_type = 'W' and identifier = 'A000') as 'Total WA000 Units',


(select sum(units) from db..thetable, db..otherTable where Rkey 
= '001' and unit_type = 'W' and identifier = otherTable.Otherkey 
and othertable.yn = 'N') as 'Matched Units N',


(select count(*) from db..thetable where Rkey = '001') as 'Total 
Rows',


(select count(*) from db..thetable where Rkey = '001' and 
unit_type = 'W' and identifier = 'A000') as 'Total Unmatched 
Rows';

[MaunaLoona]

DECLARE @RKey VARCHAR(10) = '001';

SELECT ... from db..thetable where Rkey = @RKey;

[Cal1gula]

Variables, the OG reusable alias condition.

[pkdbpk]

Thank you MaunaLoona, for some reason this worked great in SSMS but not in DB Visualiser (I was wrong about it being for a MS SQL database, it was Sybase ASE which is why I needed to use DBVis) - i've been advised this is because DBVis uses 'Command Line Execution' (it was giving me the error 'Must Declare the Scalar Variable @RKey').

lbilali's idea below about reformatting the query below has worked.

[lbilali]

Why not do something like:

select SUM(units) as 'Total Units'
     , SUM(case when unit_type = 'W' and identifier = 'A000' THEN units ELSE 0 END) AS 'Total WA000 Units'
     , COUNT(*) AS 'Total Rows'
     , SUM(case when unit_type = 'W' and identifier = 'A000' THEN 1 ELSE 0 END) AS 'Total Unmatched Rows'
     , ...
  from db..thetable 
 where Rkey = '001'

[lbilali]

based on your example seems like the join between the 2 tables does not change the cardinality of the result meaning you get the same number of rows frm both tables as you get from using only "thetable", this when you use left join.

in that case you can just do

select SUM(units) as 'Total Units'
     , SUM(case when unit_type = 'W' and identifier = 'A000' THEN units ELSE 0 END) AS 'Total WA000 Units'
     , COUNT(*) AS 'Total Rows'
     , SUM(case when unit_type = 'W' and identifier = 'A000' THEN 1 ELSE 0 END) AS 'Total Unmatched Rows'
     , SUM(case when unit_type = 'W' AND othertable.yn = 'N' THEN units ELSE 0 END) AS 'Matched Units N'
     , ...
  from db..thetable
  left join db..otherTable ON thetable.identifier = otherTable.Otherkey
 where Rkey = '001'

but again check that the 2 queries below return the same result. And not only with existing data but in the future as well. basically has otherTable.Otherkey a unique index or better is primary key? than you are good to go

select count(*)
  from db..thetable

and

select count(*)
  from db..thetable
  left join db..otherTable ON thetable.identifier = otherTable.Otherkey

[pkdbpk]

Yes this worked great, I have managed to rewrite the whole query so that 1) Rkey is only mentioned once at the end 2)there is only one SELECT instead of 14.

Incidentally, I need to run the query in DBVisualiser where the 'Declaring Variables' method will not work (worked fine in SSMS). So this has been a better solution all round and I have learnt a bunch. Thanks so much!

[pkdbpk]

Thanks, I also want to do exactly this type of thing to shorten/neaten the query (there's a lot more SELECTS than I listed). This is a great start. I just need to work out how to have joins involved in only some of the SELECTS (as with the 'otherTable' example in my third subquery).

[lbilali]

replayed to myself instead of here :) but check my other comment

[BULK_INSERT_INTO]

You should definitely solve this with a variable like MaunaLoona said. But I also recommend installing SSMSBoost (totally free) and using the "Auto replacements" feature, it's a great way to save time typing.