reduce duplication of syntax - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

reduce duplication of syntax

Author

Topic

cardgunner

326 Posts

Posted - 2012-04-10 : 14:07:22

I am entering the same lengthy syntax multiple times thru out a SQL statement. So if i have to adjust the syntax i will than have to adjust it multiple times.

I tried
declare @model varchar
set @model=case when dext816_model.t_spec!='' then dext816_model.t_spec else rtrim(substring(scfg200.t_crob, 1, len(scfg200.t_crob)-3))end

but it errors saying multi-part indentifier **** could not be bound.

IS there a way to make this work?

CardGunner

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-04-10 : 14:41:32

You can't do that without dynamic SQL. Show us the code and we'll see if we can trim it down.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

cardgunner

326 Posts

Posted - 2012-04-10 : 14:45:16

One example is (case when dext816_model.t_spec!='' then dext816_model.t_spec else rtrim(substring(scfg200.t_crob, 1, len(scfg200.t_crob)-3))end)

That is repeated 5 times.

CardGunner

cardgunner

326 Posts

Posted - 2012-04-10 : 14:50:09

What this does is trim off the last 3 charcters from scfg200.t_crob. However if field dext816_model is filled in than use that data. That is just for the model name.

I have similar criteria for manufacture.

That also is repeated 5 times.

CardGunner

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-04-10 : 15:29:09

Please show us your code. A code snippet isn't enough.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

cardgunner

326 Posts

Posted - 2012-04-10 : 15:54:56

[code]
select
case when dext816_mfg.t_spec is NULL then cmcs060.t_dsca else dext816_mfg.t_spec end mfg,
case when dext816_model.t_spec!='' then dext816_model.t_spec else rtrim(substring(scfg200.t_crob, 1, len(scfg200.t_crob)-3))end model,
scfg200.t_clot unit,
scfg010.t_desc category,
scfg200.t_moyr_c u_year,
isnull(cast(sext205.t_mred as varchar),'') meter,
case
when sext205.t_mtyp='1' then 'KM'
when sext205.t_mtyp='2' then 'HOURS'
when sext205.t_mtyp='3' then 'UNITS'
when sext205.t_mtyp='5' then 'MILES'
else '' end meter_type,
scfg200.t_cser sn,
dext816_video.t_spec video,
dext806.t_pris price,
rtrim(case when dext816_mfg.t_spec !='' then dext816_mfg.t_spec else cmcs060.t_dsca end) + ', ' +
rtrim(scfg010.t_desc) + ', ' +
isnull(rtrim(case when dext816_model.t_spec!='' then dext816_model.t_spec else rtrim(substring(scfg200.t_crob, 1, len(scfg200.t_crob)-3))end ),'') + ', ' +
isnull(rtrim(dext816_kword.t_spec), '') + ', ' +
'USED' + ' ' +
rtrim(case when dext816_model.t_spec!='' then dext816_model.t_spec else rtrim(substring(scfg200.t_crob, 1, len(scfg200.t_crob)-3))end )+ ' '+
rtrim(scfg010.t_desc) + ' FOR SALE' unit_kword,
rtrim(case when dext816_mfg.t_spec !='' then dext816_mfg.t_spec else cmcs060.t_dsca end)+' '+
isnull(rtrim(case when dext816_model.t_spec!='' then dext816_model.t_spec else rtrim(substring(scfg200.t_crob, 1, len(scfg200.t_crob)-3))end ),'')+' '+
rtrim(scfg010.t_desc)+' '+cast(scfg200.t_moyr_c as varchar) + ' #'+scfg200.t_clot unit_title,
'NEW/USED ' + cast(scfg200.t_moyr_c as varchar)+' '+
rtrim(case when dext816_mfg.t_spec !='' then dext816_mfg.t_spec else cmcs060.t_dsca end)+' '+
isnull(rtrim(case when dext816_model.t_spec!='' then dext816_model.t_spec else rtrim(substring(scfg200.t_crob, 1, len(scfg200.t_crob)-3))end ),'')+' '+
case when isnull(cast(sext205.t_mred as varchar),'')='' then '' else (rtrim(scfg010.t_desc)+ ' WITH ' + isnull(cast(sext205.t_mred as varchar),'')+' '+
isnull((case when sext205.t_mtyp='1' then 'KM'
when sext205.t_mtyp='2' then 'HOURS'
when sext205.t_mtyp='3' then 'UNITS'
when sext205.t_mtyp='5' then 'MILES'
else '' end ), '') )end +
unit_dsca,
'' unit_header,
'' catg_title,
'' catg_kword,
'' catg_header,
'' catg_dsca

from ttscfg200100 scfg200
join --Category table
ttscfg010100 scfg010 on scfg010.t_cogr=scfg200.t_cogr
join --Manufacture Table
ttcmcs060100 cmcs060 on cmcs060.t_cmnf=scfg200.t_cmnf
left join -- spec table model name
ttdext816100 dext816_model on dext816_model.t_clot=scfg200.t_clot
and dext816_model.t_seqn=990
left join -- spec table video name
ttdext816100 dext816_video on dext816_video.t_clot=scfg200.t_clot
and dext816_video.t_seqn=991
left join -- spec table mfg name
ttdext816100 dext816_mfg on dext816_mfg.t_clot=scfg200.t_clot
and dext816_mfg.t_seqn=992
left join -- spec table keyword
ttdext816100 dext816_kword on dext816_kword.t_clot=scfg200.t_clot
and dext816_kword.t_seqn=993
left join -- combined hour meter table
(SELECT
SUM(sext205.t_mred) AS 't_mred',
sext205.t_clot,
sext205.t_mtyp
FROM
ttsext205100 AS sext205 JOIN
(SELECT
MAX(t_date) AS t_date,
t_clot, t_mseq
FROM
ttsext205100
GROUP BY
t_mseq, t_clot
) tmp ON sext205.t_date = tmp.t_date
and sext205.t_mseq = tmp.t_mseq
and sext205.t_clot = tmp.t_clot
GROUP BY
sext205.t_clot,
sext205.t_mtyp
) sext205 on sext205.t_clot=scfg200.t_clot and scfg200.t_mtyp1_c=sext205.t_mtyp
left join -- Most recent price
(select dext806.t_clot, dext806.t_pris
from ttdext806100 dext806 join
(select t_clot, max(t_efdt)as t_date
from ttdext806100
group by t_clot
) tmp on tmp.t_clot=dext806.t_clot and tmp.t_date=dext806.t_efdt
) dext806 on dext806.t_clot=scfg200.t_clot
where scfg200.t_intt=1
and scfg200.t_stat in (10,80,100)
and scfg010.t_cogr not in ('AUT', 'SHP', 'ME032', 'MT001')
ORDER BY scfg200.t_clot[/code]

CardGunner

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-04-10 : 16:04:07

Your code is making my head spin.

What I would do is put that entire thing into a derived table and instead of that repeated code, do it once and return that as a column from the derived table. Then use that new column outside of the derived table everywhere that part was needed.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

cardgunner

326 Posts

Posted - 2012-04-10 : 16:24:13

It make smy head spin too.

Derived table is like what I did in the code above for most recent price or combined hour meter table?

CardGunner

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources