Little Help with SQL, DISTINCT with JOIN

Little Help with SQL, DISTINCT with JOIN

  

Hoping someone can help here. 


I have a SQL function where I want to return the DISTINCT Supplier.Id from a  join function I have attempted to write:


I get the error: SQL1: Incorrect Syntax near the keyword 'WHERE' 


Can anyone help me get this over the line? 

Is it even possible to DISTINCT function a join like this?


SyntaxEditor Code Snippet

SELECT DISTINCT {Supplier}.[Id]
FROM
    (SELECT {PurchaseOrderLine}.[Product],{Product}.[SupplierId]
    FROM {PurchaseOrderLine}
    INNER JOIN {Product}
    ON {PurchaseOrderLine}.[Product] = {Product}.[Id])

WHERE {PurchaseOrderLine}.[PurchaseOrder]=@PurchaseOrderId

From what I see on Oracle database standpoint, the syntax is correct...
Maybe you can look the generated SQL to see where the problem lies..
You can see the generated SQL in one of the tabs on Advance Query/SQL window.

Harlin Setiadarma wrote:

From what I see on Oracle database standpoint, the syntax is correct...
Maybe you can look the generated SQL to see where the problem lies..
You can see the generated SQL in one of the tabs on Advance Query/SQL window.


Hi Harlin,


Thanks for the reply, this is the executed SQL (little beyond my knowledge) 

SyntaxEditor Code Snippet

SELECT DISTINCT  [CJXGXX043].DBO.[OSUSR_Y9W_COMPANY].[ID] 
FROM 
    (SELECT  [CJXGXX043].DBO.[OSUSR_Y9W_PURCHASEORDERLINE].[PRODUCT], [CJXGXX043].DBO.[OSUSR_Y9W_PRODUCT].[SUPPLIERID] 
    FROM  [CJXGXX043].DBO.[OSUSR_Y9W_PURCHASEORDERLINE] 
    INNER JOIN  [CJXGXX043].DBO.[OSUSR_Y9W_PRODUCT] 
    ON  [CJXGXX043].DBO.[OSUSR_Y9W_PURCHASEORDERLINE].[PRODUCT] =  [CJXGXX043].DBO.[OSUSR_Y9W_PRODUCT].[ID]) 
 
WHERE  [CJXGXX043].DBO.[OSUSR_Y9W_PURCHASEORDERLINE].[PURCHASEORDER]=@PurchaseOrderId

Hi,

Try put Where clause inside From select. You are searching on "PurchaseOrder" outside From Select, but that Select doesn't return that field.

The "DISTINCT" field is also invalid, because From Select isn't looking for Supplier table, maybe {Product}.[SupplierId]?

SELECT DISTINCT {Supplier}.[Id]
FROM
    (SELECT {PurchaseOrderLine}.[Product],{Product}.[SupplierId]
    FROM {PurchaseOrderLine}
    INNER JOIN {Product}
    ON {PurchaseOrderLine}.[Product] = {Product}.[Id]
    WHERE {PurchaseOrderLine}.[PurchaseOrder]=@PurchaseOrderId)

Nelson Inácio wrote:

Hi,

Try put Where clause inside From select. You are searching on "PurchaseOrder" outside From Select, but that Select doesn't return that field.

The "DISTINCT" field is also invalid, because From Select isn't looking for Supplier table, maybe {Product}.[SupplierId]?

SELECT DISTINCT {Supplier}.[Id]
FROM
    (SELECT {PurchaseOrderLine}.[Product],{Product}.[SupplierId]
    FROM {PurchaseOrderLine}
    INNER JOIN {Product}
    ON {PurchaseOrderLine}.[Product] = {Product}.[Id]
    WHERE {PurchaseOrderLine}.[PurchaseOrder]=@PurchaseOrderId)

Hi Nelson,

Thanks for the reply!

I tried both your suggestions but the same issue really, the syntax error just changes from 'Where' to ')'.

One thing I noticed later on is I have a warning "there is a mismatch between the number of SELECT'ed and the output structure" .


My structure includes the attributes "SupplierId", "ProductId" and "PurchaseOrderId", what else should I have? Might this be the issue? 

Talis Krumins wrote:

Nelson Inácio wrote:

Hi,

Try put Where clause inside From select. You are searching on "PurchaseOrder" outside From Select, but that Select doesn't return that field.

The "DISTINCT" field is also invalid, because From Select isn't looking for Supplier table, maybe {Product}.[SupplierId]?

SELECT DISTINCT {Supplier}.[Id]
FROM
    (SELECT {PurchaseOrderLine}.[Product],{Product}.[SupplierId]
    FROM {PurchaseOrderLine}
    INNER JOIN {Product}
    ON {PurchaseOrderLine}.[Product] = {Product}.[Id]
    WHERE {PurchaseOrderLine}.[PurchaseOrder]=@PurchaseOrderId)

Hi Nelson,

Thanks for the reply!

I tried both your suggestions but the same issue really, the syntax error just changes from 'Where' to ')'.

One thing I noticed later on is I have a warning "there is a mismatch between the number of SELECT'ed and the output structure" .


My structure includes the attributes "SupplierId", "ProductId" and "PurchaseOrderId", what else should I have? Might this be the issue? 


Also I noticed the PurchaseOrderId wasn't called up so i modified my query a little. 


SyntaxEditor Code Snippet

SELECT DISTINCT {Product}.[SupplierId]
FROM
    (SELECT {PurchaseOrderLine}.[Product],{Product}.[SupplierId], {PurchaseOrderLine}.[PurchaseOrder]
    FROM {PurchaseOrderLine}
    INNER JOIN {Product}
    ON {PurchaseOrderLine}.[Product] = {Product}.[Id])
