August 20, 2020 at 1:24 pm
hi, can someone help me what does this code mean/do step by step. thank you so much, i understand the basics but what are the two dotes and the first line
select distinct left(a.unq_id_src_stm,13) partija_13,a.unq_id_src_stm, b.cl_val_id
into #t1
from tez_bdw_tut..sor_pd_ar a
join TEZ_BDW_TUT..sor_ar_x_cl b on a.PD_AR_ID = b.AR_ID
where b.cl_scm_id = 63 and
20200806 between b.eff_dt and isnull(b.end_dt,99991231)
and a.src_substm_id = 7 and a.UNQ_ID_SRC_STM like '00701%'
select distinct a.*, b.PARTIJA, b.STATUS, b.iznos,
(case when a.CL_VAL_ID = 630002 then '0' when a.CL_VAL_ID = 630007 then '1' else 'non existing value' end ) status_new
into ##t2
from #t1 a
join tez_inpt_tut..d_provcalc b on b.PARTIJA = a.partija_13 collate database_default
--where (a.CL_VAL_ID <> 630002 and b.STATUS = 0) or (a.CL_VAL_ID <> 630007 and b.STATUS = 1)
where b.STATUS <> (case when a.CL_VAL_ID = 630002 then '0' when a.CL_VAL_ID = 630007 then '1' else 'non existing value' end )
order by 1,3
August 20, 2020 at 2:21 pm
# is a local temp table, ## is a global temp table, and left function pulls characters from a string, the person who wrote this is pulling 13.
https://www.mssqltips.com/sqlservertip/6035/local-vs-global-sql-server-temporary-tables/
https://www.w3schools.com/Sql/func_sqlserver_left.asp
MCSE SQL Server 2012\2014\2016
August 21, 2020 at 12:37 pm
The dots are part of the three part naming. They're supplying the database name, TEZ_BDW_TUT, skipping the schema name, '..', and supplying the table name, sor_ar_x_cl. The assumption is, the schema that you're running this under is the same, so everything will work. Personally, I think that's a very poor coding practice. I recommend people always include the schema. In fact, usually, exclude the database, but include the schema is the right way.
Also worth noting, aliasing tables as 'a', 'b', 'c' is a sure fire way to cause confusion. Different tables will be 'a' in different queries, making code very difficult to decipher. Better to have a consistent aliasing method that leads to clear communication. All code is communication. Make it as clear as possible. Instead, for the table sor_ar_x_cl, I'd go with saxc for an alias. Use that alias everywhere and things are more clear.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply