Re: [sqr-users] Challenging issue with Brio SQR in UNIX

[maddy <madhudvm@gmail.com>]

Hi,

I have tried all the various things mentioned in the chain of emails
but still have the same issue. The issue is with commit statement but
not sure why it is not working with ORDER BY clause in the SELECT
statement.

Please take a look at the attached SQR program and the cursor
information when compiled with -S flag.


Thanks!!
Maddy


Environment : Hyperion 8.5.0.0.0.566/IBM RS6000/AIX 5.2/ODBC DataDirect 5.1
Database : Sybase 12.5

On 10/9/07, George Jansen <GJANSEN@aflcio.org> wrote:
> It strikes me that an ORDER BY clause means sorting, sorting means the use of 
>temporary space, and this could be limited.
>
> As for the location, are you using Oracle? With Oracle you could under some 
>circumstances be connecting through a shared server (sorting in the shared 
>pool) and in others connecting through a dedicated server.
>
> >>> maddy <madhudvm@gmail.com> 10/4/2007 6:01 PM >>>
> Hi,
>
> We have a strange problem with all the Brio SQR Programs executing under
> UNIX environment.All these programs are working fine and giving data when
> executed in windows environment and giving no data in UNIX enviroment.
>
> We are creating a temp table and  inserting data in the SQR program but
> unable to Select the data from the same table and print it on to the
> report.However when the ORDER BY clause in the select statement is commented
> out, data is pulled on to the report.
>
> If I run the same program again without droping and creating the table we
> get the data from the table even with ORDER BY clause which is strange.
> How can a ORDER BY clause in the SELECT statement cause this kind of issue
> ? I can send the skeleton program if any of the experts can look into this
> issue.
>
> Environment: Hyperion 8.5 installed on UNIX (AIX)
>
> Thanks,
> Maddy
>
> _______________________________________________
> 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
>
Here was a non admissible message part of 'application/octet-stream'
MIME type. It has been automatically discarded before sending the
message to the list. 
Cursor Status:

 Cursor #1:
   SQL = commit transaction
 Compiles = 1
 Executes = 1
 Rows     = 0

 Cursor #2:
   SQL = if exists(select 1 from sysobjects where name ='CID30_JP'and
         type='U') begin drop table CID30_JP end
 Compiles = 1
 Executes = 1
 Rows     = 0

 Cursor #3:
   SQL = create table CID30_JP ( BLEI_BILL_LEVEL     char(1)  null,
         BLEI_LST_TL_DUE_DT  datetime null, BLEI_LAST_BILL_DTM  datetime
         null, BLEI_LST_BL_END_DT  datetime null, BLEI_SUSPND_BL_IND
         char(1)  null, GRGR_ID             char(8)  null, SGSG_ID
           char(4)  null, SBSB_ID             char(9)  null )
 Compiles = 1
 Executes = 1
 Rows     = 0

 Cursor #4:
   SQL = commit transaction
 Compiles = 1
 Executes = 1
 Rows     = 0

 Cursor #5:
   SQL = {call RPT..BERSP_EVENT_STEP_INSERT(?,?,?,?)}
 Compiles = 0
 Executes = 4
 Rows     = 0


 Cursor #6:
   SQL = insert into CID30_JP ( BLEI_BILL_LEVEL, BLEI_LST_TL_DUE_DT,
         BLEI_LAST_BILL_DTM, BLEI_LST_BL_END_DT, BLEI_SUSPND_BL_IND,
         GRGR_ID, SGSG_ID, SBSB_ID ) select 'I', BLEI.BLEI_LST_TL_DUE_DT,
         BLEI.BLEI_LAST_BILL_DTM, BLEI.BLEI_LST_BL_END_DT,
         BLEI.BLEI_SUSPND_BL_IND, GRGR.GRGR_ID, null , SBSB.SBSB_ID from
         fadfyin1..CMC_BLEI_ENTY_INFO      BLEI, fadfyin1..CMC_GRGR_GROUP
               GRGR, fadfyin1..CMC_SBSB_SUBSC          SBSB where
         BLEI.BLEI_BILL_LEVEL_CK  =   SBSB.SBSB_CK and SBSB.GRGR_CK
            =   GRGR.GRGR_CK and BLEI.BLEI_BILL_LEVEL     =   'I' and
         BLEI.BLEI_SUSPND_BL_IND in ('B','D','S') union select 'G',
         BLEI.BLEI_LST_TL_DUE_DT, BLEI.BLEI_LAST_BILL_DTM,
         BLEI.BLEI_LST_BL_END_DT, BLEI.BLEI_SUSPND_BL_IND, GRGR.GRGR_ID,
         SGSG.SGSG_ID, null from fadfyin1..CMC_BLEI_ENTY_INFO      BLEI,
         fadfyin1..CMC_GRGR_GROUP      GRGR, fadfyin1..CMC_SGSG_SUB_GROUP
         SGSG where BLEI.BLEI_BILL_LEVEL_CK =   SGSG.SGSG_CK and
         SGSG.GRGR_CK            =   GRGR.GRGR_CK and BLEI.BLEI_BILL_LEVEL
          =   'S' and BLEI.BLEI_SUSPND_BL_IND in ('B','D','S') union select
         'G', BLEI.BLEI_LST_TL_DUE_DT, BLEI.BLEI_LAST_BILL_DTM,
         BLEI.BLEI_LST_BL_END_DT, BLEI.BLEI_SUSPND_BL_IND, GRGR.GRGR_ID,
         null, null from fadfyin1..CMC_BLEI_ENTY_INFO      BLEI,
         fadfyin1..CMC_GRGR_GROUP          GRGR where
         BLEI.BLEI_BILL_LEVEL_CK =   GRGR.GRGR_CK and BLEI.BLEI_BILL_LEVEL
          =   'G' and BLEI.BLEI_SUSPND_BL_IND in ('B','D','S')
 Compiles = 1
 Executes = 1
 Rows     = 354

 Cursor #7:
   SQL = SELECT BLSU.BLEI_BILL_LEVEL, BLSU.BLEI_SUSPND_BL_IND, BLSU.GRGR_ID,
         BLSU.SGSG_ID, BLSU.SBSB_ID,
         convert(char(10),BLSU.BLEI_LST_TL_DUE_DT,101),
         convert(char(10),BLSU.BLEI_LAST_BILL_DTM,101),
         convert(char(10),BLSU.BLEI_LST_BL_END_DT,101)  from RPT..CID30_JP
           BLSU order by BLSU.BLEI_BILL_LEVEL, BLSU.BLEI_SUSPND_BL_IND,
         BLSU.GRGR_ID, BLSU.SGSG_ID, BLSU.SBSB_ID
 Compiles = 1
 Executes = 2
 Rows     = 354

 Cursor #8:
   SQL = drop table RPT..CID30_JP
 Compiles = 1
 Executes = 1
 Rows     = 0


SQR: End of Run.
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users