WHERE {PurchaseOrderLine}.[PurchaseOrder]=@PurchaseOrderId

Talis Krumins wrote:

Nelson Inácio wrote:

Hi,

Try put Where clause inside From select. You are searching on "PurchaseOrder" outside From Select, but that Select doesn't return that field.

The "DISTINCT" field is also invalid, because From Select isn't looking for Supplier table, maybe {Product}.[SupplierId]?

SELECT DISTINCT {Supplier}.[Id]
FROM
    (SELECT {PurchaseOrderLine}.[Product],{Product}.[SupplierId]
    FROM {PurchaseOrderLine}
    INNER JOIN {Product}
    ON {PurchaseOrderLine}.[Product] = {Product}.[Id]
    WHERE {PurchaseOrderLine}.[PurchaseOrder]=@PurchaseOrderId)

Hi Nelson,

Thanks for the reply!

I tried both your suggestions but the same issue really, the syntax error just changes from 'Where' to ')'.

One thing I noticed later on is I have a warning "there is a mismatch between the number of SELECT'ed and the output structure" .


My structure includes the attributes "SupplierId", "ProductId" and "PurchaseOrderId", what else should I have? Might this be the issue? 

It's also an issue, for sure. The output structure must match with query output


Nelson Inácio wrote:

Talis Krumins wrote:

Nelson Inácio wrote:

Hi,

Try put Where clause inside From select. You are searching on "PurchaseOrder" outside From Select, but that Select doesn't return that field.

The "DISTINCT" field is also invalid, because From Select isn't looking for Supplier table, maybe {Product}.[SupplierId]?

SELECT DISTINCT {Supplier}.[Id]
FROM
    (SELECT {PurchaseOrderLine}.[Product],{Product}.[SupplierId]
    FROM {PurchaseOrderLine}
    INNER JOIN {Product}
    ON {PurchaseOrderLine}.[Product] = {Product}.[Id]
    WHERE {PurchaseOrderLine}.[PurchaseOrder]=@PurchaseOrderId)

Hi Nelson,

Thanks for the reply!

I tried both your suggestions but the same issue really, the syntax error just changes from 'Where' to ')'.

One thing I noticed later on is I have a warning "there is a mismatch between the number of SELECT'ed and the output structure" .


My structure includes the attributes "SupplierId", "ProductId" and "PurchaseOrderId", what else should I have? Might this be the issue? 

It's also an issue, for sure. The output structure must match with query output


Hi,



This is more than likely a dumb question, but what other outputs should I have? (bit of a newby to this). how do i determine this? 


You will need something like this, if {PurchaseOrderLine}.[Product] is a "Product Identifier" type

SELECT DISTINCT {Product}.[SupplierId],{PurchaseOrderLine}.[Product],@PurchaseOrderId
FROM
    (SELECT {PurchaseOrderLine}.[Product],{Product}.[SupplierId]
    FROM {PurchaseOrderLine}
    INNER JOIN {Product}
    ON {PurchaseOrderLine}.[Product] = {Product}.[Id]
    WHERE {PurchaseOrderLine}.[PurchaseOrder]=@PurchaseOrderId)

Nelson Inácio wrote:

You will need something like this, if {PurchaseOrderLine}.[Product] is a "Product Identifier" type

SELECT DISTINCT {Product}.[SupplierId],{PurchaseOrderLine}.[Product],@PurchaseOrderId
FROM
    (SELECT {PurchaseOrderLine}.[Product],{Product}.[SupplierId]
    FROM {PurchaseOrderLine}
    INNER JOIN {Product}
    ON {PurchaseOrderLine}.[Product] = {Product}.[Id]
    WHERE {PurchaseOrderLine}.[PurchaseOrder]=@PurchaseOrderId)

It is a product identifier type, so the good news is I now have a match between my query and output structure, however I still get the same syntax error (I've moved the ')' back and forth between where i originally had it, and you're indicating. )


Would the OML's help?


I am using Service Editor 10.0.405 and it is okay:

The entities:

tiong tan wrote:

I am using Service Editor 10.0.405 and it is okay:

The entities:


Hi,


Two things I've noticed that might be making things work a bit differently, I noticed you set up you output as an entity as opposed to a structure like i did. What attributes did you use in your entity? 


Also, The screenshot you took; the thing I noticed there is my PurchaseOrder is a linked attribute to another entity, would that make a difference to how this functions?






Two things I've noticed that might be making things work a bit differently, I noticed you set up you output as an entity as opposed to a structure like i did. What attributes did you use in your entity? 

=== I Change the entity output to a structure and it is still okay. There is nothing different whether you use entity or structure. Both are data types.

Also, The screenshot you took; the thing I noticed there is my PurchaseOrder is a linked attribute to another entity, would that make a difference to how this functions?

== No, i have changed it too. But to use the type of Entity Identifier has some benefits.. OS will create a dropdown widget when it is an entity identifier.


tiong tan wrote:





Two things I've noticed that might be making things work a bit differently, I noticed you set up you output as an entity as opposed to a structure like i did. What attributes did you use in your entity? 

=== I Change the entity output to a structure and it is still okay. There is nothing different whether you use entity or structure. Both are data types.

Also, The screenshot you took; the thing I noticed there is my PurchaseOrder is a linked attribute to another entity, would that make a difference to how this functions?

== No, i have changed it too. But to use the type of Entity Identifier has some benefits.. OS will create a dropdown widget when it is an entity identifier.


well.......... i don't get it... No reason it should work any differently. 


If you still get stuck:

1. try to update the service studio to the latest update

2. isolate the error; some error may affect the others

cheers,

tan