Author Topic: The query specification is incorrect  (Read 5191 times)

Offline krishna1478@gmail.com

  • Associate
  • **
  • Join Date: Aug 2010
  • Posts: 3
  • Forum Citizenship: +0/-0
The query specification is incorrect
« on: 09 Sep 2010 12:33:46 am »
Hi,
when i validate the SQL in Cognos 8.1 Report studio(from Query explorer) for updating the records in SQL server 2005 it is showing the error like Query specification is incorrect(RQP-DEF-0192 At least one expression in the Select clause is missing the AS clause to make it a proper alias).
Hear,
1). I am directly connecting to the data base (Framework manager is not used)
2) In SQL server 2005 i wrote a stored proceder for updating the record

In cognos in Report studio Query explorer i wrote,

{ exec addemp1 #prompt('empid','integer')#,#prompt('Emp_name','varchar(30)')# }

to get the records and create the prompts.


And Stored procedure is

CREATE PROCEDURE empupdate
   (@empid int output,@Emp_name nvarchar(50) output)
AS
BEGIN
   
    update empdetails set Emp_name=@Emp_name where empid=@empid
    select empid=@empid,Emp_name=@Emp_name
END
GO

Please kindly help to solve this error ASAP.

Thanks,
Krishna
 

Offline krishna1478@gmail.com

  • Associate
  • **
  • Join Date: Aug 2010
  • Posts: 3
  • Forum Citizenship: +0/-0
Re: The query specification is incorrect
« Reply #1 on: 09 Sep 2010 12:46:57 am »
Hi,
This is again Krishna.

The Stored procedure Name and is empupdate and same name i used in SQL not addemp1.

Thanks,
krishna

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 9,988
  • Forum Citizenship: +600/-9
  • Cognos Software Muppet
Re: The query specification is incorrect
« Reply #2 on: 09 Sep 2010 04:51:01 am »
Hi,

Just to clarify - you are calling an update stored procedure from within a Report Studio report by hard-coding the call in SQL?

The report is expecting a result set to be returned in a projection list, with each returned item of data explicitly named.  You could try modifying the SQL to call the SP as a derived table within a query providing the projection list - something along the lines of

{Select SP.empid AS empid,
           SP.Emp_name as Emp_name
 FROM (exec addemp1 #prompt('empid','integer')#,#prompt('Emp_name','varchar(30)')# ) SP) }

Not sure whether this would work, but possibly worth a try?

MF.
Meep!

Offline krishna1478@gmail.com

  • Associate
  • **
  • Join Date: Aug 2010
  • Posts: 3
  • Forum Citizenship: +0/-0
Re: The query specification is incorrect
« Reply #3 on: 13 Sep 2010 07:41:59 am »
Thanks for you are replay...

But that is not my requirement.

I want to update data in database from a Report by running the stored procedure.

Thanks,
Krishna

Offline Nani16904

  • Full Member
  • ***
  • Join Date: May 2017
  • Posts: 5
  • Forum Citizenship: +0/-0
Re: The query specification is incorrect
« Reply #4 on: 30 May 2017 10:16:37 am »
Hi Krishna,

Did you get a solution for the problem you are facing here. i am facing same problem now.

 


       
Twittear