[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Passing parameters in SSRS OpenQuery - Urgent

Posted on 2012-03-19
26
Medium Priority
?
1,373 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
24 Comments
 
LVL 78

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 29

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 29

Accepted Solution

by:
sammySeltzer earned 2000 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 29

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 29

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 78

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 29

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 78

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 78

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 78

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 78

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 78

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 29

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

872 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