Distinct Query but need Data in TEXT field

[gkrenton]

I have a query that starts with a distinct call but I need to pull a text field into the results. However I get an error indicating that I can't use a 'blob' statement with distinct.
Here's the primary code:

select distinct
order_shipments.order_id,
order_shipments.shipment_id,
customer_contacts.customer_id,
trim(trailing from order_shipments.first_name) || ' ' || trim(trailing from orde
r_shipments.last_name),
order_shipments.ship_address_1,
order_shipments.ship_address_2,
order_shipments.ship_address_3,
order_shipments.ship_country,
order_shipments.ship_postal_code,
order_shipments.ship_city,
order_shipments.ship_state,
customer_contacts.day_phone,
upsnoteview.email,
upsnoteview.notification_yes,
upsnoteview.note_type,
'Your order has been shipped. Thanks for your business
. Shipment # ' || order_shipments.shipment_id || ' Order # '|| order_shipments
.order_id || ' Cust # ' || customer_contacts.customer_id,
upsmap.upsid,
carrier_ship_types.saturday_delivery,
upsvaluemap.mappedresult,
upsvalueview.shipment_total,
'Y',
upscodview.cod_flag,
upscodview.cod_total
etc -

Is there anyway to include a TEXT field in this sql statement as that I can pull some other order comments into my shipping interface?

Thanks in advance.

-gina

 

[travisbrown]

Maybe a self join on a distinct subquery? I"m not on a SQL machine right now to test, and I'm not sure about DISTINCT in subqueries.

This of course would not return DISTINCT image fields, but...


SELECT b.a,b.b,a.blob FROM tbl a RIGHT OUTER JOIN (SELECT DISTINCT a,b FROM tbl) b

 

[gkrenton]

Thanks but doesn't seem to work. The more I read on the subject the more unlikely it seems that one can easily add the data.

 

[travisbrown]

Here's a distinct query that joins to text.

SELECT a.c_id, b.c_copy,
FROM
tbl_sitecontent_copy b RIGHT OUTER JOIN
(SELECT DISTINCT c_id FROM tbl_sitecontent_copy) a ON a.c_id = b.c_id

I don't think there is a way to run a distinct on text fields in T-SQL. Best you could do is a distinct on the first 8000 characters by converting to varchar. if it was an absolute necessity, you could do a string comparison in VB.

 

[r937]

i would question the need for DISTINCT at all

judging by the table names visible in the portion of the query that you showed,

order_shipments
customer_contacts
upsnoteview
upsmap
carrier_ship_types
upsvaluemap
upsvalueview
upscodview

i don't see where the multiple rows could possibly be coming from

order_shipments.order_id is presumably unique, so with my (admittedly rudimentary) knowledge of ecommerce database design, the only thing i can think of that could be causing duplication is that there are multiple shipments for the same order, and these multiple shipments are completely identical in all selected columns!!

if that's the case, simple add the order_shipments primary key to the SELECT, and you shouldn't need the DISTINCT


r937.com | rudy.ca

 

[gkrenton]

Thanks, unfortunately the order_shipments is unique but the order_id is not. Shipments get appended to the order as they ship & need both when pulling into my shipping interface. BUT you have given me an idea of maybe doing this in 2 steps since the shipment_id is unique & then add the order info after I've gotten the text from the blob.

I appreciate the help.

gina