0

I got the following set up: Oracle Database as back-end, a interface provided by our IT-Database Department and my Excel-VBA front-end.

The interface provides a variety of functions to insert, update and read data. The "DB_connection.read_test" function takes inputs:

  • TestID (0=ALL)

  • SQL Where Statement

  • Table name where the data will be stored

  • Set the header of the database as boolean true= set, false= leave as is

The function shows a view that the back-end guy prepared for me. Now there are two columns that I want to compare if they are unequal "PRODID" and "ArticleID" and get all unique pairs of "PRODID" and "ArticleID". The challenge is here that I only can write a where statement.

Sub query_ProdID_uneq_ArticleIDlocal()
Dim SQL_Where As String

SQL_Where = "PRODID <> ARTICLEID"
Call DB_connection.read_test(0, SQL_Where, "Pruefungen_Tab", True)

End Sub

This code above works but gets me all data where PRODID <> ARTICLEID. So they are not unique. I could work on in VBA-Excel and reduce the pairs to distinct pairs.

But is there a way to make this happen with the right WHERE Statement?

SQL_Where = "PRODID <> ARTICLEID and Unique(ArticleID)"

Or something like this?

Example Data:

Example Data from the query above. As you can see ArticleID and ProdID are not equal.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Lucas Raphael Pianegonda
  • 1,151
  • 12
  • 29
  • Could you show some example data. It's not clear what your exact problem is. Is it because you're getting all the permutations *(`1,2` **and** `2,1`)* or are you getting multiple rows with exactly the same values in the same columns? If you show us some example data *(either anonymised from your database, or made up by you to demonstrate the problem)* then we can solve your problem for the data you're showing us. https://stackoverflow.com/help/mcve Also, do you know the name of the table you're querying ***from***? That would allow you to use a sub-query in the WHERE clause. – MatBailie Sep 04 '18 at 12:22
  • Possible duplicate of [DISTINCT clause with WHERE](https://stackoverflow.com/questions/5610528/distinct-clause-with-where) – Lucas Raphael Pianegonda Sep 04 '18 at 14:16

0 Answers0