[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index] [Date Index] [Thread Index]
[SQR-USERS Info] [SQRUG Home Page]
RE: [sqr-users] [Microsoft][ODBC driver forOracle]CannotuseKeyset-driven cursor on join, with distinct clause, union,intersector minus or on read only result set

Subject: RE: [sqr-users] [Microsoft][ODBC driver forOracle]CannotuseKeyset-driven cursor on join, with distinct clause, union,intersector minus or on read only result set
From: "the dragon" <ceprn@hotmail.com>
Date: Fri, 31 Aug 2007 09:00:14 -0500
Bcc:
Delivery-date: Fri, 31 Aug 2007 10:04:47 -0400
In-reply-to: <D59898AE54D95941BCB81E315F29D87C017A92B2@Xav-Mail.xavient.com>
List-id: "This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions." <sqr-users.sqrug.org>
Ok, a couple of things... here is how I'd write the code you have, just for 
testing...
DISCLAIMER... I've been doing Admin work for almost 7 years exclusively, so 
my sqr coding is a little rusty - you may need to massage this a little.


Begin-select on-error=write_Error

CSG_OP_ID                         &CSG_OP_ID
to_char(HIRE_DATE,'yyyy-mm-dd')   &HIRE_DATE
to_char(TERM_DATE,'yyyy-mm-dd')   &TERM_DATE
LOCATION_NAME                     &LOCATION_NAME

   let $CSG_OP_ID     = rtrim(ltrim(&CSG_OP_ID, ' '), ' ')
   let $HIRE_DATE     = &HIRE_DATE
   let $TERM_DATE     = &TERM_DATE
   let $LOCATION_NAME = rtrim(ltrim(&LOCATION_NAME, ' '), ' ')

   #ifdef debuga
     do show_variables
   #end-if
   do write_file

from xxhr_employee_details
where csc_department in ('Y', 'P')
#ifdef debuga
  and rownum <51
#end-if

End-select

begin-procedure  show_variables

let $HIRE_DATE_DISP = edit($HIRE_DATE, 'yyyy-mm-dd')
let $TERM_DATE_DISP = edit($TERM_DATE, 'yyyy-mm-dd')

  show 'Operator ID = |' || $CSG_OP_ID      || '|'
  show 'Hire Date   = |' || $HIRE_DATE_DISP || '|'
  show 'Term Date   = |' || $TERM DATE_DISP || '|'
  show 'Location    = |' || $LOCATION_NAME  || '|'

end-procedure

begin-procedure  write_file

  write 1 from  $CSG_OP_ID
                '|'
                $HIRE_DATE
                '|'
                $TERM_DATE
                '|'
                $LOCATION_NAME
                '|'

end-procedure

Run your code with debuga turned on!!!

This should give you an idea of what is going on.  PS, since CSG_OP_ID can 
never be null you don't need this "and csg_op_id is not null".  Also, don't 
use commas after the print variable designation.

Sorry, at work, and can't play with this any more.

peace,
clark 'the dragon" willis


PSA: Salary <> Slavery. If you earn a salary, your employer is renting your 
services for 40 hours a week, not purchasing your soul. Your time is the 
only real finite asset that you have, and once used it can never be 
recovered, so don't waste it by giving it away.

I work to live; I don't live to work.

"Time is the coin of your life. It is the only coin you have, and only you 
can determine how it will be spent. Be careful lest you let other people 
spend it for you." -- Carl Sandburg (1878 - 1967)

It is impossible to defeat an ignorant man in argument. -- William G. McAdoo

Religion is regarded by the common people as true, by the wise as false, and 
by the rulers as useful. -- Seneca

"I distrust those people who know so well what God wants them to do because 
I notice it always coincides with their own desires." - Susan B. Anthony




----Original Message Follows----

Hi Peter



As per your suggestion I have changed the driver now it did not given
that error,

  but giving another error in one more query which was running properly
with normal ODBC driver.



That query is following

Begin-select on-error=write_Error

CSG_OP_ID                                  &CSG_OP_ID,

to_char(HIRE_DATE,'yyyy-mm-dd')            &HIRE_DATE,

to_char(TERM_DATE,'yyyy-mm-dd')            &TERM_DATE,

LOCATION_NAME                              &LOCATION_NAME

  write 1 from  &CSG_OP_ID '|' &HIRE_DATE '|' &TERM_DATE '|'
&LOCATION_NAME '|'

from xxhr_employee_details

where (csc_department = 'Y' or csc_department = 'P')

and csg_op_id is not null and rownum <51

End-select



In this query I am reading from a table and writing into a flat file.

I am selecting from the following table

CREATE TABLE XXHR_EMPLOYEE_DETAILS_HS

(

   CSG_OP_ID      VARCHAR2(30)              NOT NULL,

   HIRE_DATE      DATE,

   TERM_DATE      DATE,

   LOCATION_NAME  VARCHAR2(60))



In data I have null value in TERM_DATE column in 2 records for which as
per my understanding it is giving following

