Query help - takes a LONG time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query help - takes a LONG time

Author

Topic

pixelmeow
Starting Member

9 Posts

Posted - 2008-10-30 : 10:57:06

Hello,

(I forgot to mention that I'm using VB6 with tables and views. The only thing like stored procedures I've done is Forms 6.)

I have three tables, master line and milestone, with three fields together in each table as the primary key. They are work tables that hold data ingestion from flat files.

In master, each row has many corresponding rows in the other two tables. In line, a recordset is returned only if the PK exists in master. In milestone, a recordset is returned only if the PK exists in line. The queries select records based on three fields which are coded into the view, and two fields which are part of the query that accesses the view.

For example, the line view:
SELECT CASE_IDENTIFIER AS CASE_IDENTIFIER
,DOCUMENT_TYPE AS DOCUMENT_TYPE
,DOCUMENT_NUMBER AS DOCUMENT_NUMBER
,CASE_LINE_NUMBER AS CASE_LINE_NUMBER
,DELETION_INDICATOR AS DELETION_INDICATOR
,MASL AS MASL
,MDE_CODE AS MDE_CODE
,GENERIC_CODE AS GENERIC_CODE
,CASE_LINE_ITEM_QUANTITY AS CASE_LINE_ITEM_QUANTITY
,TOTAL_LINE_VALUE_AMOUNT AS TOTAL_LINE_VALUE_AMOUNT
,CASE_LINE_ITEM_DESCRIPTION AS CASE_LINE_ITEM_DESCRIPTION
FROM CASE_LINE_TEMP
WHERE CASE_IDENTIFIER IN
(SELECT CASE_IDENTIFIER
FROM CASE_MASTER_TEMP
WHERE (CASE_MASTER_STATUS_CODE = 'N'
OR CASE_MASTER_STATUS_CODE = 'P')
AND IMPLEMENTING_AGENCY = 'B'
AND (DOCUMENT_TYPE = 'AMD'
OR DOCUMENT_TYPE = 'LOA')
AND CASE_VERSION_STATUS_CODE = 'I')

To query the view, I use this:
SELECT CASE_IDENTIFIER
,DOCUMENT_TYPE
,DOCUMENT_NUMBER
,CASE_LINE_NUMBER
,DELETION_INDICATOR
,MASL
,MDE_CODE
,GENERIC_CODE
,CASE_LINE_ITEM_QUANTITY
,TOTAL_LINE_VALUE_AMOUNT
,CASE_LINE_ITEM_DESCRIPTION
FROM LINE_IMP_VIEW
WHERE CASE_IDENTIFIER = [case_identifier]
AND DOCUMENT_NUMBER = [document_number]
ORDER BY CASE_IDENTIFIER

(Please pardon the lack of code structure. The preview shows me that all my code structure has been ignored.)

I have to fill another table with these results, then use two more queries similar to the above to update the same table. What I'm doing is filling three arrays with the query results. Average sizes of the arrays are 3,000 for master, 43,000 for line, and 168,000 for milestone. My first attempt at filling the table is to use a nested for loop using the line recordcount as the outer loop and the master or milestone recordcount as the inner loop. The line data must be inserted, then updated with master and milestone, so that the table size will be 43,000 rows.

I'm sure you can see that 43,000 * 168,000 is a LONG time, then 43,000 * 3,000, after the initial 43,000 insert.

Is there any way to optimize this? Please let me know if you need more info.

Thanks,
Teresa

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-30 : 12:42:10

