Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2012
    Posts
    4

    Unanswered: aggregate function

    Case
    when @Aggregate_Aware(Planning.schSSK.Fact_SSKTran.PNRL ocator=schSSK_FilterFlagData_SSK.PNRLocator And Planning.schSSK.Fact_SSKTran.TranEndStatusID, schSSK_Fact_SSKTranKP.PNRLocator=schSSK_FilterFlag DataKp_SSK.PNRLocator AND schSSK_Fact_SSKTranKP.TranEndStatusID) In(8,5,6)
    THEN 'Flagged'

    When @Aggregate_Aware(Planning.schSSK.Fact_SSKTran.Firs tName=schSSK_FilterFlagData_SSK.FirstName And Planning.schSSK.Fact_SSKTran.TranEndStatusID,schSS K_Fact_SSKTranKP.FirstName=schSSK_FilterFlagDataKp _SSK.PNRLocator AND schSSK_Fact_SSKTranKP.TranEndStatusID) In(8,5,6)
    THEN 'Flagged'
    Else 'Unflagged'
    End

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    was there a question to go along with that post?

    also, are you sure you want an ANSI SQL solution?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2012
    Posts
    4
    Hi,
    I want to know that whether the syntax is correct or not.

    Actually this code is not of ANSI SQL , but it is related to that.

    I want to know sequence of aggregate ,when and case. it is in correct format.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i've never seen a function like @Aggregate_Aware() before

    you're passing a series of ANDed conditions to it, and that's most likely invalid syntax
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2012
    Posts
    4
    Hi, Rudy.

    Thanks I just wanted to confirm whether my syntax was Okay. And as you said the syntax might be wrong, so could you please post an example how to use case and @Aggregate_Aware function.

    Thanks in advance.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by stausif View Post
    Thanks I just wanted to confirm whether my syntax was Okay. And as you said the syntax might be wrong, so could you please post an example how to use case and @Aggregate_Aware function
    In ANSI SQL you will never be able to call a function @Aggregate_Aware as that is an invalid SQL identifier (the leading @ is invalid)

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Formatted first WHEN ... THEN clause may be...
    Code:
    ...
    when @Aggregate_Aware(
                 Planning.schSSK.Fact_SSKTran  .PNRLocator = schSSK_FilterFlagData_SSK  .PNRLocator
             And Planning.schSSK.Fact_SSKTran  .TranEndStatusID
           ,              schSSK_Fact_SSKTranKP.PNRLocator = schSSK_FilterFlagDataKp_SSK.PNRLocator
             AND          schSSK_Fact_SSKTranKP.TranEndStatusID
         ) In (8,5,6)
    THEN 'Flagged'
    ...
    I guessed that you might want to pass the parameter "Planning.schSSK.Fact_SSKTran.TranEndStatusID" , if "Planning.schSSK.Fact_SSKTran.PNRLocator = schSSK_FilterFlagData_SSK.PNRLocator" was true.

    If my guess was right, you might want to use CASE expressions inside the function, like...
    Code:
    when @Aggregate_Aware(
             CASE Planning.schSSK.Fact_SSKTran  .PNRLocator
             WHEN    schSSK_FilterFlagData_SSK  .PNRLocator
             THEN Planning.schSSK.Fact_SSKTran  .TranEndStatusID
             END
           , CASE          schSSK_Fact_SSKTranKP.PNRLocator
             WHEN    schSSK_FilterFlagDataKp_SSK.PNRLocator
             THEN          schSSK_Fact_SSKTranKP.TranEndStatusID
             END
         ) In (8,5,6)
    THEN 'Flagged'
    But, this is based on my guess, and I don't know how the @Aggregate_Aware function works.
    So, my example might be completely wrong.
    Last edited by tonkuma; 06-09-12 at 06:50. Reason: Add an example and notes.

  8. #8
    Join Date
    Jun 2012
    Posts
    4
    Thanks a lot guys..

Posting Permissions

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