SPONSORS:






User Tag List

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

    Execute oracle query in QTP 11.5

    Hi All,

    I 'm trying to execute the below oracle query in QTP but giving me the error message " ORA-00911: invalid character".

    Here is the code.


    oCon.Open

    Set oRs = CreateObject("ADODB.RecordSet")

    oRs.Open "Select a\.Sales_fcst_Lower_TL, Sales_fcst_Upper_TL, FCST_LVL_Set, a\.no_of_weeks_fcst_tl,a\.sub_class_cd," _
    &"Channel, a\.Created_dt, class_cd, section_cd" _
    &"from governance_ref a, nonfood_hierarchy b" _
    &"where a\.sub_class_cd = b\.sub_class_cd and b\.sub_class_cd = 'SH17CC' and to_date(a\.created_dt, 'DD-MON-YY')='12-NOV-13'",oCon

    I 've used "\" to treat "." as a literal. And "_" ,"&" to treat it as continuation opion .Is this it right approach?

    Thank you for your replies.

  2. #2
    Member
    Join Date
    Apr 2012
    Location
    Chennai
    Posts
    137
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0
    you can put in note pad and execute the query by reading the text

    or

    try with the ascii code like char(39)

  3. #3
    Advanced Member
    Join Date
    May 2007
    Posts
    609
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)
    Total Downloaded
    0
    You should get rid of the \ characters. They aren't necessary.

    Edit: You should also put spaces at the end of each line. When you concatenate them, there won't be any space in between and that'll cause issues.
    Dennis Belew

  4. #4
    Member
    Join Date
    Jun 2008
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0
    Thank you for the replies. I 've tried executing the following query by inserting spaces at the end of each line. The below error message is got displayed.
    "The test runn cannot continue due to the syntax error.. Expected en

    oRs.Open "Select a.Sales_fcst_Lower_TL, Sales_fcst_Upper_TL, FCST_LVL_Set, a.no_of_weeks_fcst_tl,a.sub_class_cd,"_
    "Channel, a.Created_dt, class_cd, section_cd"_
    "from governance_ref a, nonfood_hierarchy b"_
    "where a.sub_class_cd = b.sub_class_cd and b.sub_class_cd = 'SH17CC' and to_date(a.created_dt,'DD-MON-YY')='12-NOV-13'",oCon

  5. #5
    Moderator
    Join Date
    Jul 2005
    Location
    Delhi
    Posts
    15,575
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)
    Total Downloaded
    0
    Where are the & signs for string concatenation?

  6. #6
    Member
    Join Date
    Jun 2008
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0
    Quote Originally Posted by tarunlalwani View Post
    Where are the & signs for string concatenation?
    Hi Tarun,

    the below is the code which I 've used contiunation option. but still no luck

    oRs.Open "Select a.Sales_fcst_Lower_TL, Sales_fcst_Upper_TL, FCST_LVL_Set, a.no_of_weeks_fcst_tl,a.sub_class_cd,"&_
    "Channel, a.Created_dt, class_cd, section_cd"&_
    "from governance_ref a, nonfood_hierarchy b"&_
    "where a.sub_class_cd = b.sub_class_cd and b.sub_class_cd = 'SH17CC' and to_date(a.created_dt,'DD-MON-YY')='12-NOV-13'",oCon.

    Error msg: "FROM keyword not found where execpected "

  7. #7
    Moderator
    Join Date
    Jul 2005
    Location
    Delhi
    Posts
    15,575
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)
    Total Downloaded
    0
    Add spaces. Your concatenated query will 'section_cdfrom' instead of 'section_cd from'

  8. #8
    Member
    Join Date
    Jun 2008
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0
    Quote Originally Posted by tarunlalwani View Post
    Add spaces. Your concatenated query will 'section_cdfrom' instead of 'section_cd from'
    Hi Tarun,

    After inserting the space I 'm not getting the keyword missing error. But now if I execute the query I m getting "ORA-01861: literal does not match format string" error.

    Below query I 've executed.

    oRs.Open "Select a.Sales_fcst_Lower_TL, Sales_fcst_Upper_TL, FCST_LVL_Set, a.no_of_weeks_fcst_tl,a.sub_class_cd,"&_
    "Channel, a.Created_dt, class_cd, section_cd"&_
    " from governance_ref a, nonfood_hierarchy b"&_
    " where a.sub_class_cd = b.sub_class_cd and b.sub_class_cd = 'SH17CC' and to_date(a.created_dt, 'DD-MON-YY')='12-NOV-13'",oCon

    If I execute the same with query till sub_class_cd='SH17CC', I could able to get the output but "and to_date(a.created_dt, 'DD-MON-YY')='12-NOV-13'",oCon" is not working.

    I 've tried replacing "(" to "\(" but still no luck

    Thank you for your help

  9. #9
    Member
    Join Date
    May 2011
    Location
    California, US
    Posts
    306
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0
    one more point to note...check the format of a.created_dt, whether it is really 'DD-MON-YY' , else need to change the format in to_date accordingly
    --
    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 10.00%
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 04:11 PM.

Copyright BetaSoft Inc.