Is there a way inside of my query to display records for project 0400 and 0401 as only 0400?

Posted on 2012-09-18
Medium Priority
Last Modified: 2012-09-22
I have a coldfusion report used to display our vendor's schedule. We have had to split a few of our project's into multiple phases. We want these phases to appear as one project to our vendor's and I am wondering if inside of my query there is a way to combine records for the multiple projects which are really just phases so that they appear as a single project.

Any help is greatly appreciated.
Question by:J C
  • 3
  • 3
  • 2
LVL 75

Accepted Solution

käµfm³d   👽 earned 1000 total points
ID: 38411327
You could do a CASE statement:

SELECT CASE WHEN [column] = '0400' OR [column] = '0401' THEN '0400'
            ELSE [column]
FROM [table]

Open in new window


Author Comment

by:J C
ID: 38411612
Thank you,that does work. What if I had multiple projects that I needed to merge in such a fashion? Can you show me how that should be constructed?

0500 0r 0501 as an example


Author Comment

by:J C
ID: 38411630
SELECT     projcode AS DoNotUs, phase, [Plan], elev, taskcode, description, startdate, enddate, actualenddate, lsuperappr, ProjectName, ShowOnWeb, projid, aid, [Vendor Name],
                      ltaskcomp, Lot, ShortDesc, DayOfWeek, projlot, [Vendor Code], name, WebProjShort, onhold, CASE WHEN projcode = '0500' OR
                      projcode = '0514' THEN '0500' ELSE projcode END AS projcode
FROM         dbo.ScheduleCalMaster_view
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 75

Expert Comment

by:käµfm³d 👽
ID: 38411878
Yes, just add multiple WHEN clauses:

SELECT CASE WHEN [column] = '0400' OR [column] = '0401' THEN '0400'
            WHEN [column] = '0500' OR [column] = '0501' THEN '0500'
            ELSE [column]
FROM [table]

Open in new window

LVL 52

Expert Comment

ID: 38412011
(no points )

You can also use IN (...). Same result, just a little shorter

         .. WHEN [column] IN('0400', '0401') THEN '0400' ....

Author Comment

by:J C
ID: 38412033
This isn't accomplishing what I was hoping it would. Ill post another question if I need to but to briefly describe one of the results I am searching for is this...

I have a coldfusion page which starts by prompting user's for certain parameters which will filter the report when they hit search. It asks for the project and so on. I went into the view that provides the list of values they select the project from and set it so that these two projid' equal the same projid. It still shows both Project's in the list instead of returning only the one. Am I using the right method to accomplish what I need?

This is the view

SELECT     TOP (100) PERCENT rems.project.projid AS DoNotUse, rems.project.name, rems.project.projcode, rems.project.hidden, rems.project.ounitcode, 
                      rems.project.compcode, rems.project.projtype, dbo.ProjectInfoTable.LotsToFrom, dbo.ProjectInfoTable.ShowOnWeb, dbo.ProjectInfoTable.WebProjName, 
                      dbo.ProjectInfoTable.WebProjName + N'  Lots ' + dbo.ProjectInfoTable.LotsToFrom AS Name2, CASE WHEN projid = '00010107' OR
                      projid = '00010126' THEN '00010107' WHEN projid = '00010119' OR
                      projid = '00010124' THEN '00010119' WHEN projid = '00010120' OR
                      projid = '00010125' THEN '00010120' ELSE projid END AS projid
FROM         rems.project INNER JOIN
                      dbo.ProjectInfoTable ON rems.project.projcode = dbo.ProjectInfoTable.ProjCode
WHERE     (rems.project.hidden = 0) AND (rems.project.name <> 'AVAILABLE') AND (rems.project.projtype = 'hc') AND (dbo.ProjectInfoTable.ShowOnWeb = 1)
ORDER BY dbo.ProjectInfoTable.WebProjName

Open in new window

coldfusion part:
																	<table width="100%" cellpadding="5" cellspacing="0" border="0">
                                <cfquery name="getProjects" datasource="SQL">
                                SELECT * FROM Project_view ORDER BY Name 
                                  <td width="25%" class="content"> <strong>Project:</strong></td>
                                  <td class="content"><select name="ProjID" style="width:300px;" onchange="setLots();">
                                    <option value="-1"<cfif REQUEST.projID EQ -1> selected</cfif>>All 
                                    <option value="">
                                    <cfloop from="1" to="75" index="REQUEST.index">
                                    <cfloop query="getProjects">
                                      <option value="<cfoutput>#getProjects.ProjID#</cfoutput>"<cfif REQUEST.projID EQ getProjects.ProjID> selected</cfif>><cfoutput>#getProjects.Name#</cfoutput></option>

Open in new window

LVL 52

Assisted Solution

_agx_ earned 1000 total points
ID: 38412127
Which "name" do you want to display, the one for the primary id ie 00010107 rather than 00010126?

If so, one option (that shouldn't break anything) is using CASE to assign a priority to the projID values. Assign "2" to the secondary projects you want to skip, and "1" to the rest:

                    WHEN projid IN ('00010126','00010124','00010125')  THEN 2
                     ELSE 1
                     END AS ProjectIDPriority

Then you can either retrieve only id's with priority = 1

             SELECT Name
             FROM    Project_view
             WHERE  ProjectIDPriority = 1
             ORDER BY Name

... OR if you need the other data for some reason, output only names with priority = 1

<cfoutput query="getProjects">
          <cfif ProjectIDPriority eq 1>
               <option ....>#getProjects.Name#</option>
LVL 52

Expert Comment

ID: 38412169
.. or you could simply exclude the secondary project names from the results altogether:

        SELECT Columns
        FROM    YourView
        WHERE  projid NOT IN ('00010126','00010124','00010125')

That's simpler, but adding a priority column might be more flexible.

Edit Off to catch some sleep. (It's been one of those days ...;-)

Edit  Ok, I'm back. Just wanted to add one thing:  

         It still shows both Project's in the list instead of returning only the one.

Correct, because the CASE simply changes the value returned for the "projid". It doesn't "merge" the related rows in any way. Since the view returns multiple columns (not just the projid) your query needs to suppress or filter out the subprojects ie '00010126',...  and return only the record associated primary id's instead ie 00010107. That's what the previous 2 options achieve.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question