Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error:Right Parenthesis is required in Oracle Toad. Expand / Collapse
Author
Message
Posted Tuesday, November 29, 2011 1:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 20, 2013 5:42 AM
Points: 1, Visits: 3
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";
Post #1212966
Posted Tuesday, November 29, 2011 8:46 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:28 AM
Points: 1,400, Visits: 6,794
Could you use code tags?
AND"+
"z.usernameman
concatenates to andz.usernameman which is invalid
Trying to generate dynamic sql?
Post #1213328
Posted Tuesday, November 29, 2011 9:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:20 AM
Points: 12,927, Visits: 32,332
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1213341
Posted Saturday, December 3, 2011 4:06 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #1215905
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse