7 Replies Latest reply on Mar 9, 2017 9:00 AM by Gabriel LOURENCO-CARDOSO

    ORDER BY in CUSTOM SQL

    Gabriel LOURENCO-CARDOSO

      Hello

       

      I am using tableau desktop V90.

       

      I want to define a sql query in the "custom sql" part to connect to an oracle Database

      I am writing the following sql

       

      selet tab1.x  ,

              tab1.y,

              (select tab2.date from aaaaa tab2 where tab2.date > tab1.date and rownum = 1     order by tab2.date desc) as mydate

               from aaaa tab1

               order tab1.date desc

       

      ==> an error is displayed Oracle database error 907: ORA-00907: missing right parenthesis

      if I remove the "   order by tab2.date desc" it works

       

      please could you help me to know how I can have the order by in my second "select...

       

      thanks in advance

      BR

      Gabriel

        • 1. Re: ORDER BY in CUSTOM SQL
          Mark Holtz

          Gabriel,

          Tableau wraps a custom SQL statement in a nested sub-statement when issuing against standard relational database sources.

          You could include the ORDER BY so long as you also include the rownum limit there as well.

           

          May I ask why you even need the data source to be ordered?

          You can always control ordering in the visualization layer of whatever you build in Tableau.

          • 2. Re: ORDER BY in CUSTOM SQL
            Carl Slifer

            Hi Gabriel,

             

            Don't use Order By in custom SQL. How the data ends up in the resulting database has no order how Tableau queries this result later on for the rest of your workbook. When using fields you can change the sort order for each of these as you wish.

             

            Carl Slifer

            InterWorks

            • 3. Re: ORDER BY in CUSTOM SQL
              Gabriel LOURENCO-CARDOSO

              Hello

               

              thanks for your answer but I really need to perform the "order by" in the custom sql to retrieve the right date

              the result shall be

              tab1.x tab1.y tab1.date tab2.date

              tab1 and tab2 are alias for the same table in DB

               

               

              do you have a clue ?.

               

              BR

              Gabriel

              • 4. Re: ORDER BY in CUSTOM SQL
                Mark Holtz

                Oracle SQL is not my strong suit.

                In SQL Server, it be possible to re-write this query a number of ways that do not require the ORDER BY clause.

                 

                You'll have to flex your SQL-writing muscles.

                You need to either specify the rownum criteria (which makes the ORDER BY syntax necessary) or re-write without an ORDER BY in your outermost select.

                 

                Can you just set a WHERE rownum = n so that n is more records than you'll ever need to retrieve?

                • 5. Re: ORDER BY in CUSTOM SQL
                  Gabriel LOURENCO-CARDOSO

                  Hello

                  I don't if I am clear

                   

                  I have a table containing diferent kinds of events and the data of events.

                  evt1   event type 1     date1

                  evt2   event type 2     date2

                  evt3   event type 1     date3

                  evt4   event type 2     date4

                  evt5   event type 1     date5

                  evt6   event type 2     date6

                   

                  I would like to have as result of my custom sql

                   

                  evt1   event type 1     date1   evt2   event type 2     date2  ==> retrieve the following event with a diferent event type

                  evt3   event type 1     date3    evt4   event type 2     date4 ==> retrieve the following event with a diferent event type

                  evt5   event type 1     date5    evt6   event type 2     date6 ==> retrieve the following event with a diferent event type

                   

                  to build a report in which I will have

                  evt1  evt2  date1 - date2

                  evt3  evt4  date3 - date4

                  evt5  evt6  date5 - date6

                   

                  BR

                  Gabriel

                  • 6. Re: ORDER BY in CUSTOM SQL
                    Mark Holtz

                    How do you establish that Event 1 and Event 2 are related?

                    There should be a join criteria from data set 1 to data set 2 where they both share something in common, right?

                    • 7. Re: ORDER BY in CUSTOM SQL
                      Gabriel LOURENCO-CARDOSO

                      yes it is right

                      the event 1 and event 2 are store in the same table and the join is on the field user

                       

                      for each event whose even type is 1 I need to retrieve the following ( in term of date) event whose even type is 2