[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Passing parameters in SSRS OpenQuery - Urgent

Posted on 2012-03-19
26
Medium Priority
?
1,353 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 7
  • 6
26 Comments
 
LVL 77

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 77

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 77

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 77

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 77

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 77

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 77

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
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.

656 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