Distinct in an OQL

[pwilliams-3]

All,
Does anyone know how to do a distinct in an OQL? I would like to get distinct
PI's in the following OQL.

//Create OQL object
pdm_error = PDMOQLCreate(&pdm_oql);

//Create OQLet search. PI
pdm_error = PDMOQLAddStringOQLet(pdm_oql, "PDMc_PI", ", "main_branch", ",
&pdm_oqlet);

//Create OQLet search. Branch
pdm_error = PDMOQLAddStringOQLet(pdm_oql, "PDMc_PIBranch", ", "elements",
", &pdm_oqlet);

//Create OQLet search. PIV
pdm_error = PDMOQLAddStringOQLet(pdm_oql, "PDMc_PIV", ", "release_level",
", &pdm_oqlet);

//Create OQLet search. Release Level
pdm_error = PDMOQLAddStringOQLet(pdm_oql, "PDMc_ReleaseLevel",
"((name='APPROVAL_PENDING') or (name='RELEASED') or (name='RELEASED_HISTORY')
or (name='RELEASED_NOT_MAINTAINED'))", ", ", &pdm_oqlet);

//Create OQL object
pdm_error = PDMOQLCreate(&pdm_oql2);

//Create OQLet search. PI
pdm_error = PDMOQLAddStringOQLet(pdm_oql2, "PDMc_PI", ", "folder", ",
&pdm_oqlet);

sprintf(szTemp, "name='%s", pdm_folderName);

//Create OQLet search. Folder
pdm_error = PDMOQLAddStringOQLet(pdm_oql2, "PDMc_Folder", szTemp, ", ",
&pdm_oqlet);

//Create OQL object
pdm_error = PDMOQLCreate(oql);

//Merge the OQL handles
pdm_error = PDMOQLMerge(pdm_oql, pdm_oql2, "AND", *oql);



Patrick Williams
Application Engineer
Steelcase, Inc.

This thread is inactive and closed by the PTC Community Management Team. If you would like to provide a reply and re-open this thread, please notify the moderator and reference the thread. You may also use "Start a topic" button to ask a new question. Please be sure to include what version of the PTC product you are using so another community member knowledgeable about your version may be able to assist.

[pwilliams-3]

No,
What is happening is that I get back the PI several times because it has one
or more PIV's at any of those release levels. I want to get back distinct PI
names.



Patrick Williams
Application Engineer
Steelcase, Inc.

[MarcMettes]

> From: -
>
> Does anyone know how to do a distinct in an OQL? I would like to
> get distinct PI's in the following OQL.
>
> //Create OQL object
> pdm_error = PDMOQLCreate(&pdm_oql);
>
> //Create OQLet search. PI
> pdm_error = PDMOQLAddStringOQLet(pdm_oql, "PDMc_PI", ",
> "main_branch", ", &pdm_oqlet);
>
> //Create OQLet search. Branch
> pdm_error = PDMOQLAddStringOQLet(pdm_oql, "PDMc_PIBranch",
> ", "elements", ", &pdm_oqlet);
>
> //Create OQLet search. PIV
> pdm_error = PDMOQLAddStringOQLet(pdm_oql, "PDMc_PIV", ",
> "release_level", ", &pdm_oqlet);
>
> //Create OQLet search. Release Level
> pdm_error = PDMOQLAddStringOQLet(pdm_oql,
> "PDMc_ReleaseLevel",
> "((name='APPROVAL_PENDING') or
> (name='RELEASED') or
> (name='RELEASED_HISTORY') or
> (name='RELEASED_NOT_MAINTAINED'))",
> ", ", &pdm_oqlet);
>
> //Create OQL object
> pdm_error = PDMOQLCreate(&pdm_oql2);
>
> //Create OQLet search. PI
> pdm_error = PDMOQLAddStringOQLet(pdm_oql2, "PDMc_PI", ",
> "folder", ", &pdm_oqlet);
>
> sprintf(szTemp, "name='%s", pdm_folderName);
>
> //Create OQLet search. Folder
> pdm_error = PDMOQLAddStringOQLet(pdm_oql2, "PDMc_Folder",
> szTemp, ", ", &pdm_oqlet);
>
> //Create OQL object
> pdm_error = PDMOQLCreate(oql);
>
> //Merge the OQL handles
> pdm_error = PDMOQLMerge(pdm_oql, pdm_oql2, "AND", *oql);


If all you want is a list of ProductItems, then why are you
adding branch, revision, version, and release level to the
query. Why not just do a "PDMc_PI" query?

With SQL, doing a query only on the PDM_PRODUCTITEM table is
guaranteed to give you distinct results. Once you join in
Revision and Version, you get multiple results for each
ProductItem.

For example 'select piname from pdm.PDM_PRODUCTITEM' will give
you the same results as 'select distinct piname from
pdm.PDM_PRODUCTITEM', except that use of 'distinct' will make
take longer, since oracle has to search for duplicates.


Marc






gib