[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index] [Date Index] [Thread Index]
[SQR-USERS Info] [SQRUG Home Page]
Re: [sqr-users] (SQR 1303) Error in SQL (perhapsmissing &name afterexpression)

Subject: Re: [sqr-users] (SQR 1303) Error in SQL (perhapsmissing &name afterexpression)
From: dbrawner@att.net
Date: Tue, 25 Mar 2008 00:06:56 +0000
Delivery-date: Mon, 24 Mar 2008 20:10:00 -0400
List-id: "This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions." <sqr-users.sqrug.org>
Try it without the ' around ('SESSIONID'),  SQR or SQL might be interpreting 
these as eacape characters.  I see in the error message "SQR 1303) Error in SQL 
(perhaps missing &name after expression): select USERENV('SESSIONID') From DUAL 
"  the &SESSION_ID is not being recognized...

Just a thought...

Dave

-------------- Original message from Marina Piatibratov <marinal@rogers.com>: 
-------------- 


> I see there is a left space in my email, though I don't see it in any 
> editor when I look into the code. 
> But even with the left space before "from" it doesn't fail in my local env. 
> Isn't it that select columns should strictly start from the first 
> column, but for the "from" and "where clause" it's not necessary? 
> Anyway the same code runs okay in users support env. 
> 
> --Marina 
> 
> Larry Roux wrote: 
> 
> >Some things to try: 
> > 
> >1. Did you try making sure that the From statement was left justified? 
> > 
> >2. Are you positive you do not really have this (the select in there 
> >again)? 
> >begin-select 
> >select 
> >USERENV('SESSIONID') &SESSION_ID 
> > From DUAL 
> >end-select 
> > 
> >3. Could the user id being used to run this not have access to be able 
> >to run the USERENV proc? 
> > 
> >Unfortunately, SQR error messages are rarely the answer. At best they 
> >guide you to where the error is, not what it is. 
> > 
> >-----Original Message----- 
> >From: sqr-users-bounces+lroux=syr.edu@sqrug.org 
> >[mailto:sqr-users-bounces+lroux=syr.edu@sqrug.org] On Behalf Of Marina 
> >Piatibratov 
> >Sent: Monday, March 24, 2008 1:27 PM 
> >To: This list is for discussion about the SQR database reporting 
> >languagefrom Hyperion Solutions. 
> >Subject: Re: [sqr-users] (SQR 1303) Error in SQL (perhaps missing &name 
> >afterexpression) 
> > 
> >Hi, 
> > 
> >User fixed test.sqr to be: 
> > 
> >begin-select 
> >USERENV('SESSIONID') &SESSION_ID 
> > From DUAL 
> >end-select 
> > 
> >and ran without -rt, but it still fails the same way. In their support 
> >env the same test.sqr runs fine. 
> >It's not smth new, I always used to get session id in this way. 
> >Something realted to the db settings? 
> > 
> >(SQR 1303) Error in SQL (perhaps missing &name after expression): 
> >select USERENV('SESSIONID') From DUAL 
> >(SQR 1304) Check SELECT columns, expressions and 'where' clause for 
> >syntax. 
> > 
> >SQR: Program Aborting. 
> > 
> >Cursor Status: 
> > 
> > Cursor #1: 
> > SQL = select USERENV('SESSIONID') From DUAL 
> > Compiles = 1 
> > Executes = 0 
> > Rows = 0 
> > 
> >In support: 
> >----------- 
> >Cursor Status: 
> > 
> > Cursor #1: 
> > SQL = select USERENV('SESSIONID') From DUAL 
> > Compiles = 1 
> > Executes = 1 
> > Rows = 1 
> > 
> > Cursor #2: 
> > SQL = select to_char(AUTO_ALLOC_PROCESS_TYPE) from STOCK_PARAM 
> > Compiles = 1 
> > Executes = 1 
> > Rows = 1 
> > 
> >Thanks,Marina 
> > 
> > 
> >Peter Burton wrote: 
> > 
> > 
> > 
> >>Marina, 
> >> 
> >>Have the user try running the .SQR file without the -RT (use .SQT) 
> >> 
> >> 
> >command line flag. 
> > 
> > 
> >>Peter 
> >> 
> >>-----Original Message----- 
> >>From: sqr-users-bounces+peter.burton=oracle.com@sqrug.org 
> >> 
> >> 
> >[mailto:sqr-users-bounces+peter.burton=oracle.com@sqrug.org] On Behalf 
> >Of Marina Piatibratov 
> > 
> > 
> >>Sent: Friday, March 21, 2008 8:11 PM 
> >>To: This list is for discussion about the SQR database reporting 
> >> 
> >> 
> >language from Hyperion Solutions. 
> > 
> > 
> >>Subject: Re: [sqr-users] (SQR 1303) Error in SQL (perhaps missing &name 
> >> 
> >> 
> >after expression) 
> > 
> > 
> >>Don, 
> >>Thanks for your quick response. 
> >> 
> >>You know, you're right. The version of test.sqr I sent to the user 
> >>contained "<" before From dual. I have no idea how it got there, but 
> >>that explains why it failed for them (for test.sqr). Unfortunately it 
> >>doesn't explain why the original sqr is not working there: it completes 
> >>w/o errors but all cursors bring zero rows (the db has the data) and 
> >>"select USERENV('SESSIONID') From DUAL" also brings zero rows. 
> >>We use -xcb -xmb -s -rt options in command line. 
> >>And it seems that for these options it skips the error and continues 
> >>working w/o selecting a single row. 
> >>I asked the users to run the test.sqr also with -xcb -xmb -s -rt 
> >> 
> >> 
> >options 
> > 
> > 
> >>and that's what I got: 
> >>3.err 
> >>Cursor Status: 
> >> 
> >> Cursor #1: 
> >> SQL = select USERENV('SESSIONID') From DUAL 
> >>Compiles = 1 
> >>Executes = 1 
> >>Rows = 0 
> >> 
> >>Cursor #2: 
> >> SQL = select to_char(AUTO_ALLOC_PROCESS_TYPE) from STOCK_PARAM 
> >>Compiles = 1 
> >>Executes = 1 
> >>Rows = 0 
> >> 
> >>The lis file: 
> >>Test beginning work 
> >>Sort Order : 
> >>Request Number : 
> >>Sequence Number: 0 
> >>AUTO_ALLOC_PROCESS_TYPE= 
> >>Test work completed 
> >> 
> >>--- It didn't bring the row from the db, but it does exist there: 
> >>SQL> select to_char(AUTO_ALLOC_PROCESS_TYPE) from STOCK_PARAM; 
> >> 
> >>TO_CHAR(AUTO_ALLOC_PROCESS_TYPE) 
> >>---------------------------------------- 
> >>2 
> >> 
> >>The result I got in 3.err/lis (for test.sqr) is just the same I got for 
> >>the original sqr that's why I thought I found the problem. 
> >>Of course the original sqr has: 
> >>begin-select 
> >>USERENV('SESSIONID') &SESSION_ID 
> >>From DUAL 
> >>end-select 
> >> 
> >>My next question is how it can execute "select USERENV('SESSIONID') 
> >>&SESSION_ID from dual" and bring zero rows. 
> >> 
> >>I can't run anything on their side that's why it's a bit difficult. :-\ 
> >> 
> >>Thanks,Marina 
> >> 
> >>---- 
> >>Don Mellen wrote: 
> >> 
> >> 
> >> 
> >> 
> >> 
> >>>Hmm. First suggestion, make sure the "From" starts in the first 
> >>> 
> >>> 
> >column with 
> > 
> > 
> >>>no spaces/tabs before it. 
> >>> 
> >>>On Fri, Mar 21, 2008 at 6:57 PM, Marina Piatibratov 
> >>> 
> >>> 
> > 
> > 
> > 
> >>>wrote: 
> >>> 
> >>> 
> >>> 
> >>> 
> >>> 
> >>> 
> >>> 
> >>>>Hello, 
> >>>> 
> >>>>I ran into the following problem: 
> >>>>The sqr fails with: 
> >>>>(SQR 1303) Error in SQL (perhaps missing &name after expression): 
> >>>>select USERENV('SESSIONID') From DUAL 
> >>>>(SQR 1304) Check SELECT columns, expressions and 'where' clause for 
> >>>>syntax. 
> >>>> on 
> >>>>begin-select 
> >>>>USERENV('SESSIONID') &SESSION_ID 
> >>>> 
> >>>> 
> >>>>From DUAL 
> >>> 
> >>> 
> >>>>end-select 
> >>>> 
> >>>>I created test.sqr (see code below) using the fragments from the 
> >>>>original sqr and asked the users to run it on their side. It failed 
> >>>> 
> >>>> 
> >with 
> > 
> > 
> >>>>the same error (see 1.err - for users below) . In my test env it 
> >>>>completes okay (see 1.err and 1.lis below). Just to note that 
> >>>> 
> >>>> 
> >original 
> > 
> > 
> >>>>sqr is running fine in production, the problem happens in the users 
> >>>>staging db and sqr code has not changed. 
> >>>> 
> >>>>Environment is the same (both for users and mine): 
> >>>>SQR version 4.0.3 
> >>>>Oracle 9.2.0.4.0 
> >>>>Windows Server 2003 Standard edition Version 5.2 
> >>>> 
> >>>>The command line: 
> >>>>D:\app\bin\sqr7w test.sqr /@ -s -e1.err -f1.lis 
> >>>> 
> >>>> 
> >'' 
> > 
> > 
> >>>>'' '' 'EST' ' 0' 
> >>>> 
> >>>>The test.sqr code: 
> >>>>begin-report 
> >>>>print 'Test beginning work' (+1,1) 
> >>>>do Input 
> >>>>print 'Test work completed' (+1,1) 
> >>>>end-report 
> >>>>!-------------------------------------------------------------------- 
> >>>> 
> >>>> 
> >----- 
> > 
> > 
> >>>>begin-procedure Input 
> >>>> input $SORT_ORDER ' Enter Sort Order ' 
> >>>> input $p_eng_request__t ' Enter Request Number ' 
> >>>> input $p_sequence_no ' Enter Sequence Number ' 
> >>>> move $p_sequence_no to #p_sequence_no 
> >>>>let $tmp = 'Sort Order : '||$sort_order 
> >>>>print $tmp (+1,1) 
> >>>>let $tmp = 'Request Number : '||$p_eng_request__t 
> >>>>print $tmp (+1,1) 
> >>>>let $tmp = 'Sequence Number: '||to_char(#p_sequence_no) 
> >>>>print $tmp (+1,1) 
> >>>> 
> >>>>begin-select 
> >>>>USERENV('SESSIONID') &SESSION_ID 
> >>>> 
> >>>> 
> >>>>From DUAL 
> >>> 
> >>> 
> >>>>end-select 
> >>>> 
> >>>>begin-select 
> >>>>to_char(AUTO_ALLOC_PROCESS_TYPE) &auto_alloc_process_type 
> >>>> 
> >>>> 
> >>>> 
> >>>> 
> >>>>from STOCK_PARAM 
> >>> 
> >>> 
> >>> 
> >>> 
> >>>>end-select 
> >>>> 
> >>>>let $tmp = 'AUTO_ALLOC_PROCESS_TYPE='||&auto_alloc_process_type 
> >>>>print $tmp (+1,1) 
> >>>>end-procedure Input 
> >>>> 
> >>>>================== 
> >>>> 
> >>>>Results at the users side (failed with error): 
> >>>>1.err : 
> >>>>(SQR 1303) Error in SQL (perhaps missing &name after expression): 
> >>>>select USERENV('SESSIONID') From DUAL 
> >>>>(SQR 1304) Check SELECT columns, expressions and 'where' clause for 
> >>>>syntax. 
> >>>> 
> >>>>SQR: Program Aborting. 
> >>>> 
> >>>>Cursor Status: 
> >>>> 
> >>>>Cursor #1: 
> >>>>SQL = select USERENV('SESSIONID') From DUAL 
> >>>>Compiles = 1 
> >>>>Executes = 0 
> >>>>Rows = 0 
> >>>> 
> >>>> 
> >>>> 
> >>>>From SQL Plus from user's db: 
> >>> 
> >>> 
> >>>>SQL> select USERENV('SESSIONID') from dual; 
> >>>> 
> >>>>USERENV('SESSIONID') 
> >>>>-------------------- 
> >>>> 27731 
> >>>> 
> >>>>Results in my test env: 
> >>>>================ 
> >>>>1.err: 
> >>>>Cursor Status: 
> >>>> 
> >>>>Cursor #1: 
> >>>>SQL = select USERENV('SESSIONID') From DUAL 
> >>>>Compiles = 1 
> >>>>Executes = 1 
> >>>>Rows = 1 
> >>>> 
> >>>>Cursor #2: 
> >>>>SQL = select to_char(AUTO_ALLOC_PROCESS_TYPE) from STOCK_PARAM 
> >>>>Compiles = 1 
> >>>>Executes = 1 
> >>>>Rows = 1 
> >>>> 
> >>>>1.lis: 
> >>>>Test beginning work 
> >>>>Sort Order : 
> >>>>Request Number : 
> >>>>Sequence Number: 0 
> >>>>AUTO_ALLOC_PROCESS_TYPE=2 
> >>>>Test work completed 
> >>>> 
> >>>>==================== 
> >>>>Thanks for your help 
> >>>>--Marina 
> >>>> 
> >>>>_______________________________________________ 
> >>>>sqr-users mailing list 
> >>>>sqr-users@sqrug.org 
> >>>>http://www.sqrug.org/mailman/listinfo/sqr-users 
> >>>> 
> >>>> 
> >>>> 
> >>>> 
> >>>> 
> >>>> 
> >>>> 
> >>> 
> >>> 
> >>> 
> >>> 
> >>> 
> >>_______________________________________________ 
> >>sqr-users mailing list 
> >>sqr-users@sqrug.org 
> >>http://www.sqrug.org/mailman/listinfo/sqr-users 
> >> 
> >> 
> >>_______________________________________________ 
> >>sqr-users mailing list 
> >>sqr-users@sqrug.org 
> >>http://www.sqrug.org/mailman/listinfo/sqr-users 
> >> 
> >> 
> >> 
> >> 
> >> 
> > 
> >_______________________________________________ 
> >sqr-users mailing list 
> >sqr-users@sqrug.org 
> >http://www.sqrug.org/mailman/listinfo/sqr-users 
> > 
> >_______________________________________________ 
> >sqr-users mailing list 
> >sqr-users@sqrug.org 
> >http://www.sqrug.org/mailman/listinfo/sqr-users 
> > 
> > 
> > 
> 
> _______________________________________________ 
> sqr-users mailing list 
> sqr-users@sqrug.org 
> http://www.sqrug.org/mailman/listinfo/sqr-users 

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
Prev by Date: [sqr-users] SQR Character Set
Next by Date: [sqr-users] Calling a DOS command .bat file from UNIX
Previous by thread: [sqr-users] SQR Character Set
Next by thread: [sqr-users] Calling a DOS command .bat file from UNIX
Indexes: [ Thread] [ Author] [ Date]
SQR-USERS List and Archive Info
SQRUG Home Page