Error:Right Parenthesis is required in Oracle Toad.

Question

Post reply

Error:Right Parenthesis is required in Oracle Toad.

swainsarada

Newbie

Points: 9
More actions
November 29, 2011 at 1:00 am

#390976

string str3 = "SELECT a.siebleloginid, a.employee, MAX (a.tierlevel) AS tier FROM"+
"((SELECT e.siebleloginid, e.lastname || ', ' || e.firstname AS employee,"+
"DECODE (e.siebleloginid, m.siebleloginid, 2, DECODE (e.username, z.usernamesup, 1, 0) )"+
"AS tierlevel FROM emadministration.tblemployeeinfo m, emadministration.tblemployeeinfo e,"+
"emadministration.tblzlistsectsup z WHERE m.siebleloginid ='OLEMSUP' AND"+
"z.usernameman = m.username AND e.section = z.section AND e.employmentstatus = 'Active')"+
"UNION (SELECT e.siebleloginid, e.lastname || ', ' || e.firstname AS employee, "+
"DECODE (e.siebleloginid, m.siebleloginid, 1, 0 ) AS tierlevel FROM "+
"emadministration.tblemployeeinfo m, emadministration.tblemployeeinfo e,"+
"emadministration.tblzlistsectsup z WHERE m.siebleloginid ='OLEMSUP'"+
"AND (z.usernamesup = m.username OR (z.usernametempsup = m.username "+
"AND z.tempsupenddate >= TRUNC (SYSDATE - 0.25)))"+
"AND e.section = z.section AND e.employmentstatus = 'Active') "+
"UNION (SELECT e.siebleloginid, e.lastname || ', ' || e.firstname AS employee,"+
"DECODE (zm.usernameman, NULL, DECODE (zs.usernamesup, NULL, 0, 1),2) AS tierlevel"+
"FROM emadministration.tblemployeeinfo e, emadministration.tblzlistsectsup zm,"+
"emadministration.tblzlistsectsup zs WHERE e.siebleloginid ='OLEMSUP' AND"+
"e.employmentstatus = 'Active'AND e.username = zm.usernameman(+) AND"+
"e.username = zs.usernamesup(+))) a GROUP BY a.siebleloginid, a.employee"+
"ORDER BY tier DESC, employee";

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32066 More actions · Answer 1

Could you use code tags?

AND"+

"z.usernameman

concatenates to andz.usernameman which is invalid

Trying to generate dynamic sql?

Lowell SSC Guru Points: 323504 More actions · Answer 2

I don't use TOAD, but i do some Oracle conversions a lot.

i thought ALL concatenation in Oracle must use double pipes...why do i see you trying to + strings togehter? is that a toad functionality?

should it be more like this?

string str3 = " SELECT a.siebleloginid, a.employee, MAX (a.tierlevel) AS tier FROM "

|| " ((SELECT e.siebleloginid, e.lastname || ', ' || e.firstname AS employee, "

|| " DECODE (e.siebleloginid, m.siebleloginid, 2, DECODE (e.username, z.usernamesup, 1, 0) ) "

|| " AS tierlevel FROM emadministration.tblemployeeinfo m, emadministration.tblemployeeinfo e, "

|| " emadministration.tblzlistsectsup z WHERE m.siebleloginid ='OLEMSUP' AND "

|| " z.usernameman = m.username AND e.section = z.section AND e.employmentstatus = 'Active') "

|| " UNION (SELECT e.siebleloginid, e.lastname || ', ' || e.firstname AS employee, "

|| " DECODE (e.siebleloginid, m.siebleloginid, 1, 0 ) AS tierlevel FROM "

|| " emadministration.tblemployeeinfo m, emadministration.tblemployeeinfo e, "

|| " emadministration.tblzlistsectsup z WHERE m.siebleloginid ='OLEMSUP' "

|| " AND (z.usernamesup = m.username OR (z.usernametempsup = m.username "

|| " AND z.tempsupenddate >= TRUNC (SYSDATE - 0.25))) "

|| " AND e.section = z.section AND e.employmentstatus = 'Active') "

|| " UNION (SELECT e.siebleloginid, e.lastname || ', ' || e.firstname AS employee, "

|| " DECODE (zm.usernameman, NULL, DECODE (zs.usernamesup, NULL, 0, 1),2) AS tierlevel "

|| " FROM emadministration.tblemployeeinfo e, emadministration.tblzlistsectsup zm, "

|| " emadministration.tblzlistsectsup zs WHERE e.siebleloginid ='OLEMSUP' AND "

|| " e.employmentstatus = 'Active'AND e.username = zm.usernameman(+) AND "

|| " e.username = zs.usernamesup(+))) a GROUP BY a.siebleloginid, a.employee "

|| " ORDER BY tier DESC, employee " ;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

Lowell (11/29/2011)
i thought ALL concatenation in Oracle must use double pipes

You are correct Lowell.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.