Solved

Passing parameters in SSRS OpenQuery - Urgent

Posted on 2012-03-19
26
1,257 Views
Last Modified: 2012-05-31
Hi ,

I am trying to pass parameters in SSRS  Opnquery , it's throwing error : incoeerect syntax near 'MM'

Following is the query......

DECLARE @TSQL varchar(8000)
   
SELECT @TSQL = 'SELECT * FROM Openquery (x , ''Select ColorDate,StartOperatorID,equipmentname,colorcode, trim(round(avg(speed),2)) AS AvgSpeed from
(SELECT  TO_CHAR(ENDDATETIME , '''MM/DD/YY''') AS ColorDate
        ,STARTOPERATORID
       ,trim(EQUIPMENTNAME) AS EquipmentName
       ,TRIM(COLORCODE) AS COLORCODE ,
       TRIM(sum(ROUND((ACTUALLENGTH)/((ENDDATETIME-STARTDATETIME))/86400,2))) AS Speed
       ,Round(0.3* ((ENDDATETIME - STARTDATETIME)*86400
                           - ( sqrt(power((ENDDATETIME - STARTDATETIME)*86400,2)  - ((6.67*ActualLength))))),2) AS RunSpeed
             FROM kgf.SEGMENT
       WHERE CURESAMPLE = '''False''' AND ACTUALLENGTH >4000 AND
      Trim(COLORCODE)> 0  AND ((ENDDATETIME-STARTDATETIME)*1440)>1
 AND  ENDDATETIME is NOT NULL AND ENDDATETIME >= ''''' + @StartDate + ''''' And ENDDATETIME < ''''' + @EndDate + '''''
  GROUP BY ENDDATETIME
          ,STARTOPERATORID
         ,EQUIPMENTNAME
         ,ROUND((ENDDATETIME-STARTDATETIME)*1440 , 2)
         ,trim(COLORCODE)
         ORDER BY TO_CHAR(ENDDATETIME , ' ' 'MM/DD/YY' ' ')
       ,STARTOPERATORID
       ,EQUIPMENTNAME
      ) a
       group by ColorDate,StartOperatorID,equipmentname,colorcode       ORDER BY ColorDate,StartOperatorID,equipmentname,colorcode' ') c
INNER JOIN
OpenQuery(y , ' 'SELECT OperatorID , Name As Operator FROM PHD_USER.OPID_NAMESVIEW ' ')  h
on c.STARTOPERATORID = h.OperatorID'

SELECT @TSQL

EXEC (@TSQL)
0
Comment
Question by:CodingSucks
  • 11
  • 7
  • 6
26 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37738018
Use two to get one.

Try:
...
ORDER BY TO_CHAR(ENDDATETIME , ' 'MM/DD/YY' ' )
...
0
 

Author Comment

by:CodingSucks
ID: 37738042
Hi Slightwv,

Thanks for quick response..i tried what you suggested still same message.

PN
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 37738045
Are you using sql server or oracle syntax?

If SQL Server, I don't think you can use TO_CHAR.

Also, your parameters should :param for instance :EndDate, not  @EndDate  or  @startDate
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:CodingSucks
ID: 37738104
Sammy,

I am uisng oracle syntax...when i run the query it prompts to input parameters, once i enter parameters it's throwing error "Incorrect Syntax near 'MM' "

Thanks,
PN.
0
 
LVL 28

Accepted Solution

by:
sammySeltzer earned 500 total points
ID: 37738110
Oops, sorry this is OpenQuery.

Ignore everything I said and try this instead:

