Using UNIONS - Junior Programmer????

Using UNIONS - Junior Programmer????

Post by Brad Isaac » Sat, 17 Aug 2002 09:22:51



Dear friends,

I am working with SQL Server 2000 and I am using UNIONS... I have 3 Views
joined nicely together with 16 fields...now I must join just 2 more fields
although they are from another View.  Is there a way to do this without
placing null values within the fields not used.

Just wondering , if there an easier way to perform this code.

Any ideas would be greatly appreciated,

My code is below:

Thanks in advance,

Brad Isaacs
Junior Programmer Stored Procs

************* CODE  BELOW *************

-- DEFINE VARIABLES

DECLARE @outline_level             INTEGER
DECLARE @cnt                            INTEGER
DECLARE @ProjectName             VARCHAR (255)
DECLARE @ProjectId                   VARCHAR(64)

-- DEFINE TABLES

CREATE TABLE #ProjectCoManagers(
             ProjectId                         VARCHAR (32)      NULL,
             CoManagerName            VARCHAR (100)   NULL,
)

CREATE  TABLE #ResultSet (
             ProjectID                         VARCHAR (225)   NULL,
             ProjectName                    VARCHAR (255)  NULL,
             ProjectCode                     VARCHAR (50)     NULL,
             ProjRank                          VARCHAR (50)     NULL,
             ProjectManager                VARCHAR (100)   NULL,
             ProjectSponsor                 VARCHAR(100)   NULL,
             ElementName                   VARCHAR (255) NULL,
             ElementType                    VARCHAR (32)   NULL,
             TreeLevel                         INT                     NULL,
             RANK                               INT
NULL,
             Outline_sequence            VARCHAR(255) NULL,
             StartDate                         VARCHAR(255) NULL,
             EndDate                         VARCHAR(255)  NULL,
             Variance                          VARCHAR(50)     NULL,
       ParentID                          VARCHAR(255)   NULL,
             ElementID                        VARCHAR(255)    NULL,
            CoManagerName            VARCHAR (1000)   NULL

)

CREATE  TABLE #TMP_USER_PROJECTS (
  ElementID                          VARCHAR(32)    NULL,
  ElementType                      VARCHAR(32)    NULL,
  ElementName                     VARCHAR(255)   NULL,
 )

CREATE CLUSTERED INDEX #AK_TMP_USER_PROJECTS ON
#TMP_USER_PROJECTS(ElementID )

--  Call GetUserProjectsFromElements

  EXECUTE GetUserProjectsFromElements @UserName, @ElementID, @ElementType

--Update the resultset table

INSERT INTO #ResultSet

--  Query for Projects

SELECT   P.ProjectID,
                       P.ProjectName,
         P.CharterNo AS ProjectCode,
         P.Scope_Priority AS ProjRank,
        R.SFName + ' ' + R.SLName AS ProjectManager,
         Res.SFName + ' ' + Res.SLName  AS ProjectSponsor,
        ProjectName AS ElementName,
       'Project' As ElementType,
                 0 As TreeLevel,
                              0 AS RANK,
                              '' AS Outline_sequence,
                               P.Rollup_PrefixedStartDate AS StartDate,
                               P.Rollup_PrefixedFinishDate AS FinishDate,
                               P.Rollup_Variance AS Variance,
                        null AS ParentID,
                        P.ProjectID AS ElementID,
                              ' ' AS CoManagerName

FROM dbPMO_Projects P JOIN dbPMO_Resources R ON (P.Scope_ProjectManager =
R.UserName)
                                                       INNER JOIN