Error     -->[DataDirect][ODBC Oracle Wire Protocol driver]Character,
decimal, and binary parameters cannot have a precision       of zero.
Error in parameter 3.

  And for remaining records it is giving the following error

[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.

While nobody changed the data type for parameter 4 which is "
iLOCATION_NAME".Its structure is same as earlier



If I mark the comment in comment for the following line as

!write 1 from  &CSG_OP_ID '|' &HIRE_DATE '|' &TERM_DATE '|'
&LOCATION_NAME '|'

Then it does not give any error .But I have to write it in the text file


Please provide some solution for this problem







Following are the collective errors for the above query







[DataDirect][ODBC Oracle Wire Protocol driver]Character, decimal, and
binary parameters cannot have a precision of zero.  Error in parameter
3.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Character, decimal, and
binary parameters cannot have a precision of zero.  Error in parameter
3.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.



[DataDirect][ODBC Oracle Wire Protocol driver]Data type for parameter 4
has changed since first SQLExecute call.





Regards,

Harvinder Singh Narang



Xavient Information Systems



C-124, Hosiery Complex



Phase - II, Noida



Tel. +91-120 436 2500 Extn. XXX



Fax +91-120 2461970



Mobile 9899328393



Visit us: www.xavient.com



E-mail: hsnarang@xavient.com















========================================================================
==================================

This communication & accompanying documents ("this e-mail") contains
confidential and/or privileged information for exclusive use of the
individual to whom it is addressed. If you are not the intended
recipient, please immediately notify the company & delete this e-mail.
Any unauthorized use or disclosure of this e-mail is strictly
prohibited. Representations in this e-mail are subject to contract. As
an e-mail user please be cautious of the technical & other
vulnerabilities of the internet which may result in malicious and/or
unauthorized access to / use / alteration of e-mails/e-mail IDs. Thank
you.



========================================================================
==================================









-----Original Message-----
From: sqr-users-bounces+hsnarang=xavient.com@sqrug.org
[mailto:sqr-users-bounces+hsnarang=xavient.com@sqrug.org] On Behalf Of
Peter Burton
Sent: Friday, August 31, 2007 2:38 PM
To: This list is for discussion about the SQR database reporting
languagefrom Hyperion Solutions.
Subject: RE: [sqr-users] [Microsoft][ODBC driver for Oracle]Cannotuse
Keyset-driven cursor on join, with distinct clause, union, intersector
minus or on read only result set



Harvinder,



Yes ... Use the ODBC drivers supplied with the SQR product rather than
the Micosoft Oracle ODBC driver.



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 Harvinder Singh Narang

Sent: Friday, August 31, 2007 3:01 AM

To: sqr-users@sqrug.org

Subject: [sqr-users] [Microsoft][ODBC driver for Oracle]Cannot use
Keyset-driven cursor on join, with distinct clause, union, intersect or
minus or on read only result set



Hi







I am running a following query







SELECT DISTINCT AL1.SUB_ACCT_NO_WOC,AL1.LS_CHG_DTE_WOC FROM



WOC_BASE_COMP AL1, WOS_SERV_CODE AL2



WHERE AL1.SYS_WOC=AL2.SYS_WOS  AND



AL2.SERV_NET_CHG_WOS=1 AND



AL2.SERV_CDE_WOS='RT' AND



AL1.LS_CHG_DTE_WOC between sysdate-8 and sysdate-2;











Through SQR which is Giving the following error



[Microsoft][ODBC driver for Oracle]Cannot use Keyset-driven cursor on

join, with distinct clause, union, intersect or minus or on read only

result set



My SQR Version is 8.3



My Oracle version is 9.2



I am using ODBC Driver version 5.75.1117.00 for connecting to Oracle.







Can any one provide me a solution for this?











Regards,



Harvinder Singh Narang



Xavient Information Systems



C-124, Hosiery Complex



Phase - II, Noida



Tel. +91-120 436 2500 Extn. XXX



Fax +91-120 2461970



Mobile 9899328393



Visit us: www.xavient.com



E-mail: hsnarang@xavient.com















========================================================================

==================================

This communication & accompanying documents ("this e-mail") contains

confidential and/or privileged information for exclusive use of the

individual to whom it is addressed. If you are not the intended

recipient, please immediately notify the company & delete this e-mail.

Any unauthorized use or disclosure of this e-mail is strictly

prohibited. Representations in this e-mail are subject to contract. As

an e-mail user please be cautious of the technical & other

vulnerabilities of the internet which may result in malicious and/or

unauthorized access to / use / alteration of e-mails/e-mail IDs. Thank

you.



========================================================================

==================================







_______________________________________________

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

_________________________________________________________________
Learn.Laugh.Share. Reallivemoms is right place! 
http://www.reallivemoms.com?ocid=TXT_TAGHM&loc=us


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
References:
- RE: [sqr-users] [Microsoft][ODBC driver forOracle]Cannotuse Keyset-driven cursor on join,with distinct clause, union,intersector minus or on read only result set
  - From: "Harvinder Singh Narang" <hsnarang@xavient.com>