Variables in QTP scripting

There are 3 types of variable declarations in QTP /

1. Dim:
Variables declared by dim statement are available for the current context only (Script/Procedure)

2. Public :
Variables declared by public statement are available to all the scripts and the procedures

3. Private
Variable declared by the private statement are available only to the script/file in which it is declared.

SQL to pull Dynamic Test Summary Report from ALM ..Quick view about the Project Status!!!!

We often get Queries on the Status of the Execution /Project .Requirement Coverage ,test Case associated with it and Defect if any associated with it.
In ALM we do not have a Standardized Report in the form of Excel/Word doc which just pulls the Report.We end up doing a Manual Work ..Manually pick the Requirements Pick the test case ..then defects if any...Imagine a Project with Multiple Requirements and 1000 Test Cases ..this is almost a impossible task..So Solution??????????

Go to Dashboard in ALM ,Select a New Excel Report ....

Query...

   SELECT t1.RC_REQ_ID AS RequirementID,
  t1.RQ_REQ_NAME    AS REQUIREMENTDESC,
  t1.TS_NAME        AS TESTCASENAME,
  BG_BUG_ID         AS DEFECTID,
  BG_SUMMARY,
  BG_DESCRIPTION
FROM
  (SELECT *
  FROM REQ R,
    REQ_COVER RC,
    TEST T
  WHERE RC_ENTITY_ID=T.TS_TEST_ID
  AND Rq_req_product='PROJECT NAME'
  AND R.RQ_REQ_ID   =RC.RC_REQ_ID
  )T1
LEFT OUTER JOIN
  ( SELECT * FROM LINK L,BUG B WHERE L.LN_BUG_ID=B.BG_BUG_ID
  )T2
ON T1.TS_TEST_ID       =T2.LN_ENTITY_ID
AND T2.LN_ENTITY_TYPE IN ('TEST','TESTCYCL')
AND BG_PROJECT         ='PROJECT NAME'

Report Done!!!!!!!!!!!!!!
Let us know if you need any assistance.

Excel Handling in QTP

Hi Folks,
Handling Excel file is very impotant aspect of QTP coding .It is used in parameterization.
I am describing the process in below steps

1. Reference Object:     

 First step of excel handling is to create a reference object, as per below

Set myexcelObj=CreateObject("excel.application")

Now the myexcelObj is not actully holding anything , it is even not pointing to any excel file yet

2.Linking a workbooks(Excel File):

Second Step is to tell your excel reference object that it is gonna point to an excel file(Workbook, both is same thing).so our code will be as below 
.Please note that myexcelObj can point(Handle) more than 1 excel file.

Set myexcelObj=CreateObject("excel.application")

Set myexcelWorkBook =myexcelObj.workbooks.open(FilePATH)

3. Pointing to Worksheet:

Now the myexcelWorkbook object will point to the actual excel file , so the next step is to point the worksheet in that particular file.We can point the worksheet by index or by the sheetname as below

Set myexcelObj=CreateObject("excel.application")

Set myexcelWorkBook =myexcelObj.workbooks.open(FilePATH)

Set myexcelWorksheet=myexcelworkBook.worksheets(SheeetName)

4. Getting the Data:

Once we have reference of the workSheet , getting the data is quite easy .

Set myexcelObj=CreateObject("excel.application")

Set myexcelWorkBook =myexcelObj.workbooks.open(FilePATH)

Set myexcelWorksheet=myexcelworkBook.worksheets(SheeetName)

exceldata=myexcelWorksheet.cells(1,"A").value

             Or

exceldata=myexcelWorksheet.cells(1,1).value

Now we have received the data as "exceldata" .so the final code is as shown above

Hope this helped.

N'th Highest salary in Employee Table

Hi Folks ,
What is the most common question in any of SQL development/DB Testing Interview.

write a query for
2'nd highest salary in employee table.

Generally we use a nested query approach for this question as per below

select employee_sal,employee_name 

from employee where employee_sal=(select max(employee_sal) 

from employee where employee_sal<(select max(employee_sal) 

from employee))

Now if I ask for 3rd highest salary , then again we have to add one more level of subquery...cool...?
but if i ask for 12th highest salary then hmm????
So obviously this is not the right way to wrtite this type of query .
below is generic for Nth highest salary.
lets see how
2nd higest salary

select employee_sal,employee_name from employee emp1 
where 1=(select count(distinct employee_sal) from employee emp2 where 
emp2.employee_sal>emp1.employee_sal)

3rd Highest salary

select employee_sal,employee_name from employee emp1 
where 2=(select count(distinct employee_sal) from employee emp2 where 
emp2.employee_sal>emp1.employee_sal)

