CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2012
    Posts
    2

    Referring to Parameter in Crystal Reports SQL

    Hi,

    I've been given the task of converting some reports from Access to Crystal Reports, I've used Crystal before but just for building basic reports, but to make it quicker I wanted to convert the SQL from Access rather than go through the interface and drag fields over etc to replicate the reports. The characters used etc are a bit different but I think eventually I've got it mostly sorted for my test report.

    I'm working in Add Command in my DB connection, and I've entered the code as below, and I can get the report to work as expected and bring me back my fields and values, however in the HAVING clause, T13A07 in the report as below, should be a parameter where the user is prompted to enter the match code. I've defined a parameter as MatchCode on the right of the Add Command pop-up, entered it into the report as ={?MatchCode} (so the line is HAVING (((MD009.MTCD09)={?MatchCode})) )and when I try to run the report it correctly prompts for the parameter, I enter T13A07 and it gives me an error saying 'Failed to retrieve data from the database. Details: 42S22:[IBM]...SQL0206 - Column T13A07 not in specified tables. [Database Vendor Code: -206 ]'

    Any help would really be appreciated, I can't see why it would work without the parameter but won't work with the parameter with the entered value or why it thinks it's a column heading and not a field value. I'm pretty new to Crystal Reports so apologies is this is a really dumb question.

    Jenny

    SELECT "MD009"."MTCD09" AS Code, Trim("MTDS08") AS "Match", Count(MD009.MEMB09) AS "Count"
    FROM "TKTDTA"."MD009" "MD009" LEFT JOIN "TKTDTA"."MD008" "MD008" ON ("MD009"."ACTR09" = "MD008"."ACTR08") AND (MD009.CONO09 = MD008.CONO08) AND (MD009.MTCD09 = MD008.MTCD08)
    WHERE (((MD009.ACTR09)='A') AND ((MD009.CONO09)='RAN') AND ((MD009.SRCE09)<>'C' Or (MD009.SRCE09)='C') AND ((MD009.PYRF09)<>0) AND ((MD009.STAT09)='1'))
    GROUP BY MD009.MTCD09, Trim("MTDS08")
    HAVING (((MD009.MTCD09)='T13A07'))
    ORDER BY MD009.MTCD09, Count(MD009.MEMB09) DESC

  2. #2
    Join Date
    Dec 2012
    Posts
    2

    Re: Referring to Parameter in Crystal Reports SQL

    I worked it out, I had to put the parameter into single quotes. I suppose Crystal Reports must take the parameter as you enter it and put it into the SQL and try to send it to the server, if I was entering criteria normally I'd enter 'T13A07' so I suppose when you enter the parameter it makes sense that it wants this to be in single quotes too. I'm glad I've worked it out as it was driving me mad.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured