mikenj
02-07-2006, 01:02 PM
I'm working on a script to track software fixes. Using the below
script, I can sucessfully extract all the record data where I
identity the fix is for the primary release and the primary company.
SELECT DETAIL.hotfixID, DETAIL.hotfix_date, PRODUCT.ProductName,
RELEASE.ReleaseNumber, PRIORITY.Priority, CUSTOMER.CompanyName,
DETAIL.status_detail, DEVMANAGER.FirstName,
DEVMANAGER.LastName, STATUS.StatusDesc, DETAIL.short_desc,
DETAIL.long_desc FROM DETAIL INNER JOIN PRODUCT
ON DETAIL.product = PRODUCT.ProductID
INNER JOIN DEVMANAGER ON DETAIL.dev_manager = DEVMANAGER.DevManagerID
INNER JOIN PRIORITY ON DETAIL.priority = PRIORITY.PriorityID
INNER JOIN RELEASE_HOTFIX ON DETAIL.hotfixID = RELEASE_HOTFIX.HotFixID
INNER JOIN STATUS ON RELEASE_HOTFIX.ReleaseStatus = STATUS.StatusID
INNER JOIN RELEASE ON RELEASE_HOTFIX.ReleaseNumber = RELEASE.ReleaseID
INNER JOIN CUSTOMER_HOTFIX ON DETAIL.hotfixID = CUSTOMER_HOTFIX.HotFixID
INNER JOIN CUSTOMER ON CUSTOMER_HOTFIX.CustomerID = CUSTOMER.CompanyID
WHERE (RELEASE_HOTFIX.PrimaryRelease = 1) AND (CUSTOMER_HOTFIX.PrimaryCustomer = 1)
ORDER BY DETAIL.hotfix_date
However, I also want to display the non-primary releases and non-primary
companies in the listing in a format such as:
------------------
Date
Product
Priority
Manager
Primary Release: 1.0
Non-Primary Releass: 1.1, 1.1, 1.3
Primary Customer: ABC
Non-Primary Customers:, DEF, GHI, JKL
------------------
The non-primary releases and customers can be a variable number of entries.
I have a secondary SQL script, but to call this script for each entry seems inefficient:
SELECT ALL CUSTOMER_HOTFIX.HotFixID, CUSTOMER.CompanyName
FROM CUSTOMER, CUSTOMER_HOTFIX, DETAIL
WHERE (((CUSTOMER_HOTFIX.CustomerID = CUSTOMER.CompanyID)
AND (DETAIL.hotfixID = CUSTOMER_HOTFIX.HotFixID)) AND (CUSTOMER_HOTFIX.PrimaryCustomer = 0))
ORDER BY 1 ASC
This probably is a simple application, but I'm banging my head against the wall.
Any information is appreciated.
script, I can sucessfully extract all the record data where I
identity the fix is for the primary release and the primary company.
SELECT DETAIL.hotfixID, DETAIL.hotfix_date, PRODUCT.ProductName,
RELEASE.ReleaseNumber, PRIORITY.Priority, CUSTOMER.CompanyName,
DETAIL.status_detail, DEVMANAGER.FirstName,
DEVMANAGER.LastName, STATUS.StatusDesc, DETAIL.short_desc,
DETAIL.long_desc FROM DETAIL INNER JOIN PRODUCT
ON DETAIL.product = PRODUCT.ProductID
INNER JOIN DEVMANAGER ON DETAIL.dev_manager = DEVMANAGER.DevManagerID
INNER JOIN PRIORITY ON DETAIL.priority = PRIORITY.PriorityID
INNER JOIN RELEASE_HOTFIX ON DETAIL.hotfixID = RELEASE_HOTFIX.HotFixID
INNER JOIN STATUS ON RELEASE_HOTFIX.ReleaseStatus = STATUS.StatusID
INNER JOIN RELEASE ON RELEASE_HOTFIX.ReleaseNumber = RELEASE.ReleaseID
INNER JOIN CUSTOMER_HOTFIX ON DETAIL.hotfixID = CUSTOMER_HOTFIX.HotFixID
INNER JOIN CUSTOMER ON CUSTOMER_HOTFIX.CustomerID = CUSTOMER.CompanyID
WHERE (RELEASE_HOTFIX.PrimaryRelease = 1) AND (CUSTOMER_HOTFIX.PrimaryCustomer = 1)
ORDER BY DETAIL.hotfix_date
However, I also want to display the non-primary releases and non-primary
companies in the listing in a format such as:
------------------
Date
Product
Priority
Manager
Primary Release: 1.0
Non-Primary Releass: 1.1, 1.1, 1.3
Primary Customer: ABC
Non-Primary Customers:, DEF, GHI, JKL
------------------
The non-primary releases and customers can be a variable number of entries.
I have a secondary SQL script, but to call this script for each entry seems inefficient:
SELECT ALL CUSTOMER_HOTFIX.HotFixID, CUSTOMER.CompanyName
FROM CUSTOMER, CUSTOMER_HOTFIX, DETAIL
WHERE (((CUSTOMER_HOTFIX.CustomerID = CUSTOMER.CompanyID)
AND (DETAIL.hotfixID = CUSTOMER_HOTFIX.HotFixID)) AND (CUSTOMER_HOTFIX.PrimaryCustomer = 0))
ORDER BY 1 ASC
This probably is a simple application, but I'm banging my head against the wall.
Any information is appreciated.