[rpmartin]

I’m trying to get a query that includes item descriptions and minimum priceing, but when I put description in the group by clause, I get the error:

“inconsistent datatypes: expected – got CLOB[10300]”

I get similar errors if I try min, to_char, or substr on the description. Any ideas how to work around this?

thanks,

Rob
This is a cached copy. Click here to see the original post.

[frederic.jannelle]

You cannot use the Group By clause for text variables (like your CLOB description).

I am not sure what kind of results you expect from your query, why do you want to use Group By (and why use it on description?)? If you use the item’s description to simply differentiate the items than you could use the item’s ID instead and you could Group By this variable.

Hope this helps,

[rpmartin]

RE: Memo field in GROUP BY clause

Thanks for your response Frederic. I guess I should give a little more background…

I’m creating a datafeed that includes both an items description and prices. We have items in a parent/child relationship where the parent item has the description and the child items have prices, so the query looks something like:

SELECT p.item_id, p.description, min(decode(ip.Name,’Web Retail’, ip.Item_Unit_Price)) as WebRetail

FROM items p INNER JOIN

items c on p.item_id = c.parent_ID INNER JOIN

item_prices ip on c.item_id = ip.item_id

GROUP BY p.item_id, p.description

I left out a lot of it, but that’s the gist of the query. It might not have been clear in the original post, but I’m running this query using an odbc connection.

thanks,

Rob

[frederic.jannelle]

Can you give me an example of a ‘p.description’ value? Is it a really long description? I understand that you want to use this value to identify your items. Is there any other field you could use that is not a CLOB that would accomplish the same thing? Could you use ‘p.item_id’ alone in the GROUP BY?

One thing is sure is that you cannot include ‘p.description’ in your GROUP BY because of the limitations surrounding CLOB and GROUP BY and also for the fact that two descriptions are very unlikely to be identical thus rendering the GROUP BY useless for this field.

[rpmartin]

RE: Memo field in GROUP BY clause

In this query, description will always be the same for each item_id. The group by is needed because each item_id/description combination will have several prices from associated child records. I only want the min and max price associated with a parent record. Description is required for the datafeed, so must be included in the query.

Maybe I’m thinking about the problem the wrong way and there’s a better way to do this. I’ve also tried creating derived tables, but the query takes too long to run. I don’t want to have to import the data to a database locally and process it there, but I can’t quite get it to work in a single query.

thanks for your help,

Rob

[bdougherty]

RE: Memo field in GROUP BY clause

Hi,

You may be able to accomplish this by performing a substring on the desired field using a function(text).

Set it to be as long as practical and this should convert it to a string that can let you group.

I’ve not tried this in NS, but have had luck doing this in other db systems.

Thanks,

Brian

[rpmartin]

RE: Memo field in GROUP BY clause

Thanks Brian. I tried using substr(description,1,10) in place of description and got the same error.

Oracle has another function (dbms_lob.substr) specifically for clobs and blobs, but I get a syntax error when I try to use it. Looks like I’ll just import two separate queries to a local database and process it from there :/

Thanks,

Rob

[Author]

Posts