SELECT DISTINCT in a BROWSE

questions
1

Hello everyone !!

I have browse with 2 tables, Father and son.

I need to show parents that only contain children and I only show 2 fields from the parent.

In the schematic file I marked the child with the INNER option. Up to here all nice. It shows me the parents who have a child.

The problem is that it shows me the father as many times as he has children.

How do I tell it to only show one parent ?

In SQL I put DISTINCT, but I don’t know how to put it in the browse

The sentence is as follows:

SELECT DISTINCT A.COARTICLE, A.DEARTICLE FROM Article AS A
INNER JOIN ArticlesFormula AS AF ON (A.COARTICULO=AF.COARTICULO)

Thank you very much

2

Since you’re using SQL I would leverage that.

  1. Easiest - create a view on the SQL server (select DISTINCT my fields from table1 where exists (select id from table2 where table1.id = table2.id)). Import that view into your dictionary. Use that in the browse.

  2. Use the table in the browse. Do not join it in Clarion. Instead, use prop:sqlFilter ThisView{prop:sqlfilter} = ‘parentId in (select parentId from childtable)’

2 Likes
3

Hello Jane, Thank you very much !!

4

Carlos,
You can also use the aggregate functionality on the view to accomplish your distinct.

MyView VIEW(Article)
  PROJECT(ART:COARTICLE)
  PROJECT(ART:DARTICLE)
   JOIN()
    ....
   END
END
  CODE

  OPEN(MyView)
  MyView('ART:COARTICLE', PROP:NAME} = 'DISTINCT A.COARTICLE'

This will replace the first column name with what you assign to prop:name. This allows you to do things like DISTINCT or TOP 10, etc.
Search in the help for aggregate

3 Likes
5

thanks for the information. I have read the help and I could with your example and looking at the debug I could understand why the “SQL Advanced” tab is used in the browse.

Thank you very much

6

Rick,
Does it support the distinction for 2 or more columns?

Should it look like

 OPEN(MyView)
  MyView('ART:COARTICLE', PROP:NAME} = 'DISTINCT A.COARTICLE'
  MyView('ART:DEARTICLE', PROP:NAME} = 'DISTINCT A.DEARTICLE'

??

7

That isn’t how distinct works. A single DISTINCT applies to all the columns in the select statement.

SELECT DISTINCT A.COARTICLE, A.DEARTICLE FROM Article

The distinct applies to both COARTICLE and DEARICLE.

1 Like
8

Rick,

Ah, I see, so do we need to use

MyView('ART:COARTICLE', PROP:NAME} = 'DISTINCT A.COARTICLE'

just for the first column in the Clarion View?