Skip to Content
author's profile photo Former Member
Former Member

Command Line SQL to use as a parameter

Hello all,

I am a beginner/intermediate cyrstal XI sp2 user who is self-tought (with some help from this forum). Here is my question/request. I am attempting to utilize a command to prompt a parameter. The problem is my SQL skills are minimal. So, here is what I want to do.

I want to concatenate three fields to display in the parameter selection area as a dropdown menu.

Here is my SQL:

Select STRING(eh.EPISODE_NUMBER,' - ',eh.Program_Value,' - ',aa.assess_date) from system.episode_history eh, cwsavpmliveadassess.adult_assessment_butte aa where eh.EPISODE_NUMBER=aa.EPISODE_NUMBER AND eh.PATID = aa.PATID

Do I have to define the "aa.assess_date" as a date value?

How then do I incorporate the command into the parameters?

I hope this makes sense, if not, I will do my best to clarify.

Thank you,


Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Oct 06, 2010 at 03:39 PM

    Q1: Do I have to define the "aa.assess_date" as a date value?

    A1: No, you are concatenating it into a string.

    Q2: How then do I incorporate the command into the parameters?

    A2: Well the 1st thing you'll need to do is give you concatenated field a name... Then you'd simply create a new parameter and change the type to dynamic. Then, under "Value", where it says "Click here to add item", click it and find your command.

    Just a warning: Based on the SQL you provided, you are bringing in a single concatenated field which means that you are unlikely to have a single field in your database that matches it.

    You may want to bring in some sort of an ID field to use as the parameter value and use the concat field as the description.



    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member


      As far as the the date showing up as a numeric string, you'll need to check the documentation for your database for a function that will convert a date field to a string field without displaying the underlying numeric values.

      Google database name string functions and you're bound to find something that works.

      As far as the rest of the parameter itself I'd be willing to bet that there's an AssessmentID that you aren't currently using.

      So here's what I'd do.

      I'd create a Command with 5 fields (no concatenation)... AssessmentID, Client_Name, Episode, Program, Date

      Id then determine the most logical hierarchy that a user is likely to follow. Think of it this way. As a user trying to find a specific record, where would I generally want to start looking?

      So for the sake of moving forward, lets say: Program > Episode > Client_Name > Date

      I'd then create a parameter with a cascading list of values, using the command as the data source and I'd use the AssessmentID as the actual value that gets passed. See "Creating a parameter with a cascading list of values" in the online help for details.

      By taking this approach, the user would first select the Program they want, which will filter the list of available episodes... The user would then choose an episode from those available, which will filter the Client_Names of available... The user would then Select the client they want and then they would finally select the appropriate date.



Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.