Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Do not count duplicate values in SQL

BjornlaatDec 22 2022 — edited Dec 22 2022

Good morning,

I am having some problems with my current query, namely that duplicate values are being counted. This is, because in a pair table of two tables, values can appear twice.

The current query is as follows:

SELECT
BUY.SHIPMENT_XID BUYSHIPMENT,
SELL.SHIPMENT_XID SELLSHIPMENT
FROM
SHIPMENT SELL,
V30_SHIPMENT_ORDER_RELEASE V30,
V30_SHIPMENT_ORDER_RELEASE V300,
SHIPMENT BUY
WHERE
SELL.PERSPECTIVE = 'S
AND SELL.SHIPMENT_GID = V30.SHIPMENT_GID
AND V30.ORDER_RELEASE_GID = V300.ORDER_RELEASE_GID
AND V300.PERSPECTIVE = 'B
AND V300.SHIPMENT_GID = BUY.SHIPMENT_GID

In the table shipment there is only the shipment number as shipment_gid and perspective indicating whether it is a buy shipment or a sellshipment via B or S
Example data:
SHIPMENT_GID, PERSPECTIVE
1, B
2, S
3, B
4, S

To link a buy shipment to a sellshipment, oracle has the table: V30_SHIPMENT_ORDER_RELEASE. Here 3 fields are available: PERSPECTIVE, SHIPMENT_GID and ORDER_RELEASE_GID. By means of an ORDER_RELEASE_GID (or several) buy shipments are linked to a sellshipment.
Example data:
SHIPMENT_GID, PERSPECTIVE, ORDER_RELEASE_GID
1, B, RELEASE1
2, S, RELEASE1
3, B, RELEASE2
3, B, RELEASE3
4, S, RELEASE2
4, S, RELEASE3

the result the above query should give is:
BUYSHIPMENT, SELLSHIPMENT
1, 2
3,4

the current query returns the following result
BUYSHIPMENT, SELLSHIPMENT
1, 2
3,4
3,4

Comments

mickyw

Hello Melvis,

This is nice feature. I've actually tested it and found very annoying problem in this implementation. As soon as ZFS storage appliance creates hidden .$EXTEND directory in share (this is created automatically and is visible only if you go into Solaris share) - then all requests to URL representing account - fail with permission problem. Truss invoked on httpd process shows that it fails on .$EXTEND directory and returns this problem as generic permission problem to swiftclient user.

Another question I have: we frequently set mountpoint on project level changing it from /export to somthing like "/export/<project_name>". This allows us to have the same share names and having same share paths (like /export/<project_name>/<share_name>). From document it looks like directory under "/export" is used as Swift account. Does this mean that using 3-level mountpoint for shares is not supported for object storage use?

Regards,

Michal

1 - 1
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 19 2023
Added on Dec 22 2022
4 comments
431 views