Announcement

Collapse
No announcement yet.

Saved Query - Avoid Duplicate Flexfield (Shipment)

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Saved Query - Avoid Duplicate Flexfield (Shipment)

    I'm working on a query to avoid a duplicate flexfield (ATTRIBUTE2) from the shipment table (Buy Shipment - Inserted via UI by user):

    CHECK ONE SQL:
    select attribute2 from shipment s2 where s2.shipment_gid = ? and not exists (select 'X' from shipment s3 where s3.attribute2 = s2.attribute2)

    FIND ALL SQL:
    select attribute2 from shipment s2 where s2.shipment_gid = ? and not exists (select 'X' from shipment s3 where s3.attribute2 = s2.attribute2)

    Having a bit of trouble getting this to stick.


    Any advise on alterations to clean this up?

    Thanks for the help!

  • #2
    Hi Pauls.

    Where are you going to use this saved query because your query is not clear.
    Do you want to fetch the distinct attribute2 or if user enters the value for attribute2 that should not be available in attribute2.shipment column.

    Simple way is, you can use DISTINCT. select distinct attribute2 from shipment

    Comment


    • #3
      Yes, that is correct. I’m looking for the distinct flex field value from the table. I’ve tried using distinct and this doesn’t seem to do the trick.

      Comment


      • #4
        I think you would want to do something like this:
        select attribute2 from shipment s2 where s2.shipment_gid = ? and not exists (select 'X' from shipment s3 where s3.attribute2 = s2.attribute2 and s3.shipment_gid <> s2.shipment_gid)

        This way, you are checking for duplicates and ensuring that you are not pulling in the same shipment in your NOT EXISTS. If you get no result, then the s2.attribute2 is a unique value.

        Comment


        • #5
          ttwam this worked like a charm. The one item I noticed (and there may be no work around) is that the action check in the SERVPROV domain only applies to specific shipments pertaining to that carrier. In my domain, the check works against every shipment. Would there be a way to further restrict this within the SP domain? I wouldn’t anticipate carriers inputting the same BOL (more like internal team members). However, there is always that slight possibility.

          Cheers

          Comment

          Working...
          X