AnsweredAssumed Answered

How making a 'Distinct' with DBMS Script ?

Question asked by jmroman on Oct 19, 2015
Latest reply on Oct 20, 2015 by George Jeffcock

Like • Show 0 Likes0
Comment • 5

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:

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

jmroman Oct 19, 2015 8:36 PM

Correct Answer

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

See the reply in context

No one else had this question

Outcomes

George Jeffcock

Oct 19, 2015 6:08 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.

Actions

George Jeffcock

@ George Jeffcock on Oct 19, 2015 5: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...)

Actions

jmroman @ George Jeffcock on Oct 19, 2015 8:37 PM

That is clear thanks

Actions

jmroman @ George Jeffcock on Oct 19, 2015 8:36 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

Actions

George Jeffcock

@ jmroman on Oct 20, 2015 4:52 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

Actions

5 Replies

George Jeffcock Oct 19, 2015 6:08 PM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
- George Jeffcock @ George Jeffcock on Oct 19, 2015 5:58 PM
  Mark Correct
  Correct Answer
  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...)
  Like • Show 0 Likes0
  Actions
  - jmroman @ George Jeffcock on Oct 19, 2015 8:37 PM
    Mark Correct
    Correct Answer
    That is clear thanks
    Like • Show 0 Likes0
    Actions
- jmroman @ George Jeffcock on Oct 19, 2015 8:36 PM
  Unmark Correct
  Correct Answer
  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
  Like • Show 0 Likes0
  Actions
  - George Jeffcock @ jmroman on Oct 20, 2015 4:52 AM
    Mark Correct
    Correct Answer
    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
    Like • Show 0 Likes0
    Actions

How making a 'Distinct' with DBMS Script ?

Outcomes

Related Content

Recommended Content

Incoming Links