Skip to Content
avatar image
Former Member

Picking one type of multi record

HI I Have data like this:

Order# Order_code Order_Amt

123 Code1 100.00

123 Code2 100.00

123 Code3 100.00

123. Code4 100.00

Most of our orders will have only one order_code per order, but some have more than 1.Different parts require different works etc..

So in the above suppose we have a hierarchy, if there is a code1, only show that. if not a code1 in this list, then code2 is next.

How do I code this in the record select?

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Apr 11, 2016 at 04:32 AM

    Hi Paul,

    What is the data source of the report?

    If you're reporting against a Command Object, you'd need to handle this in the SQL query.

    If you're reporting against tables/views, there is a way however, I prefer to know the source first.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

    • You should be able to handle this in a SQL Query like this:

      Select

      T2.Order#,

      T2.Code,

      T2.Rows

      From

      (

      Select T.Order#,

      T.Code,

      Dense_Rank() Over(Partition By Order# Order By Case When Code = 'Code1' then 1

      When Code = 'Code2' then 2

      When Code = 'Code3' then 3

      When Code = 'Code4' then 4 END ASC) Rows

      from Table T

      )T2

      Where

      T2.Rows = 1

      Order by T2.Code

      -Abhilash