SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Results 1 to 3 of 3
  1. #1
    Member
    Join Date
    Jun 2008
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Either BOF or EOF is True, or the current record has been deleted in QTP

    Hi All,

    I 'm executing oracle query in QTP. But it is giving the following error "Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record." But there was no syntax error in the query. the same query if I execute in SQL developer I m getting the getting rows nd columns. But failed in executing with QTP tool . Please help me in this regard.

    Below is the code which I 'm trying to execute in qtp. I 've given the line breakup (&_) at the end of every line.

    Set oRs = CreateObject("ADODB.RecordSet")


    oRs.Open "Select Section_DESC as ""Total POs"", SV, CV, Margin, SV_TO_BE, CV_TO_BE, MARGIN_TO_BE"&_
    " ,(SV+SV_TO_BE) as Total_SV, (CV+CV_TO_BE) as TOTAL_CV,"&_
    " Case When Round((Total_VAT-(ORG_CV+ORG_CVT))/nullif(Total_VAT,0)*100,1) is NULL then 0 else Round((Total_VAT-(ORG_CV+ORG_CVT))/nullif(Total_VAT,0)*100,1) END as Total_Margin"&_
    " from ( Select department_desc, Section_desc, Round(SV,1) as SV, Round(CV,1) as CV, "&_
    " Case When Round((SV_VAT-CV)/nullif(SV_VAT,0)*100,1) is NULL then 0 else Round((SV_VAT-CV)/nullif(SV_VAT,0)*100,1) END as Margin,"&_
    " Round(SVT,1) as SV_TO_BE, Round(CVT,1) as CV_TO_BE, CVT as ORG_CVT, CV as ORG_CV,"&_
    " Case When Round((SVT_VAT-CVT)/nullif(SVT_VAT,0)*100,1) is NULL then 0 else Round((SVT_VAT-CVT)/nullif(SVT_VAT,0)*100,1) END as Margin_To_be, (SV_VAT + SVT_VAT) as Total_VAT"&_
    " from (Select b.department_desc, b.Section_desc, "&_
    " Case When sum(cost_price_raised)/1000 is NULL then 0 else sum(cost_price_raised)/1000 End as CV, "&_
    " Case When sum(selling_value_raised_X_VAT)/1000 is NULL then 0 else sum(selling_value_raised_X_VAT)/1000 End as SV_VAT, "&_
    " Case When sum(selling_value_raised)/1000 is NULL then 0 else sum(selling_value_raised)/1000 End as SV, "&_
    " Case When sum(COST_PRICE_TO_BE_RAISED)/1000 is NULL then 0 else sum(COST_PRICE_TO_BE_RAISED)/1000 End as CVT, "&_
    " Case When sum(SELLING_VALUE_TO_BE_RSD_X_VAT)/1000 is NULL then 0 else sum(SELLING_VALUE_TO_BE_RSD_X_VAT)/1000 END as SVT_VAT, "&_
    " Case When sum(SELLING_VALUE_TO_BE_RAISED)/1000 is NULL then 0 else sum(SELLING_VALUE_TO_BE_RAISED)/1000 END as SVT"&_
    " from BI_HOME_ORDER_TOTALS a, nonfood_hierarchy b where "&_
    " a.sub_class_cd = b.sub_class_cd and a.sub_class_cd in (Select distinct Sub_class_CD from user_access_ref where User_ID = 'T-IY00')"&_
    " GROUP BY b.department_desc, b.Section_desc Order by b.Section_desc, b.department_desc))"&_
    " Union"&_
    " Select department_desc as ""Total POs"", SV, CV, Margin, SV_TO_BE, CV_TO_BE, MARGIN_TO_BE"&_
    " ,(SV+SV_TO_BE) as Total_SV, (CV+CV_TO_BE) as TOTAL_CV,"&_
    " Case When Round((Total_VAT-(ORG_CV+ORG_CVT))/nullif(Total_VAT,0)*100,1) is NULL then 0 else Round((Total_VAT-(ORG_CV+ORG_CVT))/nullif(Total_VAT,0)*100,1) END as Total_Margin"&_
    " from (Select department_desc, Round(SV,1) as SV, Round(CV,1) as CV, "&_
    " Case When Round((SV_VAT-CV)/nullif(SV_VAT,0)*100,1) is NULL then 0 else Round((SV_VAT-CV)/nullif(SV_VAT,0)*100,1) END as Margin,"&_
    " Round(SVT,1) as SV_TO_BE, Round(CVT,1) as CV_TO_BE, CVT as ORG_CVT, CV as ORG_CV,"&_
    " Case When Round((SVT_VAT-CVT)/nullif(SVT_VAT,0)*100,1) is NULL then 0 else Round((SVT_VAT-CVT)/nullif(SVT_VAT,0)*100,1) END as Margin_To_be, (SV_VAT + SVT_VAT) as Total_VAT"&_
    " from (Select b.department_desc, Case When sum(cost_price_raised)/1000 is NULL then 0 else sum(cost_price_raised)/1000 End as CV, "&_
    " Case When sum(selling_value_raised_X_VAT)/1000 is NULL then 0 else sum(selling_value_raised_X_VAT)/1000 End as SV_VAT, "&_
    " Case When sum(selling_value_raised)/1000 is NULL then 0 else sum(selling_value_raised)/1000 End as SV,"&_
    " Case When sum(COST_PRICE_TO_BE_RAISED)/1000 is NULL then 0 else sum(COST_PRICE_TO_BE_RAISED)/1000 End as CVT,"&_
    " Case When sum(SELLING_VALUE_TO_BE_RSD_X_VAT)/1000 is NULL then 0 else sum(SELLING_VALUE_TO_BE_RSD_X_VAT)/1000 END as SVT_VAT,"&_
    " Case When sum(SELLING_VALUE_TO_BE_RAISED)/1000 is NULL then 0 else sum(SELLING_VALUE_TO_BE_RAISED)/1000 END as SVT"&_
    " from BI_HOME_ORDER_TOTALS a, nonfood_hierarchy b where a.sub_class_cd = b.sub_class_cd and a.sub_class_cd in (Select distinct Sub_class_CD from user_access_ref where User_ID = 'T-IY00') "&_
    " GROUP BY b.department_desc Order by b.department_desc))Order by 1",oCon

  2. #2
    Moderator
    Join Date
    Sep 2001
    Location
    Doncaster, UK
    Posts
    5,788
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0
    Queries that run in SQL Developer will need "tweaking" to work in VBScript, there are slight differences between the 2 syntaxes (or should that be Syntai)

    Here's a function I use to do global "Oracle SQL to VBScript SQL" changes

    Code:
     ' Changes Oracle SQL to VBScript SQL 
     ' By wrapping the SQL in " " &_
     'e.g. "Select &_"
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set f1 = FSO.OpenTextFile("D:\f1.txt", 1, false)
    Set f2 = FSO.OpenTextFile("D:\f2.txt",8,True)
    
    While f1.AtEndOfStream <> True
    	l1 = f1.ReadLine()
    	l2 = Chr(34)&l1&CHR(34)&" &_"
    	f2.WriteLine(l2)
    Wend
    
    f1.Close
    f2.Close
    Set FSO = Nothing
    Set f1 = Nothing
    Set f2 = Nothing

    Mark Smith.

  3. #3
    Member
    Join Date
    May 2011
    Location
    California, US
    Posts
    306
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0
    why not just take the code from excel - as you need not follow any syntax, just copy paste the code into the excel cell.
    But have to make sure few things while you do DB testing automation - in IDE the query may look good but when we fire the qry with ODBC we can see sum extra white lines appears.

    I had gone crazy when i initially started this using macros but down the line could see that it is only mostly because of extra blank lines.
    Also if you trying to do a MINUS operation - and you expect 0 records then take the count of the qry as we would get line break instead of a NULL value.....there are just examples that i had seen.

    Try to run your qry and debug - it will give you the pin points that you have take care for any Qry.
    --
    Vijay

 

 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Search Engine Optimisation provided by DragonByte SEO v2.0.36 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Resources saved on this page: MySQL 7.69%
vBulletin Optimisation provided by vB Optimise v2.6.4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.2.8 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominate (Lite) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 09:23 AM.

Copyright BetaSoft Inc.