(SELECT  TO_CHAR(ENDDATETIME , ''''MM/DD/YY'''') AS ColorDate
0
 

Author Comment

by:CodingSucks
ID: 37738140
Sammy,

with TO_CHAR(ENDDATETIME , ''''MM/DD/YY'''')    it's new error

An error occurred while preparing the query for execution against OLE DB provider "OraOLEDB.Oracle" for linked server "x".
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 37738161
There is got to be more error messages than that, no?
0
 

Author Comment

by:CodingSucks
ID: 37738184
its showing the whole query in error message..
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 37738239
Copy and paste the entire error with code. We need more details from the error message.
0
 

Author Comment

by:CodingSucks
ID: 37738691
An error occurred while preparing the query "Select ColorDate,StartOperatorID,equipmentname,colorcode, trim(round(avg(speed),2)) AS AvgSpeed from
(SELECT  TO_CHAR(ENDDATETIME , 'MM/DD/YY') AS ColorDate
        ,STARTOPERATORID
       ,trim(EQUIPMENTNAME) AS EquipmentName
       ,TRIM(COLORCODE) AS COLORCODE ,
       TRIM(sum(ROUND((ACTUALLENGTH)/((ENDDATETIME-STARTDATETIME))/86400,2))) AS Speed
       ,Round((0.3)* ((ENDDATETIME - STARTDATETIME)*86400
                           - ( sqrt(power((ENDDATETIME - STARTDATETIME)*86400,2)  - (((6.67)*ActualLength))))),2) AS RunSpeed
         FROM OLS.COLORSEGMENT
       WHERE CURESAMPLE = 'False' AND ACTUALLENGTH >4000 AND
      Trim(COLORCODE)> 0  AND ((ENDDATETIME-STARTDATETIME)*1440)>1
 AND  ENDDATETIME is NOT NULL AND ENDDATETIME >= '03/12/12' And ENDDATETIME < '03/13/12'
  GROUP BY ENDDATETIME
          ,STARTOPERATORID
         ,EQUIPMENTNAME
         ,ROUND((ENDDATETIME-STARTDATETIME)*1440 , 2)
         ,trim(COLORCODE)
         ORDER BY TO_CHAR(ENDDATETIME , 'MM/DD/YY')
       ,STARTOPERATORID
       ,EQUIPMENTNAME
       ,COLORCODE ) a
       group by ColorDate,StartOperatorID,equipmentname,colorcode,color
       ORDER BY ColorDate,StartOperatorID,equipmentname,colorcode,color " for execution against OLE DB provider "OraOLEDB.Oracle" for linked server "XYZ".
0
 

Author Comment

by:CodingSucks
ID: 37738748
looks like its working ....but i have issue with group by

following is the query

Select ColorDate,StartOperatorID,equipmentname,colorcode, trim(round(avg(speed),2)) AS AvgSpeed  , trim(round(avg(runspeed),2)) AS NormSpeed from
(SELECT  TO_CHAR(ENDDATETIME , 'MM/DD/YY') AS ColorDate
        ,STARTOPERATORID
       ,trim(EQUIPMENTNAME) AS EquipmentName
       ,TRIM(COLORCODE) AS COLORCODE ,
       TRIM(sum(ROUND((ACTUALLENGTH)/((ENDDATETIME-STARTDATETIME))/86400,2))) AS Speed
       ,TRIM(Sum(Round((0.3)* ((ENDDATETIME - STARTDATETIME)*86400
                           - ( sqrt(power((ENDDATETIME - STARTDATETIME)*86400,2)  - (((6.67)*ActualLength))))),2))) AS RunSpeed
         FROM OLS.COLORSEGMENT
       WHERE CURESAMPLE = 'False' AND ACTUALLENGTH >4000 AND
      Trim(COLORCODE)> 0  AND ((ENDDATETIME-STARTDATETIME)*1440)>1
 AND  ENDDATETIME is NOT NULL AND ENDDATETIME >= '03/13/12' And ENDDATETIME < '03/14/12'
  GROUP BY ENDDATETIME
          ,STARTOPERATORID
         ,EQUIPMENTNAME
         ,ROUND((ENDDATETIME-STARTDATETIME)*1440 , 2)
         ,trim(COLORCODE)
         ORDER BY TO_CHAR(ENDDATETIME , 'MM/DD/YY')
       ,STARTOPERATORID
       ,EQUIPMENTNAME
       ,COLORCODE)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37738768
>>but i have issue with group by

you have to group by all non-aggregate columns in your outer query:

...
 ,EQUIPMENTNAME
       ,COLORCODE)
group by
ColorDate,StartOperatorID,equipmentname,colorcode
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 37738794
Well, this is a different code from what you showed earlier.
0
 

Author Comment

by:CodingSucks
ID: 37738820
I have modified the query and it looks as below....still having issue with group by

Select ColorDate,StartOperatorID,equipmentname,colorcode, trim(round(avg(speed),2)) AS AvgSpeed ,  trim(round(avg(runspeed),2)) AS NormSpeed  from
(SELECT  TO_CHAR(ENDDATETIME , 'MM/DD/YY') AS ColorDate
        ,STARTOPERATORID
       ,trim(EQUIPMENTNAME) AS EquipmentName
       ,TRIM(COLORCODE) AS COLORCODE ,
       TRIM(sum(ROUND((ACTUALLENGTH)/((ENDDATETIME-STARTDATETIME))/86400,2))) AS Speed
       ,Round((0.3)* ((ENDDATETIME - STARTDATETIME)*86400
                           - ( sqrt(power((ENDDATETIME - STARTDATETIME)*86400,2)  - (((6.67)*ActualLength))))),2) AS RunSpeed
         FROM OLS.COLORSEGMENT
       WHERE CURESAMPLE = 'False' AND ACTUALLENGTH >4000 AND
      Trim(COLORCODE)> 0  AND ((ENDDATETIME-STARTDATETIME)*1440)>1
 AND  ENDDATETIME is NOT NULL AND ENDDATETIME >= '03/12/12' And ENDDATETIME < '03/13/12'
  GROUP BY ENDDATETIME
          ,STARTOPERATORID
         ,EQUIPMENTNAME
         ,ROUND((ENDDATETIME-STARTDATETIME)*1440 , 2)
         ,trim(COLORCODE)
         ORDER BY TO_CHAR(ENDDATETIME , 'MM/DD/YY')
       ,STARTOPERATORID
       ,EQUIPMENTNAME
       ,COLORCODE ) a
       group by ColorDate,StartOperatorID,equipmentname,colorcode
       ORDER BY ColorDate,StartOperatorID,equipmentname,colorcode
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37738838
>>still having issue with group by

Error? Not producing the correct results?  what?
0
 

Author Comment

by:CodingSucks
ID: 37738859
ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
0
 

Author Comment

by:CodingSucks
ID: 37742194
I've requested that this question be closed as follows:

Accepted answer: 0 points for CodingSucks's comment #37738140

for the following reason:

It worked as expected
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37738884
If http:#a37738140 was the final solution then I would expect you to accept sammySeltzer's post http:#a37738110
0
 

Author Comment

by:CodingSucks
ID: 37739431
Following is the final Work Solution.....

DECLARE @TSQL varchar(8000)
     
 SELECT @TSQL = 'SELECT * FROM Openquery (server, ''Select ColorDate,StartOperatorID,equipmentname,colorcode,trim(round(avg(speed),2)) AS AvgSpeed ,  trim(round(avg(runspeed),2)) AS NormSpeed  from
(SELECT  TO_CHAR(ENDDATETIME , ''''MM/DD/YYYY'''') AS ColorDate
        ,STARTOPERATORID
       ,trim(EQUIPMENTNAME) AS EquipmentName
       ,TRIM(COLORCODE) AS COLORCODE ,

       TRIM(sum(ROUND((ACTUALLENGTH)/((ENDDATETIME-STARTDATETIME))/86400,2))) AS Speed
       ,trim(sum(Round((0.3)* ((ENDDATETIME - STARTDATETIME)*86400
                           - ( sqrt(power((ENDDATETIME - STARTDATETIME)*86400,2)  - (((6.67)*ActualLength))))),2))) AS RunSpeed
         FROM OLS.COLORSEGMENT
       WHERE CURESAMPLE = ''''False'''' AND ACTUALLENGTH >4000 AND
      Trim(COLORCODE)> 0  AND ((ENDDATETIME-STARTDATETIME)*1440)>1
      AND (ENDDATETIME - STARTDATETIME)*86400  > sqrt(power((ENDDATETIME - STARTDATETIME)*86400,2)  - (((6.67)*(ActualLength)))) AND
           power((ENDDATETIME - STARTDATETIME)*86400,2) > (6.67)*(ActualLength)
 AND  ENDDATETIME is NOT NULL AND TO_CHAR(ENDDATETIME , ''''MM/DD/YYYY'''')>= ''''' + @StartDate + ''''' And TO_CHAR(ENDDATETIME , ''''MM/DD/YYYY'''')< ''''' + @EndDate +'''''
  GROUP BY ENDDATETIME
          ,STARTOPERATORID
         ,EQUIPMENTNAME
         ,ROUND((ENDDATETIME-STARTDATETIME)*1440 , 2)
         ,trim(COLORCODE)
         ORDER BY TO_CHAR(ENDDATETIME , ''''MM/DD/YYYY'''')
       ,STARTOPERATORID
       ,EQUIPMENTNAME
       ,COLORCODE)
       group by ColorDate,StartOperatorID,equipmentname,colorcode
       ORDER BY ColorDate,StartOperatorID,equipmentname,colorcode '') c
INNER JOIN
OpenQuery(server1 , ''SELECT OperatorID , Name As Operator FROM PHD_USER.OPID_NAMESVIEW'')  h
on c.STARTOPERATORID = h.OperatorID
INNER JOIN
OpenQuery(server1 , ''SELECT DISTINCT COLORCODE AS cCode , COLORNAME As Color FROM PHD_USER.COLORINGVIEW WHERE COLORNAME is NOT NULL'')  n
on c.colorcode = n.cCode'


EXEC (@TSQL)

Many thanks to Sammy and SlightWv  :)

PN.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37739462
Not sure that I helped much but it appears the main 'fix' was sammySeltzer's post http:#a37738110 that used 4 single quotes.

Please accept that post as the solution and not yours in http:#a37738140
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37742195
Since you have not taken my suggestion, I'll involve a Moderator.
0
 

Author Comment

by:CodingSucks
ID: 37742326
different answer
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37742345
>>different answer

Didn't the 4 single quotes not solve the original problem of "incoeerect syntax near 'MM'
"
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 37742459
CodingSucks, we still haven't seen anything you have done differently from the suggestions you were given.

If I were you, I would go ahead and accept the answer.

There is always another time you might need help.

Just bear this in mind as you argue over semantics.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question