HI pixelmeow, if you enclose the sql in code /code (with square brackets round: like {code} {/code} but (with [ and ] ) it will format


SELECT CASE_IDENTIFIER AS CASE_IDENTIFIER
	  ,DOCUMENT_TYPE AS DOCUMENT_TYPE
	  ,DOCUMENT_NUMBER AS DOCUMENT_NUMBER
	  ,CASE_LINE_NUMBER AS CASE_LINE_NUMBER
	  ,DELETION_INDICATOR AS DELETION_INDICATOR
	  ,MASL AS MASL
	  ,MDE_CODE AS MDE_CODE
	  ,GENERIC_CODE AS GENERIC_CODE
	  ,CASE_LINE_ITEM_QUANTITY AS CASE_LINE_ITEM_QUANTITY
	  ,TOTAL_LINE_VALUE_AMOUNT AS TOTAL_LINE_VALUE_AMOUNT
	  ,CASE_LINE_ITEM_DESCRIPTION AS CASE_LINE_ITEM_DESCRIPTION
  FROM CASE_LINE_TEMP
 WHERE CASE_IDENTIFIER IN
 	(SELECT CASE_IDENTIFIER
	   FROM CASE_MASTER_TEMP
	  WHERE (CASE_MASTER_STATUS_CODE = 'N'
	  	 OR  CASE_MASTER_STATUS_CODE = 'P')
		AND IMPLEMENTING_AGENCY = 'B'
		AND (DOCUMENT_TYPE = 'AMD'
    	 OR  DOCUMENT_TYPE = 'LOA')
		AND CASE_VERSION_STATUS_CODE = 'I')

To query the view, I use this:
SELECT CASE_IDENTIFIER
      ,DOCUMENT_TYPE
      ,DOCUMENT_NUMBER
      ,CASE_LINE_NUMBER
      ,DELETION_INDICATOR
      ,MASL
      ,MDE_CODE
      ,GENERIC_CODE
      ,CASE_LINE_ITEM_QUANTITY
      ,TOTAL_LINE_VALUE_AMOUNT
      ,CASE_LINE_ITEM_DESCRIPTION
  FROM LINE_IMP_VIEW    
 WHERE CASE_IDENTIFIER = [case_identifier]
   AND DOCUMENT_NUMBER = [document_number]
 ORDER BY CASE_IDENTIFIER

-------------
Charlie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 13:15:46

Did understand the purpose of loops? Cant you just use a set based solution to pulate the new table? Just join the tables on related fields and pull what all data you need from them and insert it into new table.

pixelmeow
Starting Member

9 Posts

Posted - 2008-10-30 : 14:48:21

Okay, Charlie, thanks. I should have remembered that from a site I posted to yesterday or the day before.

Visakh16, the purpose of the loops is this:

for each record in line, search in milestone for matches to the record in line. There's one record in line that has 455 matching records in milestone, so that's 455 loops inside one outer loop of the line recordset. The line recordcount is 43,000.

I'm fairly new to T-SQL, I've been using Oracle for 8 years now. Can you give me an example of what you mean? This business of using arrays to get data from three tables and inserting/updating one table with all that is killing me.

Thanks!

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-30 : 15:59:55

Can you please give us some sample data? I think the best solution is to JOIN these three tables.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 00:56:40

quote:
Originally posted by pixelmeow

Okay, Charlie, thanks. I should have remembered that from a site I posted to yesterday or the day before.

Visakh16, the purpose of the loops is this:

for each record in line, search in milestone for matches to the record in line. There's one record in line that has 455 matching records in milestone, so that's 455 loops inside one outer loop of the line recordset. The line recordcount is 43,000.I'm fairly new to T-SQL, I've been using Oracle for 8 years now. Can you give me an example of what you mean? This business of using arrays to get data from three tables and inserting/updating one table with all that is killing me.

Thanks!

to get the matching records you just need a join. no need of looping through each record and find matching ones. If you can provide some sample data we would be able to give with query.

pixelmeow
Starting Member

9 Posts

Posted - 2008-11-06 : 12:11:47

Sorry for the delay, got sidetracked. This is (obviously) very long...

First view = "master_imp_view"


SELECT CASE_IDENTIFIER AS CASE_IDENTIFIER
	  ,DOCUMENT_TYPE AS DOCUMENT_TYPE
	  ,DOCUMENT_NUMBER AS DOCUMENT_NUMBER
	  ,...
  FROM CASE_MASTER_TEMP
 WHERE CASE_IDENTIFIER IN
 	(SELECT CASE_IDENTIFIER
	   FROM LINE_IMP_VIEW)
   AND CASE_IDENTIFIER IN
 	(SELECT CASE_IDENTIFIER
	   FROM CASE_MASTER_TEMP
	  WHERE (...)

2nd view = "line_imp_view"


SELECT CASE_IDENTIFIER AS CASE_IDENTIFIER
	  ,DOCUMENT_TYPE AS DOCUMENT_TYPE
	  ,DOCUMENT_NUMBER AS DOCUMENT_NUMBER
	  ,...
  FROM CASE_LINE_TEMP
 WHERE CASE_IDENTIFIER IN
 	(SELECT CASE_IDENTIFIER
	   FROM CASE_MASTER_TEMP
	  WHERE (...)

3rd view = "case_milestone_view"


SELECT CASE_IDENTIFIER AS CASE_IDENTIFIER
	  ,DOCUMENT_TYPE AS DOCUMENT_TYPE
	  ,DOCUMENT_NUMBER AS DOCUMENT_NUMBER
	  ,MILESTONE AS MILESTONE
	  ,MILESTONE_DATE AS MILESTONE_DATE
  FROM CASE_MILESTONE_TEMP
 WHERE CASE_IDENTIFIER IN
 	(SELECT CASE_IDENTIFIER
	   FROM LINE_IMP_VIEW)
   AND CASE_IDENTIFIER IN
 	(SELECT CASE_IDENTIFIER
	   FROM CASE_MASTER_TEMP
	  WHERE ...)

Table data goes into = "initial_cases"


order *	name *
...
3	CASEID
5	TYPE
6	NUMBER
...
24	OFFEREDDATE
28	FINANCIALIMPLEMENTATIONMILESTONEDATE
...

Now, I need records from line IF they have matching records in master, and IF a few "wheres" match.


INSERT INITIAL_CASES 
	  (CASEID
	  ,TYPE
	  ,NUMBER
	  ,...)
(SELECT CASE_IDENTIFIER
	   ,DOCUMENT_TYPE
	   ,DOCUMENT_NUMBER
	   ,...
  FROM LINE_IMP_VIEW)

Now I need the records from master to update initial_cases.


UPDATE IC
	SET IC.COUNTRYCODE = CMT.COUNTRY_CODE
	   ,...
   FROM INITIAL_CASES IC
	   ,CASE_MASTER_TEMP CMT
  WHERE ...

Now the same for milestone. The problem is that field "milestone". It can be any of several codes, like OFFERED or FINIMP. The value for milestone_date is used to update a date field in initial_cases, depending on the value of milestone. If it's OFFERED, the updated field is initial_cases.OFFEREDDATE. That's where I'm running into the huge time problem. It has to check the value in of milestone in every record in a table that will have at least 164,000 records.

The same thing has to be done one more times with a slightly different condition in the where statement. All data is varchar, with a few money or date format conversions.

Thanks so much for looking.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 12:15:53

do you have appropriate indexes on all the tables?

pixelmeow
Starting Member

9 Posts

Posted - 2008-11-06 : 14:21:27

I don't have indexes because these tables will be truncated and filled each day. If I did it would be the combination of case_identifier, document_type, and document_number. Those three pieces match one record from master to many in line and many in milestone. The only ones we want from master are those that have matching records in line, however.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 00:16:23

if query is taking too long considering putting indexes on appropriate columns. And each day drop indexes before truncation and then recreate only filled.

pixelmeow
Starting Member

9 Posts

Posted - 2008-11-07 : 08:41:26

So after inserting data into the three tables, apply indexes on the important columns? That sounds reasonable. I will try that now!

Thanks!

pixelmeow
Starting Member

9 Posts

Posted - 2008-11-07 : 16:35:19

Just started testing, but it looks good so far. Whew! The indexes and joins really helped!

I used composite indexes on those first three fields, and it speeded things up incredibly! The join suggestions got me to throw out all the array stuff, I was just making things so much harder on myself than it needed to be.

I have some formatting and conditionals to put in the code yet, but not much, and it's not the hard part, anyway.

Thanks, everyone!

Official solution:


(LINE_IMP_VIEW)
SELECT CASE_IDENTIFIER AS CASE_IDENTIFIER
	  ,DOCUMENT_TYPE AS DOCUMENT_TYPE
	  ,DOCUMENT_NUMBER AS DOCUMENT_NUMBER
	  ,CASE_LINE_NUMBER AS CASE_LINE_NUMBER
	  ,DELETION_INDICATOR AS DELETION_INDICATOR
	  ,MASL AS MASL
	  ,MDE_CODE AS MDE_CODE
	  ,GENERIC_CODE AS GENERIC_CODE
	  ,CASE_LINE_ITEM_QUANTITY AS CASE_LINE_ITEM_QUANTITY
	  ,TOTAL_LINE_VALUE_AMOUNT AS TOTAL_LINE_VALUE_AMOUNT
	  ,CASE_LINE_ITEM_DESCRIPTION AS CASE_LINE_ITEM_DESCRIPTION
  FROM CASE_LINE_TEMP
 WHERE CASE_IDENTIFIER IN
 	(SELECT CASE_IDENTIFIER
	   FROM CASE_MASTER_TEMP
	  WHERE (CASE_MASTER_STATUS_CODE = 'N'
	  	 OR  CASE_MASTER_STATUS_CODE = 'P')
		AND IMPLEMENTING_AGENCY = 'B'
		AND (DOCUMENT_TYPE = 'AMD'
    	 OR  DOCUMENT_TYPE = 'LOA')
		AND CASE_VERSION_STATUS_CODE = 'I')
   AND(DOCUMENT_TYPE = 'AMD'
	OR DOCUMENT_TYPE = 'LOA')

(LINE_NOT_IMP_VIEW)
SELECT CASE_IDENTIFIER AS CASE_IDENTIFIER
	  ,DOCUMENT_TYPE AS DOCUMENT_TYPE
	  ,DOCUMENT_NUMBER AS DOCUMENT_NUMBER
	  ,CASE_LINE_NUMBER AS CASE_LINE_NUMBER
	  ,DELETION_INDICATOR AS DELETION_INDICATOR
	  ,MASL AS MASL
	  ,MDE_CODE AS MDE_CODE
	  ,GENERIC_CODE AS GENERIC_CODE
	  ,CASE_LINE_ITEM_QUANTITY AS CASE_LINE_ITEM_QUANTITY
	  ,TOTAL_LINE_VALUE_AMOUNT AS TOTAL_LINE_VALUE_AMOUNT
	  ,CASE_LINE_ITEM_DESCRIPTION AS CASE_LINE_ITEM_DESCRIPTION
  FROM CASE_LINE_TEMP
 WHERE CASE_IDENTIFIER IN
 	(SELECT CASE_IDENTIFIER
	   FROM CASE_MASTER_TEMP
	  WHERE (CASE_MASTER_STATUS_CODE = 'N'
	  	 OR  CASE_MASTER_STATUS_CODE = 'P')
		AND IMPLEMENTING_AGENCY = 'B'
		AND (DOCUMENT_TYPE = 'AMD'
    	 OR  DOCUMENT_TYPE = 'LOA')
		AND CASE_VERSION_STATUS_CODE <> 'I'
		AND CASE_VERSION_STATUS_CODE <> 'CL')
	AND (DOCUMENT_TYPE = 'AMD'
	 OR DOCUMENT_TYPE = 'LOA')

(MASTER_IMP_VIEW)
SELECT CASE_IDENTIFIER AS CASE_IDENTIFIER
	  ,DOCUMENT_TYPE AS DOCUMENT_TYPE
	  ,DOCUMENT_NUMBER AS DOCUMENT_NUMBER
	  ,COUNTRY_CODE AS COUNTRY_CODE
	  ,IMPLEMENTING_AGENCY AS IMPLEMENTING_AGENCY
	  ,COMBATANT_COMMAND AS COMBATANT_COMMAND
	  ,CASE_MASTER_STATUS_CODE AS CASE_MASTER_STATUS_CODE
	  ,CASE_VERSION_STATUS_CODE AS CASE_VERSION_STATUS_CODE
	  ,PREPARING_ACTIVITY AS PREPARING_ACTIVITY
	  ,ACTIVITY_MANAGER_IDENTIFIER AS ACTIVITY_MANAGER_IDENTIFIER
	  ,INITIATOR_ACTIVITY AS INITIATOR_ACTIVITY
	  ,CASE_CATEGORY_CODE AS CASE_CATEGORY_CODE
	  ,CASE_MANAGER_FNAME AS CASE_MANAGER_FNAME
	  ,CASE_MANAGER_LNAME AS CASE_MANAGER_LNAME
	  ,TOTAL_CASE_VALUE_AMOUNT AS TOTAL_CASE_VALUE_AMOUNT
	  ,CUSTOMER_REQUEST_ID AS CUSTOMER_REQUEST_ID
	  ,LOR_RECEIPT_DATE AS LOR_RECEIPT_DATE
	  ,CUSTOMER_ORGANIZATION_NAME AS CUSTOMER_ORGANIZATION_NAME
	  ,CUSTOMER_REQUEST_DATE AS CUSTOMER_REQUEST_DATE
	  ,CUSTOMER_REQUEST_STATUS_CODE AS CUSTOMER_REQUEST_STATUS_CODE
	  ,CUSTOMER_REQUEST_STATUS_DATE AS CUSTOMER_REQUEST_STATUS_DATE
	  ,END_ITEM_DESCRIPTION AS END_ITEM_DESCRIPTION
  FROM CASE_MASTER_TEMP
 WHERE CASE_IDENTIFIER IN
 	(SELECT CASE_IDENTIFIER
	   FROM LINE_IMP_VIEW)

(MASTER_NOT_IMP_VIEW)
SELECT CASE_IDENTIFIER AS CASE_IDENTIFIER
	  ,DOCUMENT_TYPE AS DOCUMENT_TYPE
	  ,DOCUMENT_NUMBER AS DOCUMENT_NUMBER
	  ,COUNTRY_CODE AS COUNTRY_CODE
	  ,IMPLEMENTING_AGENCY AS IMPLEMENTING_AGENCY
	  ,COMBATANT_COMMAND AS COMBATANT_COMMAND
	  ,CASE_MASTER_STATUS_CODE AS CASE_MASTER_STATUS_CODE
	  ,CASE_VERSION_STATUS_CODE AS CASE_VERSION_STATUS_CODE
	  ,PREPARING_ACTIVITY AS PREPARING_ACTIVITY
	  ,ACTIVITY_MANAGER_IDENTIFIER AS ACTIVITY_MANAGER_IDENTIFIER
	  ,INITIATOR_ACTIVITY AS INITIATOR_ACTIVITY
	  ,CASE_CATEGORY_CODE AS CASE_CATEGORY_CODE
	  ,CASE_MANAGER_FNAME AS CASE_MANAGER_FNAME
	  ,CASE_MANAGER_LNAME AS CASE_MANAGER_LNAME
	  ,TOTAL_CASE_VALUE_AMOUNT AS TOTAL_CASE_VALUE_AMOUNT
	  ,CUSTOMER_REQUEST_ID AS CUSTOMER_REQUEST_ID
	  ,LOR_RECEIPT_DATE AS LOR_RECEIPT_DATE
	  ,CUSTOMER_ORGANIZATION_NAME AS CUSTOMER_ORGANIZATION_NAME
	  ,CUSTOMER_REQUEST_DATE AS CUSTOMER_REQUEST_DATE
	  ,CUSTOMER_REQUEST_STATUS_CODE AS CUSTOMER_REQUEST_STATUS_CODE
	  ,CUSTOMER_REQUEST_STATUS_DATE AS CUSTOMER_REQUEST_STATUS_DATE
	  ,END_ITEM_DESCRIPTION AS END_ITEM_DESCRIPTION
  FROM CASE_MASTER_TEMP
 WHERE CASE_IDENTIFIER IN
 	(SELECT CASE_IDENTIFIER
	   FROM LINE_NOT_IMP_VIEW)

VB code:


    strSQL = "TRUNCATE TABLE INITIAL_CASES"
    Call doDBaction("query", strSQL)

    strSQL = "INSERT INITIAL_CASES" & vbCrLf _
           & "      (CASEID" & vbCrLf _
           & "      ,TYPE" & vbCrLf _
           & "      ,NUMBER" & vbCrLf _
           & "      ,LINENUMBER" & vbCrLf _
           & "      ,MASL" & vbCrLf _
           & "      ,GENERALCODE" & vbCrLf _
           & "      ,MDECODE" & vbCrLf _
           & "      ,CASELINEITEMQUANTITY" & vbCrLf _
           & "      ,CASELINEITEMDESCRIPTION)" & vbCrLf _
           & "(SELECT CASE_IDENTIFIER" & vbCrLf _
           & "       ,DOCUMENT_TYPE" & vbCrLf _
           & "       ,DOCUMENT_NUMBER" & vbCrLf _
           & "       ,CASE_LINE_NUMBER" & vbCrLf _
           & "       ,MASL" & vbCrLf _
           & "       ,GENERIC_CODE" & vbCrLf _
           & "       ,MDE_CODE" & vbCrLf _
           & "       ,CASE_LINE_ITEM_QUANTITY" & vbCrLf _
           & "       ,CASE_LINE_ITEM_DESCRIPTION " & vbCrLf _
           & "  FROM LINE_IMP_VIEW)" & vbCrLf
    Call doDBaction("query", strSQL)

    strSQL = "INSERT INITIAL_CASES" & vbCrLf _
           & "      (CASEID" & vbCrLf _
           & "      ,TYPE" & vbCrLf _
           & "      ,NUMBER" & vbCrLf _
           & "      ,LINENUMBER" & vbCrLf _
           & "      ,MASL" & vbCrLf _
           & "      ,GENERALCODE" & vbCrLf _
           & "      ,MDECODE" & vbCrLf _
           & "      ,CASELINEITEMQUANTITY" & vbCrLf _
           & "      ,CASELINEITEMDESCRIPTION)" & vbCrLf _
           & "(SELECT CASE_IDENTIFIER" & vbCrLf _
           & "       ,DOCUMENT_TYPE" & vbCrLf _
           & "       ,DOCUMENT_NUMBER" & vbCrLf _
           & "       ,CASE_LINE_NUMBER" & vbCrLf _
           & "       ,MASL" & vbCrLf _
           & "       ,GENERIC_CODE" & vbCrLf _
           & "       ,MDE_CODE" & vbCrLf _
           & "       ,CASE_LINE_ITEM_QUANTITY" & vbCrLf _
           & "       ,CASE_LINE_ITEM_DESCRIPTION " & vbCrLf _
           & "  FROM LINE_NOT_IMP_VIEW)" & vbCrLf
    Call doDBaction("query", strSQL)
    
    strSQL = "UPDATE IC" & vbCrLf _
           & "   SET IC.COUNTRYCODE = MIV.COUNTRY_CODE" & vbCrLf _
           & "      ,IC.IA = MIV.IMPLEMENTING_AGENCY" & vbCrLf _
           & "      ,IC.COCOM = MIV.COMBATANT_COMMAND" & vbCrLf _
           & "      ,IC.STATUSCODE = MIV.CASE_VERSION_STATUS_CODE" & vbCrLf _
           & "      ,IC.PREPARINGACTIVITY = MIV.PREPARING_ACTIVITY" & vbCrLf _
           & "      ,IC.ACTIVITYMANAGERID = MIV.ACTIVITY_MANAGER_IDENTIFIER" & vbCrLf _
           & "      ,IC.INITIATINGACTIVITY = MIV.INITIATOR_ACTIVITY" & vbCrLf _
           & "      ,IC.CASECATEGORYCODE = MIV.CASE_CATEGORY_CODE" & vbCrLf _
           & "      ,IC.CASEMANAGER = MIV.CASE_MANAGER_FNAME + ' ' + MIV.CASE_MANAGER_LNAME" & vbCrLf _
           & "      ,IC.TOTALCASEVALUEAMOUNT = CONVERT(MONEY,MIV.TOTAL_CASE_VALUE_AMOUNT)" & vbCrLf _
           & "      ,IC.LORRECEIPTDATE = MIV.LOR_RECEIPT_DATE" & vbCrLf _
           & "      ,IC.CUSTOMERREQUESTDATE = MIV.CUSTOMER_REQUEST_DATE" & vbCrLf _
           & "      ,IC.CUSTOMERREQUESTSTATUSCODE = MIV.CUSTOMER_REQUEST_STATUS_CODE" & vbCrLf _
           & "      ,IC.CUSTOMERREQUESTSTATUSDATE = MIV.CUSTOMER_REQUEST_STATUS_DATE" & vbCrLf _
           & "      ,IC.ENDITEMDESCRIPTION = MIV.END_ITEM_DESCRIPTION" & vbCrLf _
           & "  FROM INITIAL_CASES IC" & vbCrLf _
           & "      ,MASTER_IMP_VIEW MIV" & vbCrLf _
           & " WHERE IC.CASEID = MIV.CASE_IDENTIFIER" & vbCrLf _
           & "   AND IC.NUMBER = MIV.DOCUMENT_NUMBER" & vbCrLf _
           & "   AND IC.TYPE = MIV.DOCUMENT_TYPE" & vbCrLf
    Call doDBaction("query", strSQL)

    strSQL = "UPDATE IC" & vbCrLf _
           & "   SET IC.COUNTRYCODE = MIV.COUNTRY_CODE" & vbCrLf _
           & "      ,IC.IA = MIV.IMPLEMENTING_AGENCY" & vbCrLf _
           & "      ,IC.COCOM = MIV.COMBATANT_COMMAND" & vbCrLf _
           & "      ,IC.STATUSCODE = MIV.CASE_VERSION_STATUS_CODE" & vbCrLf _
           & "      ,IC.PREPARINGACTIVITY = MIV.PREPARING_ACTIVITY" & vbCrLf _
           & "      ,IC.ACTIVITYMANAGERID = MIV.ACTIVITY_MANAGER_IDENTIFIER" & vbCrLf _
           & "      ,IC.INITIATINGACTIVITY = MIV.INITIATOR_ACTIVITY" & vbCrLf _
           & "      ,IC.CASECATEGORYCODE = MIV.CASE_CATEGORY_CODE" & vbCrLf _
           & "      ,IC.CASEMANAGER = MIV.CASE_MANAGER_FNAME + ' ' + MIV.CASE_MANAGER_LNAME" & vbCrLf _
           & "      ,IC.TOTALCASEVALUEAMOUNT = CONVERT(MONEY,MIV.TOTAL_CASE_VALUE_AMOUNT)" & vbCrLf _
           & "      ,IC.LORRECEIPTDATE = MIV.LOR_RECEIPT_DATE" & vbCrLf _
           & "      ,IC.CUSTOMERREQUESTDATE = MIV.CUSTOMER_REQUEST_DATE" & vbCrLf _
           & "      ,IC.CUSTOMERREQUESTSTATUSCODE = MIV.CUSTOMER_REQUEST_STATUS_CODE" & vbCrLf _
           & "      ,IC.CUSTOMERREQUESTSTATUSDATE = MIV.CUSTOMER_REQUEST_STATUS_DATE" & vbCrLf _
           & "      ,IC.ENDITEMDESCRIPTION = MIV.END_ITEM_DESCRIPTION" & vbCrLf _
           & "  FROM INITIAL_CASES IC" & vbCrLf _
           & "      ,MASTER_NOT_IMP_VIEW MIV" & vbCrLf _
           & " WHERE IC.CASEID = MIV.CASE_IDENTIFIER" & vbCrLf _
           & "   AND IC.NUMBER = MIV.DOCUMENT_NUMBER" & vbCrLf _
           & "   AND IC.TYPE = MIV.DOCUMENT_TYPE" & vbCrLf
    Call doDBaction("query", strSQL)

    msArray(1, 1) = "OFFERACC"
    msArray(1, 2) = "ACCEPTEDDATE"
    msArray(2, 1) = "XSTATUS"
    msArray(2, 2) = "CANCELLEDMILESTONESTATUSDATE"
    msArray(3, 1) = "CASEASGN"
    msArray(3, 2) = "CASEASSIGNEDDATE"
    msArray(4, 1) = "CASERETD"
    msArray(4, 2) = "CASERETURNEDDATE"
    msArray(5, 1) = "OSTATUS"
    msArray(5, 2) = "CASESTATUSOFFEREDMILESTONEDATE"
    msArray(6, 1) = "DCSGN"
    msArray(6, 2) = "COUNTERSIGNATUREDATE"
    msArray(7, 1) = "DOCMNTSENT"
    msArray(7, 2) = "DOCUMENTSENTDATE"
    msArray(8, 1) = "FINIMP"
    msArray(8, 2) = "FINANCIALIMPLEMENTATIONMILESTONEDATE"
    msArray(9, 1) = "ISTATUS"
    msArray(9, 2) = "IMPLEMENTATIONMILESTONESTATUSDATE" 'or "IMPLEMENTATIONDATE"
    msArray(10, 1) = "MILIMP"
    msArray(10, 2) = "IMPLEMENTATIONMILESTONESTATUSDATE"
    msArray(11, 1) = "CASEINIT"
    msArray(11, 2) = "INITIATINGDATE"
    msArray(12, 1) = "DOCINIT"
    msArray(12, 2) = "INITIATINGDATE"
    msArray(13, 1) = "MILAP"
    msArray(13, 2) = "MILAPDATE"
    msArray(14, 1) = "MILSGN"
    msArray(14, 2) = "MILDEPSIGNDATE"
    msArray(15, 1) = "OFFERED"
    msArray(15, 2) = "OFFEREDDATE"
    msArray(16, 1) = "OED"
    msArray(16, 2) = "OFFEREXPIRATIONDATE"
    msArray(17, 1) = "RSTATUS"
    msArray(17, 2) = "RSTATUSDATE"
    msArray(18, 1) = "WSTATUS"
    msArray(18, 2) = "WSTATUSDATE"
    
    For i = 1 To arrayLen '18
        strSQL = " UPDATE IC" & vbCrLf _
               & "    SET IC." & msArray(i, 2) & " = MIV.MILESTONE_DATE" & vbCrLf _
               & "   FROM INITIAL_CASES IC" & vbCrLf _
               & "       ,MILESTONE_NOT_IMP_VIEW MIV" & vbCrLf _
               & "  WHERE MIV.CASE_IDENTIFIER = IC.CASEID" & vbCrLf _
               & "    AND MIV.DOCUMENT_TYPE = IC.TYPE" & vbCrLf _
               & "    AND MIV.DOCUMENT_NUMBER = IC.NUMBER" & vbCrLf _
               & "    AND MIV.MILESTONE = '" & msArray(i, 1) & "'" & vbCrLf
        Call doDBaction("query", strSQL)
    Next i

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources