Results 1 to 11 of 11
  1. #1
    Join Date
    May 2010
    Posts
    6

    Question Unanswered: Super Urgent HELP: Add in a column

    Now i'm developing a crystal report, and i have to way to get the answer that i needed. The only way that i can think is doing something to my database VIEW.

    I need to create an extra column and insert into my VIEW in order to get the answer i want.

    For example, below is my original table:

    presentationID ProgrammeID Title ExtraColumn
    12 123 Singing Competition Singing Competition
    13 NULL Promotion Break 1 minute Singing Competition
    15 146 Morning News Morning News
    17 105 Cooking Programme Cooking Programme
    19 NULL Promotion Break 1 minute Cooking Programme
    24 NULL Promotion Break 1 minute Cooking Programme
    30 NULL Promotion Break 1 minute Cooking Programme
    67 NULL Promotion Break 1 minute Cooking Programme
    Condition:
    If the programmeID is not null it should show Title and if the programmeID is null then the system will be continued automatically search until the system found out the ProgrammeID is not null.

    Eg: My report is referring to presentationID=67, so in my report the Title should be Cooking Programme.

    My crystal report code is like below:

    stringvar tt;

    if not isnull({Sheet1.ProgrammeID}) and {Sheet1.ProgrammeID}<>-1
    then tt:={Sheet1.PresentationTitle};

    tt
    HOW i can create the script to get the ExtraColumn by using my crystal report formula code and convert it to sql code???

    Thank in advance~
    Last edited by overcharge; 05-10-10 at 01:21.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    could you show us what you expect your output to be? I am having a little difficulty interpreting what you are attempting to ask.
    Dave

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Have a look at COALESCE().
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    NULLIF(COALESCE(CAST( ProgrammeID AS VARCHAR), Title), CAST(ProgrammeID AS VARCHAR))

  5. #5
    Join Date
    May 2010
    Posts
    6
    For example by referring my POST 1 scenario, In my report i need a output like

    PresentationID Title
    12 Singing Competition
    13 Singing Competition
    15 Morning News
    17 Cooking Programme
    19 Cooking Programme
    24 Cooking Programme
    30 Cooking Programme
    67 Cooking Programme

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Don't do this in SQL - do it in the client. This sort of thing is a dog in SQL.

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    To do this in an sql statement you would have to left outer join the table to itself, join it to itself within there, get the max presentation id where the programme id is not null and use a case statement in your select clause. This is why Pootle said it would be easier to do it within your programming. Though, I am not sure you can do that in Crstal Reports, been awhile, since I've used it. The SQL would be something along the lines of:
    Code:
    select a.presentationid
            ,case when programmeid is not null then a.title
                     else b.title
             end as title
       from table a
    left outer join (select b.presentationid, c.title
                          from table b
                                ,table c
                        where b.programmeid is null
                            and b.presentationid > c.presentationid
                            and c.presentationid = (select max(d.presentationid)
                                                                 from table d
                                                            where d.programmeid is not null
                                                              and d.presentationid < b.presentationid)) as b
        on b.presentationid = a.presentationid
      and a.programmedid is null
    where............
    Dave

  8. #8
    Join Date
    May 2010
    Posts
    6
    Thanks Dave and Poolet.

    I will try to take yours advices and give it a shoot.

  9. #9
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Yes poolet is always hepful

  10. #10
    Join Date
    May 2010
    Posts
    6
    I have try Poolet code, but the result is not the answer that i want.

    The result is almost same as my scenario 1.

    TO Dave, actually all my ProgID and Title are extracted from the same table.

    Is there any way to get my expected output??? I cannot get the expected answer by using Crystal Report . T_T

  11. #11
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    The example I gave you is the same table joined in one or another to itself 4 times.
    Dave

Posting Permissions

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