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

Posted on 2012-09-18
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
    LVL 74

    Accepted Solution

    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
    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
    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
    LVL 74

    Expert Comment

    by:käµfm³d 👽
    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 51

    Expert Comment

    (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
    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.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 ( <> '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 51

    Assisted Solution

    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:

           SELECT CASE
                        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 51

    Expert Comment

    .. 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

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now