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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply