Results 1 to 6 of 6

Thread: Crystal Report

  1. #1
    Join Date
    Apr 2004
    Posts
    33

    Unanswered: Crystal Report

    I have a question and hope someone can help me with this.

    I have employee table

    employeeID,
    name,
    address
    ....

    Instructor table
    employeeID,
    specialty,
    ...

    InstructorRoster table

    employeeID,
    name,
    ...

    In my instructor table, some employees will get information from employee table and some employees will get infromation from InstructorRoster table. I don't know how to union this using Crystal.

    Here is the SQL statment.

    SELECT E.troop as troop,
    E.district as district,
    E.lname as lname,
    E.fname as fname,
    E.minit as mi,
    E.reg_no as regNo,
    E.ssn as ssn,
    I.specialty as specialty,
    I.certifiedExpire as certifiedExpire

    FROM tblSHPEmployee E, tblTrainingInstructor I
    WHERE E.reg_no = I.regNo

    UNION

    SELECT IR.troop as troop,
    IR.district as district,
    IR.lname as lname,
    IR.fname as fname,
    IR.mi as mi,
    IR.regNo as regNo,
    IR.ssn as ssn,
    I.specialty as specialty,
    I.certifiedExpire as certifiedExpire

    FROM tblTrainingInstructorRoster IR, tblTrainingInstructor I
    WHERE IR.regNo = I.regNo

    ORDER BY specialty, lname, fname, mi

    Thanks for your help.

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Just create a stored proc and use that stored proc in crystal .Thats all...
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Aug 2006
    Posts
    12
    Or, a View. But you need to do the sorting in Crystal. The Order by statement is not allowed in a View.

    If you're not sure, let me know what your database type is SQL Server, DB2, Oracle, etc. and maybe I can help on that end.

  4. #4
    Join Date
    Jul 2003
    Posts
    6
    The database that i use is DB2. Please tell me how to create a stored procedure. I don't see an option to create a sp. It has tables, view, triggers. Thanks,

  5. #5
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by ttnguyen
    The database that i use is DB2. Please tell me how to create a stored procedure. I don't see an option to create a sp. It has tables, view, triggers. Thanks,
    You can get help from this http://www.devx.com/IBMDB2/Article/28308
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  6. #6
    Join Date
    Aug 2006
    Posts
    12
    You don't need a stored procedure to do this. The code you typed above is a simple union. It can be saved as a view. Just copy and paste the SQL code into whatever you used to create your queries (Query Manager, QMF, TOAD). Usually there's a menu that helps you with creating a view.

    Here's an example of the code for creating a view:

    CREATE VIEW VW_TEST1 AS
    SELECT FIELD1, FIELD2, FIELD3,
    FROM TABLE1
    WHERE FIELD1 = 'A'

    This works in AS/400 Query Manager, MS SQL Server, Oracle, and QMF.

    Here's a web site that confirms the format

    http://www.stanford.edu/dept/itss/do...4.htm#i2102873

    The only time I use a Stored Procedure is if I want to use a parameter on the server end and pass it to the Crystal Report. Views can be linked to other views and other tables within a Crystal Report unlike queries when used as data sources.

Posting Permissions

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