I think now you have got the idea...now
Nth highest salary

select employee_sal,employee_name from employee emp1 
where (N-1)=(select count(distinct employee_sal) from employee emp2 where 
emp2.employee_sal>emp1.employee_sal)

Hope this helped

 Please subscribe for the Blog , if this helped you 

String And Array Manipulations in VB Scripting: Part2: String Function

Most Useful String Functions:

Hello folks,, I will be discussing few string function which are very useful in our day to day automation coding.

1. InStr([start, ]string1, string2[, compare])

most use variant of this function is as below

Instr(str1,str2)..

This function searches the occurrence of the str2 in the string str1, and returns the first occurrence of the str2.

str1="This world is good world"

str2="world"

msgbox Instr(str1,str2)

:OutPut 6

Notice that it is the position not the index or the subscript as one can confuse it with arrays in which the base is 0.

Now the more advanced version

str1="This world is good world"

str2="world"

msgbox Instr(7,str1,str2)

:output 20

Here the first parameter '7' is optional, it shows the starting point of the search.So the first occurrence of "world " is ignored....
The second optional parameter  in below is for comparison mode(0,1).'0 ' means the exact matching(case sensitive, binary comp), '1' means textual comp(case insensitive,textual comp)

str1="This world is good world"

str2="World"

msgbox Instr(7,str1,str2,0)

msgbox Instr(7,str1,str2,1) 

:output 0

:ouput 20

This completes the Instr Function 

2. InStrRev([start, ]string1, string2[, compare]): Identical to Instr but the search is reversed it search the string from the right side, rest all is same.


Hope it helped..

3.Mid(string, start[, length]): It is one of the very very very important functions in VbScripting.
it gives any specific part of the string .

str1="Hello"

now i want to extract the second letter in it. As it is not a array so we cant use str(1).
So we use

str1="Hello" 
str2=mid(str1,2,1) 
msgbox str2

Output: e 

Now in this statement

mid(str1,2,1) 

Instructs the VB engine that it has to start from the second element(2 as second parameter) and extract the 1 element(1 as the third parameter) from the string str1(first parameter).

This function is used mostly in reversal of string without using reverse function.
see post.
Hope this helped

String And Array Manipulations in VB Scripting: Part1

Array Declaration: .Array declaration are of two types,depending upon the use of the array and the fact whether we know the size of array in advance.

Below are described these one by one.
Static Declaration: In this we must know the exact size of the Array we are gonna use.Below is syntax for the array declaration

Dim Ary(10)..

Array are zero based in VBScriptig so the total no of element in the above mentioned array is 10+1=11 elements.
During declaration of the array we mention the maximum subscript we are gonna use.

data can be assigned  using the indexed

Ary(0)=15

Ary(1)=50

and in same manner it can be retrieved too.

var1=Ary(1)
msgbox var1

Output: 50

Arrays can be multidimensional (maximum 60 dimensions). Below is an example of 2-D array with 10 rows and 11 columns.

Dim Ary(9,10)

Dynamic Array Declaration: In this we don't need to know the exact size of array we can change the size of array as we move further.

Dim DynamicAry()

Redim DynamicAry()

Now when we use dynamic array we dont mention sige subscript in Dim statement.
Instead each time while using we mentioned the increased size.

Dim DynamicAry()



Redim DynamicAry(0)
DynamicAry(0)=12
Redim Preserve DynamicAry(1)
DynamicAry(1)=13
msgbox DynamicAry(0)
msgbox DynamicAry(1)



:Output

12

13

Preserve is a keyword which tells the VBScript engine to use preserve the content the of array while changing the size of the array.

I will be posting more on String and Array manipulation in future posts.Keep watching.... :)
Please comment for any query or concerns.

Query to return only test sets with Non Closed defects

HI ,
Below query will give the result for all the test set , which will have linked defect status as non closed status.

Please  notice that this query will account the defects which are conntect to test set only. (there miight be some defect which are linked to test case or test step.)

SO this will give us the name of the Test Set and Ensure that these Test Set have doesnt have any closed defect.

select cy_cycle_id as 'Test Set Name',BG_status 'BUG Status' from cycle,link,bug
where cy_cycle_id=LN_ENTITY_ID and LN_ENTITY_TYPE='CYCLE' and ln_bug_id=bg_bug_id

and cy_cycle_id not in (select ln_entity_id from link,bug
where LN_ENTITY_TYPE='CYCLE' and ln_bug_id=bg_bug_id and bg_status='Closed') 

Please comment if you have nay issue or the cpncern with the result set