Please let me know optimization for this query..

Question

1.00/5 (4 votes)

See more:

SQL

UPDATE #tempSDAT SET [Quad Alignment] = CASE

          WHEN [Solution For Proposal] = 'Azure' THEN 'AppPlat'
          WHEN [Solution For Proposal] = 'CRM' THEN 'AppPlat'
          WHEN [Solution For Proposal] = 'Developer Tools' THEN 'AppPlat'
          WHEN [Solution For Proposal] = 'Dynamics AX' THEN 'AppPlat'
          WHEN [Solution For Proposal] = 'Dynamics CRM' THEN 'AppPlat'
          WHEN [Solution For Proposal] = 'EAP' THEN 'Licensing/Special Projects'
          WHEN [Solution For Proposal] = 'Oracle Migrations' THEN 'AppPlat'
          WHEN [Solution For Proposal] = 'SQL' THEN 'AppPlat'
          WHEN [Solution For Proposal] = 'Desktop' THEN 'BPIO'
          WHEN [Solution For Proposal] = 'Exchange' THEN 'BPIO'
          WHEN [Solution For Proposal] = 'Lync' THEN 'BPIO'
          WHEN [Solution For Proposal] = 'Office 2010' THEN 'BPIO'
          WHEN [Solution For Proposal] = 'Office 365' THEN 'BPIO'
          WHEN [Solution For Proposal] = 'Project' THEN 'BPIO'
          WHEN [Solution For Proposal] = 'Project and Visio' THEN 'BPIO'
          WHEN [Solution For Proposal] = 'SharePoint' THEN 'BPIO'
          WHEN [Solution For Proposal] = 'Visio' THEN 'BPIO'
          WHEN [Solution For Proposal] = 'Core Infrastructure' THEN 'Core IO'
          WHEN [Solution For Proposal] = 'Datacenter' THEN 'Core IO'
          WHEN [Solution For Proposal] = 'ECI' THEN 'Core IO'
          WHEN [Solution For Proposal] = 'MDOP' THEN 'Core IO'
          WHEN [Solution For Proposal] = 'Private Cloud' THEN 'Core IO'
          WHEN [Solution For Proposal] = 'System Center' THEN 'Core IO'
          WHEN [Solution For Proposal] = 'Virtualization' THEN 'Core IO'
          WHEN [Solution For Proposal] = 'Windows 7' THEN 'Core IO'
          WHEN [Solution For Proposal] = 'Core CAL' THEN 'Licensing/Special Projects'
          WHEN [Solution For Proposal] = 'EAP-ECI' THEN 'Licensing/Special Projects'
          WHEN [Solution For Proposal] = 'ECAL' THEN 'Licensing/Special Projects'
          WHEN [Solution For Proposal] = 'Other' THEN 'Licensing/Special Projects'
          WHEN [Solution For Proposal] = 'Windows Phone' THEN 'Licensing/Special Projects'

          ELSE [Quad Alignment]
          END

Posted 19-May-14 1:53am

sarath Nath

Add a Solution

Comments

[no name] 19-May-14 8:58am

Please go back and read the answers you already have. The same techniques apply. Then please go read the FAQ for how to ask a question. Just dumping a bunch of code here is not a question or a description of any kind of a problem. Then you might benefit from learning how to use a query profiling tool.

sarath Nath 20-May-14 1:39am

I am new to this site will surely improve my self..Thank you

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RDBurmon · Accepted Answer · 2014-05-19T07:29:00

You can write your query like this

SQL

UPDATE #tempSDAT
SET [Quad Alignment]
      = CASE
          WHEN CHARINDEX('#' + [Solution For Proposal] + '#','#Azure#CRM#Developer Tools#Dynamics AX#Dynamics CRM#Oracle Migrations#SQL#')  > 0  THEN 'AppPlat'
          WHEN CHARINDEX('#' + [Solution For Proposal] + '#','#Desktop#Exchange#Lync#Office 2010#Office 365#Project#Project and Visio#SharePoint#Visio#')  > 0  THEN 'BPIO'
          WHEN CHARINDEX('#' + [Solution For Proposal] + '#','#EAP#Core CAL#EAP-ECI#ECAL#Other#Windows Phone#')  > 0  THEN 'Licensing/Special Projects'
          WHEN CHARINDEX('#' + [Solution For Proposal] + '#','#Core Infrastructure#Datacenter#ECI#MDOP#Private Cloud#System Center#Virtualization#Windows 7#')  > 0  THEN 'Core IO'
          ELSE [Quad Alignment]
       END

Maciej Los · Accepted Answer · 2014-05-19T10:23:00

Another way is to define, let's say: 'translate table' or 'dictionary table'.

SQL

DECLARE @transtable TABLE (OrygValue VARCHAR(30), NewValue VARCHAR(30))
INSERT INTO @transtable (OrygValue, NewValue)
VALUES('Azure', 'AppPlat'),
('CRM', 'AppPlat'),
('Developer Tools', 'AppPlat'),

and so on...

Than you need to Join both tables:

SQL

UPDATE dstTbl SET [Quad Alignment] = srcTbl.NewValue
FROM #tempSDAT AS dstTbl INNER JOIN @transtable AS srcTbl ON dstTbl.[Quad Alignment] = @transtable.OrygValue

That's all :)

Please let me know optimization for this query..

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0