dbPMO_Resources Res ON (P.Scope_ProjectSponsor = Res.UserName)
             WHERE P.ProjectID IN (Select Elementid from #TMP_USER_PROJECTS)

UNION

--  Query for Deliverables

SELECT  P.ProjectID,
                P.ProjectName,
                ' ' AS ProjectCode,
                 D.Scope_Priority AS ProjRank,
                R.SFName + ' ' + R.SLName AS ProjectManager,
                 Res.SFName + ' ' + Res.SLName  AS ProjectSponsor,
  D.DeliverableName AS ElementName,
     'Deliverable' As ElementType,
          D.TreeLevel As TreeLevel,
                D.RANK,
               '' AS Outline_sequence,
               D.Rollup_PrefixedStartDate AS StartDate,
              D.Rollup_PrefixedFinishDate AS FinishDate,
             D.Rollup_Variance AS Variance,
             CASE WHEN TreeLevel = 1 THEN
                                    P.ProjectID ELSE
                                    D.ParentID
             END AS ParentID,
                 D.DeliverableID AS ElementID,
                            ' ' AS CoManagerName

FROM dbPMO_Deliverables D INNER JOIN dbPMO_Projects P ON (D.ProjectID =
P.ProjectID)
                                                               INNER JOIN
dbPMO_Resources R ON (P.Scope_ProjectManager = R.UserName)
                                                               INNER JOIN
dbPMO_Resources Res ON (P.Scope_ProjectSponsor = Res.UserName)
              WHERE D.ProjectID IN (Select Elementid from
#TMP_USER_PROJECTS)

UNION

-- Query for Tasks

       SELECT    P.ProjectID,
           P.ProjectName,
                       '' AS ProjectCode,
                       T.Scope_Priority AS ProjRank,
                        R.SFName + ' ' + R.SLName AS ProjectManager,
                        Res.SFName + ' ' + Res.SLName  AS ProjectSponsor,
          T. TaskName AS ElementName,
          'Task' As ElementType,
                        T.TreeLevel AS TreeLevel,
                        T.Rank AS Rank,
                        '' AS Outline_sequence,
                        T.Rollup_PrefixedStartDate AS StartDate,
                         T.Rollup_PrefixedFinishDate AS FinishDate,
                         T.Rollup_Variance AS Variance,
       CASE WHEN TreeLevel = 1 THEN
                              T.ProjectID ELSE
                              T.ParentID
       END AS ParentID,
        T.TaskID AS ElementID,
                      ' ' AS CoManagerName

      FROM  dbPMO_Tasks T INNER JOIN dbPMO_Projects P ON (T.ProjectID =
P.ProjectID)
                                              INNER JOIN dbPMO_Resources R
ON(P.Scope_ProjectManager = R.UserName)
                                              INNER JOIN dbPMO_Resources Res
ON (P.Scope_ProjectSponsor = Res.UserName)

     WHERE T.ProjectID IN (Select Elementid from #TMP_USER_PROJECTS)

****************************HERE Is the added 2 fields but I cannot seem to
figure out how to make it work without using the UNION,,just trying to use a
variable & temporary table then ?????????????

--Update the #ProjectCoManagers  table
--Query to find Co-Managers for each Project
  INSERT INTO #ProjectCoManagers
                                           SELECT    P.ProjectID,
                                                             R.SFName + ' '
+ R.SLName AS CoManagerName

                                            FROM  dbPMO_PrjResources PRes
INNER JOIN dbPMO_Projects P ON (PRes.ProjectID = P.ProjectID)

INNER JOIN dbPMO_Resources R ON(PRes.UserName = R.UserName)

       WHERE PRes.ProjectID IN (Select Elementid from #TMP_USER_PROJECTS)
AND  PRes.FunctionType = 'Co-Manager'

UPDATE #ResultSet

--- Update Outline Sequence, Levels are copied from dbPMO

SET @outline_level=0   -- start at 0 to cycle thru the recordsets

WHILE @outline_level IS NOT NULL
BEGIN
   UPDATE ch
     SET
outline_sequence=par.outline_sequence+Space(3-Len(LTrim(Str(ch.rank))))+LTri
m(Str(ch.rank))+'.'
      FROM #ResultSet ch, #ResultSet par
      WHERE par.Treelevel=@outline_level AND ch.parentid=par.elementid

   SET @cnt = @@ROWCOUNT       -- amount of records retruned

   IF @cnt=0 SET @outline_level=NULL
   ELSE
   BEGIN
      SET @outline_level=@outline_level+1
      --PRINT 'Level ' + Cast(@outline_level AS VARCHAR(3)) + ' ' +
Cast(@cnt AS VARCHAR(5))
   END
END

--  If there is no data to display,,display this message to the user

  INSERT INTO #ResultSet

  VALUES('<NO RECORDS AVAILABLE FOR SELECTIONS PROVIDED>',
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
  NULL,
   NULL)

--DISPLAY THE RESULTS

SELECT *
FROM  #ResultSet
             WHERE Outline_sequence IS NOT NULL
ORDER BY  Outline_sequence
GO
-----------------------------
Thanks and have a good day,
Brad Isaacs
Systemcorp ALG Inc.
Friends Tel: (514) 339-5334 Ext: 268
Tel: (514) 339-1067 Ext: 268
Fax: (514) 339-9776
Email: bisa...@systemcorp.com
------

 
 
 

Using UNIONS - Junior Programmer????

Post by John Be » Sun, 18 Aug 2002 00:14:08


Hi

If I understand this correctly you are looking to insert CoManagerName
and
Outline_sequence into #ResultSet without processing them separately?

To add CoManagerName you will need to outer join the
dbPMO_PrjResources table, with something like the following which is
taken from the last union (I will let you test this as I haven't!):

     SELECT    P.ProjectID,
           P.ProjectName,
           '' AS ProjectCode,
           T.Scope_Priority AS ProjRank,
           R.SFName + ' ' + R.SLName AS ProjectManager,
           Res.SFName + ' ' + Res.SLName  AS ProjectSponsor,
           T. TaskName AS ElementName,
           'Task' As ElementType,
           T.TreeLevel AS TreeLevel,
           T.Rank AS Rank,
           NULL AS Outline_sequence,
           T.Rollup_PrefixedStartDate AS StartDate,
           T.Rollup_PrefixedFinishDate AS FinishDate,
           T.Rollup_Variance AS Variance,
           CASE WHEN TreeLevel = 1
                 THEN T.ProjectID
                 ELSE T.ParentID
           END AS ParentID,
           T.TaskID AS ElementID,
           COALESCE(R1.SFName,'') + ' ' + COALESCE(R1.SLName) AS
CoManagerName
      FROM  ( dbPMO_Tasks T INNER JOIN dbPMO_Projects P ON
(T.ProjectID = P.ProjectID)
            INNER JOIN dbPMO_Resources R ON(P.Scope_ProjectManager =
R.UserName)
            INNER JOIN dbPMO_Resources Res ON (P.Scope_ProjectSponsor
= Res.UserName) )
            ( LEFT OUTER JOIN dbPMO_PrjResources PRes ON (PRes.ProjectID =
T.ProjectID AND PRes.FunctionType = 'Co-Manager')
                INNER JOIN dbPMO_Resources R1 ON (PRes.UserName = R1.UserName) )
     WHERE T.ProjectID IN (Select Elementid from #TMP_USER_PROJECTS)

I can't see a way of doing the outline level within the view as it
requires recusively inserting data into the table.

Where you put 0 into RANK can you not set Outline_sequence to '0'?

If you have seeded all the top level parents, you should be able to do
something like:



BEGIN
   UPDATE #ResultSet
     SET ch.outline_sequence=par.outline_sequence+Space(3-Len(LTrim(Str(ch.rank))))+LTrim(Str(ch.rank))+'.'
      FROM #ResultSet ch, #ResultSet par
      WHERE ch.outline_sequence IS NULL
      AND par.outline_sequence IS NOT NULL
      AND ch.parentid=par.elementid


END

Alternatively if you seed the top level parents in the first select
then you could split the other selects into separate inserts and
populate outline_sequence as you go along.

Your record containing '<NO RECORDS AVAILABLE FOR SELECTIONS
PROVIDED>' is never going to be displayed as Outline_sequence is NULL!

John


> Dear friends,

> I am working with SQL Server 2000 and I am using UNIONS... I have 3 Views
> joined nicely together with 16 fields...now I must join just 2 more fields
> although they are from another View.  Is there a way to do this without
> placing null values within the fields not used.

> Just wondering , if there an easier way to perform this code.

> Any ideas would be greatly appreciated,

> My code is below:

> Thanks in advance,

> Brad Isaacs
> Junior Programmer Stored Procs


 
 
 

1. Junior SQL Programmer

This is not an Agency. An Excellent opportunity for a Junior programmer in
Thousand Oaks, CA. We are a the industry leader in small ticket leasing.
50 Node Network in Migration from Netware to NT. We are looking for someone
to fit into our company cloture, MS-SQL server programming experience, and
one of the following languages:
MS-access, Delphi, VB or C/C++. You decide what's good for you. Develop our
daily processing procedures. Be you're own boss. We are currently a single
MIS department looking for our next hire.
401K
Fabulous Health insurance program and much more.
e-mail you're resume in text, WP, or MS-Word Format WITH SALARY HISTORY to
the address below.

PRINCIPALS ONLY PLEASE ! ! !

--
A. Feiner
My email modified to protect from spammeres. Please use the address below
to reply

2. Dynamic Property Page display using DAO

3. SENIOR & JUNIOR VB/DATABASE PROGRAMMERS****Houston, TX

4. HTTP Connection Ignoring Roles

5. Junior programmer, North London, UK. 13K-18K

6. check shared_pool fragmentation

7. Entry\Junior level programmer wanted

8. The Key To Organizing Your Health Records

9. A junior programmer needs help !

10. A junior programmer

11. Job - Toronto for Junior/Intermediate Programmer

12. UK- Sussex - Oracle Analyst Programmers (Junior & Senior)

13. UK-Sussex - Oracle Analyst Programmers (Junior & Senior)