5 Replies Latest reply on Jan 9, 2014 1:48 PM by rhisingh

    Data modeler: Having Maintainable Query for View Creation

    e17ffd2d-d8d7-40b8-a4fc-c4c30395ee08

      Hi,

       

      I am using SQL Developer 4 and the data modeler. I typically create a view in SQL developer (from a SQL select statement). I then drag and drop the view to the data modeler in order to have it stored for the next time I regererate the database schema from the data modeler. This works and I can see the query for my view when I click Properties on the view in the data modeler. However, the query I see there is logically equivalent (and very similar) but it is hard to read for a programmer as carrier returns are added, the various elements of my where clause are not in the same order as I programmed them, comments (starting with '--') where not transfered with the drag and drop.

       

      I feel I cannot only rely on the version of the view stored in the data modeler to keep my DDL code that creates the view. Maintenance of that code would be too difficult. I feel I will need to keep a separate SQL script (in a separate version control tool) containing the select statement of my view. I would then always need to make my modifications in this select statement, update my view in the database and then drag and drop the result back in the data modeler. This is cumbersome.

       

      Do you have any solution for me? Thanks!

        • 1. Re: Data modeler: Having Maintainable Query for View Creation
          Jeff Smith Sqldev Pm-Oracle

          Might I suggest you're doing it 'backwards?'

           

          You might get closer to what you're looking for if you were to build the view in the modeler first. Version control it there, and then push it to our database(s) as needed.

           

          You an ask the modeler to 'Use SQL Developer Formatter' in the preferences which will use the SQL Developer formatter preferences when generating the DDL.

           

           

          Can you give an example of the commented code not coming over correctly? Are you saying you see these in the SQL panel of the view in SQL Developer, but they're not preserved in the model when importing them? -- Comments generally aren't stored in the data dictionary.

          • 2. Re: Data modeler: Having Maintainable Query for View Creation
            Philip Stoyanov-Oracle

            Hi,

            I then drag and drop the view to the data modeler in order to have it stored for the next time I regererate the database schema from the data modeler.

            If you don't want to use Query builder then there is a solution for you:

            1)Don't use QB for just seeing select statement - use "DDL Preview " on that view.

            QB always parse and reconstruct the query (and loosing comments - we have a bug logged for that) so you won't see original select statement

            2) Don't use "Parse older style views"functionality - I logged a bug against that functionality because it changes view definition without need

            3) Make sure "Use SQL Developer formatter" is not set in "Preferences>Data Modeler>DDL" - otherwise definition will be formatted according formatting rules set in preferences

             

            Philip

            • 3. Re: Data modeler: Having Maintainable Query for View Creation
              rhisingh

              Hi Jeff would you be able to help me in creating a data model  cause im really stuck with this one .Basically if been asked to create a survey application in oracle apex that use to excel based . So the info i was given was in a form of  excel sheet which looks like this

               

              USER'S                                                                                                                                                                    VENDORS                                                                                                                                  TOPICS

               

               

               

              NAMEE-MAILTSSAORACLEHPIBMMSSAPINTERGRAPHCISCORelationshipContractingPerformanceArchitectureSupplier FeedbackComment
              jjjjjnn BixxxxxffBis@esko.co.zaYesYesYesYesxAdded as requested by Sanet Mulder
              itha CCCniahniJ@ymail.co.zaYesYesYesxx
              Elliot danA@eskm.comYesxxx
              Ger quegj@1mail.comYesYesYesYesYesYesYesYesxxx
              isha PerKey@em.co.zaYesx
              John RatoRat.one@kom.cozaYesxx

               

              So where it says yes thous are the vendors that people associated with them have to asses and where there's an X thous are the topics that the vendors have to be rated on . So if for example the first guy on the list  jjjjjnn Bixxxxxff will asses TSSA , ORACLE , HP , IBM , MS , SAP  on the topic of Architecture and if you look at the second user itha CCCniah he would rate TSSA , ORACLE , INTERGRAPH on the topics of Relationship and performance  . Any idea how i could data model this to get my table structures right .so that features like completion status could be displayed to the user through APEX which can only be done by a correct data-model i have tried normalization but  i did go anywhere becauce there are so many variations any idea on how you would go about data modeling this would be greatly appreciated thank you     If you would like a better copy of the table my email is    rhine.singh@gmail.com    

              • 4. Re: Data modeler: Having Maintainable Query for View Creation
                Jeff Smith Sqldev Pm-Oracle

                A few things:

                • please don't hijack threads, start new ones if you have a new question
                • your data is live for everyone to see, including your colleagues and their email addresses
                • I'm not really available for data modeling consulting, but if you have specific tooling questions, we're happy to oblige

                • 5. Re: Data modeler: Having Maintainable Query for View Creation
                  rhisingh

                  Hay Jeff  thanks man i changed the email address before posting that question so the data isnt real  any one you may have in mind that could help  .