Solved

Passing parameters in SSRS OpenQuery - Urgent

Posted on 2012-03-19
26
1,286 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 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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 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 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 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 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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

696 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