Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have three select statements which have the bulk of their columns which are the same except a few. So here is an example

select
*orgid='0'
,'P' as OrgType*
, Lang
,*cast(Null as varchar) as BoardName
,cast(Null as varchar) as SchoolName*

,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(5) THEN 1 ELSE 0 END) as Column1
,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(6) THEN 1 ELSE 0 END) as Column2
,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(4) THEN 1 ELSE 0 END) as Column3

FROM *TABLEA*
WHERE *[Include_All] = 1* 
Group By *[Lang]*



select
orgid=boardname
,'B' as OrgType
, Lang
, BoardName
,cast(Null as varchar) as SchoolName


,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(5) THEN 1 ELSE 0 END) as Column1
,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(6) THEN 1 ELSE 0 END) as Column2
,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(4) THEN 1 ELSE 0 END) as Column3
FROM TABLEB
WHERE [Include_Board] = 1 
Group By [Lang], BoardName


select
orgid=SchoolName
,'S' as OrgType
, Lang
, BoardName
,SchoolName

,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(5) THEN 1 ELSE 0 END) as Column1
,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(6) THEN 1 ELSE 0 END) as Column2
,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(4) THEN 1 ELSE 0 END) as Column3

FROM TABLEC
WHERE [Include_School] = 1 
Group By [Lang], BoardName

So as you can see, the calculated columns are the same in each "grouping". The problem is that I have about 70 calculated fields (which are the same) in all three select statements. This makes reading the script horrible as i have to endlessly scroll.

The main changes are the first couple of columns in the select statement and then the way it groups by at the end.

Right now my current solution is to set strings and execute them. IE, I'll have

header1 = "SELECT FROM all..." 
header2 = "SELECT FROM board.."
header3 = "SELECT FROM school.."
footer1 = "GROUP BY lang"
footer2 = "GROUP BY lang, board"
footer3 = "GROUP BY lang, school" 
Common = CalculatedFields

EXECUTE (header1 + common + footer1)
EXECUTE (header2 + common + footer2)

but this just makes my script all red because its one large string. Not only that I have to break it up into different strings because I have a lot of text.

Any ideas on how to best do this while maintaining readability and clarity?

share|improve this question
    
Are these: WHERE [Include_Board] = 1 all the same? I mean the right-hand side is always 1? And what do those asterisks mean? –  dezso Jun 5 '12 at 16:36
    
The right side is one. THe left side is dynamic. Ie, include_all, include_school and include_baord –  masfenix Jun 5 '12 at 17:41
    
I may see it too simple (or too complicated), but why cannot you define three views (one for each query)? Then your script would look like SELECT * FROM view_A; three times. –  dezso Jun 5 '12 at 17:50
    
Because if there is a change (lets say the spec changes in one of the "sum" columns) then I have to change it three times which is error prone. Espeically when you have about 100 calculated fields. –  masfenix Jun 5 '12 at 18:09
    
Well, I would say then that you should keep you definition outside your query, and 'include' it somehow (say from a table - OK, it sounds awful, I admit). –  dezso Jun 5 '12 at 19:09

2 Answers 2

up vote 0 down vote accepted

Use a CROSS APPLY (or perhaps OUTER APPLY) to replace the case statement in your aggregates like this:

with f as (
    select * from ( values
         (1,4,1,0,0)
        ,(1,5,0,1,0)
        ,(1,6,0,0,1)
    )f(IncludeColumn,SecondInclude,Column1,Column2,Column3)
)

select
  orgid=boardname
  ,'B' as OrgType
  , Lang
  , BoardName
  ,cast(Null as varchar) as SchoolName

  ,SUM(Column1) as Column1
  ,SUM(Column2) as Column2
  ,SUM(Column3) as Column3

FROM TABLEB
CROSS APPLY f
WHERE [Include_Board] = 1 
  AND Table.IncludeColumn  = f.IncludeColumn
  AND TableB.SecondInclude = f.SecondInclude
Group By [Lang], BoardName

where now the table f can (as appropriate) be:

  • a permanent table in your database; or
  • a temporary table defined by the application; or
  • a table variable created within the stored procedure

This has the additional advantage of being data-driven; in the case that your masking needs to be amended, only a data change is required instead of a code change.

share|improve this answer

Not sure what your problem is... the three views solution sounds best. If consistency is your problem with that then automate the creation of the views. Use a function to return the common "logic"

Create function fn_GetStaticViewElement()
   return CONVERT(VARCHAR(MAX),',SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(5) THEN 1 ELSE 0 END) as Column1,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(6) THEN 1 ELSE 0 END) as Column2,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(4) THEN 1 ELSE 0 END) as Column3')

Create proc proc_GenerateViews

'ALTER VIEW vwALL
execSQL ('ALTER VIEW vwALL AS Select ... ' + fnGetStaticViewElement() + '...From...Where...')
'ALTER VIEW Board
execSQL ('ALTER VIEW vwBoard AS Select ... ' + fnGetStaticViewElement() + '...From... Where...')
'ALTER VIEW School
execSQL ('ALTER VIEW vwSchool AS Select ... ' + fnGetStaticViewElement() + '...From... Where...')

to have the data combined: Create 1 huge view, union all the sources, combining all the possible columns. De-normalised. Expose sourcename into a column. use that as part of your filter. Should act like a partitioned view, I think.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.