Not in script in VBscript

[kdjonesmtb2]

Hello,

Is there a way in Vbscript to determine the records not in a comparison between two recordsets

I have two sql results from a query that was performed on two datasets from two different servers. I am trying to move the results into Vbscript so that I can use the HP QTP testing tool. I wanted to run validation in QTP to detemine the number records that are not in a destination file and create variance percentage that can be compared to a tolerance of 10%.

Currently I have to use the SQL Import Export wizard to move the result temporary tables from both SQL servers to a Development server that I can write to.

I would like to eliminate this step by creating the records sets in VBscript from each server from within the QTP tool and then run the variance process.


Here is a sample of the SQL code I am using

Dim RXtest
query ="select distinct * " &_
"into ##Variance4 " &_
"from ##Medmetrics_2012_QTP " &_
"where ##Medmetrics_2012_QTP.MEMBERCK in (select externalId collate SQL_Latin1_General_CP1_CI_AI from CCA_RX where externalId <> ' ' ) " &_
"and NDCDrugCode not in (select CCA_RX.ndc_id collate SQL_Latin1_General_CP1_CI_AI from CCA_RX) " &_
"and NDCDrugCode is not null " &_
"and MEMBERCK <> ' ' "


Set rs5= conn.Execute(query)

Dim Comp
Dim DT_Tolerance

Comp=Result4


DT_Tolerance=trim(datatable("Tolerance",dtGlobalSheet))


If Comp>=DT_Tolerance Then
Reporter.ReportEvent 0, "Variance Compare", "Passed Variance less than 10 percent; CCA Tolerance=[" & DT_Tolerance & "]; CCA Actual=[" & Comp & "]"
Else
Reporter.ReportEvent 1, "Variance Compare", "Failed Variance greater than 10 percent ; CCA Tolerance=[" & DT_Tolerance & "]; Medmetrics Actual=[" & Comp & "]"
End if


If Comp > DT_Tolerance Then
Print ("Variance is within Tolerance")
Print Comp
Else Print ("Variance is not within Tolerance")
Print Comp
End If

 

[Geates]

You want to look into using an ADO object (

http://raqxtr.sstar.com/caspdoc/html/ado_component_reference.htm)

. With it, you can execute queries against a valid sql server which are returned in a recordSet object. From here you can easily compare the results to determine the variance.

I have not seen '<>' used in an SQL statement to symbolize not; '!=' is more common. Also, I haven't seen '#' used in an SQL statement. What does it mean.


-Geates

http://raqxtr.sstar.com/caspdoc/html/chapter_5__developer_s_reference.htm

 

[kdjonesmtb2]

The '##' allows you to create temporary tables. If you use '#' the table will disappear after a session has ended. If you use '##' the table will persist until you manually drop it.