Unable to use delete statement in SQLRPG

pts.
Tags:
DELETE statement
Embedded SQL
RPG
SQL
SQLRPG
Hi I want to use this query delete from abc a where rrn(a) between 1 and 10 in an sqlrpg prog.I was getting error messages saying rrn keyword not allowed.Am not sure how to execute this in an embeddd sql program.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,

Have you tried the following :-

DELETE FROM ABC WHERE RRN BETWEEN 1 AND 10

Regards,

Martin Gilbert.

Discuss This Question: 15  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Am getting an error which says Keyword RRN not valid als if i use <1 it says < is invalid.
    0 pointsBadges:
    report
  • philpl1jb
    http://www.itjungle.com/mgo/mgo021903-story01.html The RRN function takes one argument, which is the table designator for the table that you want the relative record numbers from. Say I have a table of orders looks like your sql should be DELETE FROM ABC WHERE RRN(ABC) BETWEEN 1 AND 10 This should work when ABC is a physical file. Phil
    49,590 pointsBadges:
    report
  • ya it worked but am unable to compile my code.I need only delete operation to be performed on the file.So i declared the file in update mode.Then in c spec i used the embedded sql.Is there something i missed.a sample would help
    0 pointsBadges:
    report
  • BigKat
    if you are using SQL for the file access, why do you even HAVE an F spec for the file???
    7,935 pointsBadges:
    report
  • So fro my req which is just to delete the records in a file using SQL do you mean to say an F spec is not required???Just a C spec would do??
    0 pointsBadges:
    report
  • philpl1jb
    Right the F spec is required for native operations -- Setll, read, chain, delete, update SQL stuff opens it's own access path. Phil
    49,590 pointsBadges:
    report
  • So will my RPG with only the below code work C/EXEC SQL C+ DELETE FROM ABC WHERE RRN(ABC) BETWEEN 1 AND 10 C/END-EXEC
    0 pointsBadges:
    report
  • Gilly400
    Hi, That should work - the only other thing you need is a SETON LR or MOVE *ON to *INLR to indicate termination of your RPG program. Regards, Martin Gilbert.
    23,730 pointsBadges:
    report
  • I have used seton lr...but the program doesnt delete the records in file ABC but there is no compilation error.
    0 pointsBadges:
    report
  • philpl1jb
    Yes, you may not have record with RRN 1 - 10 Remember when you delete records the RRN's get reused. To see the records you were working with. .. Use interactive sql SELECT * FROM ABC WHERE RRN BETWEEN 1 AND 10 Phil
    49,590 pointsBadges:
    report
  • I did use the select statemnt and it had records between with rrn bet 1 and 10.But when is use the delete it isnt working.So am confused.
    0 pointsBadges:
    report
  • philpl1jb
    Perhaps it's commitment control. Add the Commit(*none) to your program before the delete -- see below.. Commitment control will restore records when a commit is not issued. Phil C/EXEC SQL C+ COMMIT(*NONE) C/END-EXEC C/EXEC SQL C+ DELETE FROM ABC WHERE RRN(ABC) BETWEEN 1 AND 10 C/END-EXEC
    49,590 pointsBadges:
    report
  • Gilly400
    Hi, Another idea is to start debug before running your program :- STRDBG UPDPRD(*YES) Run your program, then check your joblog to see if you get any error/warning messages. Regards, Martin Gilbert.
    23,730 pointsBadges:
    report
  • BigKat
    I use the following in almost every SQLRPGLE program I write
    c/Exec SQL
    c+ set option
    c+ datfmt = *ISO,
    c+ timfmt = *ISO,
    c+ naming = *SYS,
    c+ commit = *NONE,
    c+ closqlcsr = *ENDMOD,
    c+ dlyprp = *YES
    c/end-exec
    
    7,935 pointsBadges:
    report
  • ReshmaG
    while compiling the program you can give Commit(*None) in place of *CHG Thaks Reshma
    455 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following