Plex 2E

 View Only
  • 1.  How making a 'Distinct' with DBMS Script ?

    Posted Oct 19, 2015 12:52 PM

    Hi everybody,

     

    With Plex I want to make a "DISTINCT" in my view  like in sql server. I was looking in the "CA PLEX HELP" and I Found this:

     

               VW DBMS script SRC

     

    So, I create a source code with this instruction:


              -- END OF THE VIEW

              #END

              -- SELECT DISTINCT OF THE VIEW

              #BEGIN

              SELECT distinct * FROM [Nomina].[dbo].[AA1pV]



    And Include this SRC in the triple of the view like in the picture below:


    TRIPLE.jpg


    I compiled & Generated the view and the function which is pointing to that view.

    The view retrieve all the data, and the 'distinct has not effect.


    How could achieve to do a distinct in plex?


    thanks.




    P.D: This is the source code of my view that is generated using plex:


    #BEGIN VIEW AA1pV

    CREATE VIEW AA1pV AS

    SELECT

        T1.ReferenciaEntrada,

        T1.EstadoTransaccion

    FROM Aviso_Entrada T1

    -- END OF THE VIEW

    #END

     

    -- SELECT DISTINCT OF THE VIEW

    #BEGIN

    SELECT distinct * FROM [Nomina].[dbo].[AA1pV]

    #END

     



  • 2.  Re: How making a 'Distinct' with DBMS Script ?

    Posted Oct 19, 2015 01:52 PM

    you are on the right lines here.

     

    But the distinct DDL needs to be compiled into the view.

     

    So your DBMS should something like the following

     

    -- END OF THE VIEW

    #END

    CREATE OR REPLACE VIEW %N AS

    SELECT DISTINCT

    T1.ReferenciaEntrada,

    T1.EstadoTransaccion

    FROM Aviso_Entrada T1

    ORDER BY T1.ReferenciaEntrada

     

     

     

     

    Note: You can use a macro substitution feature to add a %N to the DBMS script that is replaced by the object's SQL identifier as defined by an impl NME triple.



  • 3.  Re: How making a 'Distinct' with DBMS Script ?

    Posted Oct 19, 2015 01:58 PM

    you need to understand you are going to make the view object in plex or SQL View in SQL server do the work of DISTINCT

     

    and not the functions that use the view. This enables a DBA to replace the view with new logic and all functions that use this view will benefit (or be unhappy...)



  • 4.  Re: How making a 'Distinct' with DBMS Script ?

    Posted Oct 19, 2015 04:38 PM

    That is clear   thanks



  • 5.  Re: How making a 'Distinct' with DBMS Script ?
    Best Answer

    Posted Oct 19, 2015 04:37 PM

    Thanks GeorgeJeffcock.

     

    This  syntax is the correct for my solution, I must put two #BEGIN... One to verify the existence of the view and other that create the view because

    if i put all into one #begin, it displays an error...

     

    -- END OF THE VIEW

    #END

     

    #BEGIN

    IF ( OBJECT_ID('%N') IS NOT NULL )

       DROP VIEW %N

    #END

     

    #BEGIN

    CREATE VIEW %N AS

    SELECT DISTINCT

    T1.ReferenciaEntrada,

    T1.EstadoTransaccion

    FROM Aviso_Entrada T1



  • 6.  Re: How making a 'Distinct' with DBMS Script ?

    Posted Oct 20, 2015 12:53 AM

    you are welcome. The following should work. I think "CREATE OR REPLACE" DDL is supported by SQL Server

     

    -- END OF THE VIEW

    #END

     

    #BEGIN VIEW %N

    CREATE OR REPLACE VIEW %N AS

    SELECT DISTINCT

    T1.ReferenciaEntrada,

    T1.EstadoTransaccion

    FROM Aviso_Entrada T1

    ORDER BY T1.ReferenciaEntrada