noob alert! What i thought was a real simple query...

Hi

I have a table which has the column [itemNumber] Which contains numbers from 000 to 999. I have another table which has the UPC data for given items
I am trying to get results from my query that will show me every number in the itemNumberSet table that does not already exist (in the substring) of the UPCcode column.

By using the query below i am able to retrieve the opposite, and it works by returning results that do exist in the UPCcode column. But I cannot seem to get it to do the opposite which is what i am after. I figured it would be as simple as using NOT IN but that returned 0 results.

SELECT itemNumber FROM itemNumberSet
WHERE itemNumber IN (select SUBSTRING(UPCcode, 9, 3) FROM itemUPCtable)
ORDER BY itemNumber

Thanks for any suggestions you might have.
Jthis perhaps?
SELECT itemNumberSet.itemNumber
FROM itemNumberSet
LEFT OUTER
JOIN itemUPCtable
ON SUBSTRING(itemUPCtable.UPCcode,9,3) = itemNumberSet.itemNumber
WHERE SUBSTRING(itemUPCtable.UPCcode,9,3) IS NULL
ORDER
BY itemNumberSet.itemNumber|||I really need to practice playing with joins, cause as it stands im a hack. I should really be taking a course or something. I see you are in Toronto. I'm in Mississauga. Any tips as to where one might go to be properly educated in SQL?

Thanks again
J|||you can go a long way with online free sql tutorials

however, you need to make sure you are on an actual tutorial site, as in here's-some-good-information-because-i-love-sql type of tutorial site, rather than here's-some-sql-stuff-which-i-lifted-from-somewhere-so-i-can-run-a-lot-of-ads-and-make-money type of tutorial site

there are a couple of good sites listed here: http://r937.com/links.cfm?links=sqlsql

Non aggregate case

Hello,
In the same fact table, I have real values and min/max values. The type of value (real, min, max) is a dimension. So, I want to aggregate value only for real value, because for min/max values, all the leaf are filled by my program.
So, it's possible to say "I want the not aggregate value in the database" in MDX query?
Thank you,
Guillaume.

Hi Guillaume,

Can you create a view (or named query, if you're using AS 2005) on the fact table, such that 3 separate measure fields can be created for the real, min and max values? In that case, appropriate aggregation functions of sum(), min(0 and max() can be applied to each measure. Something like:

create view splitfact as

select fact.dim1, fact.dim2, fact.dim3, ..,

sum(case fact.typedim when 'real' then fact.valfield else 0 end) as realval,

sum(case fact.typedim when 'min' then fact.valfield else 0 end) as minval,

sum(case fact.typedim when 'max' then fact.valfield else 0 end) as maxval

from fact

group by fact.dim1, fact.dim2, fact.dim3, ..

|||

If you look at the attribute in the cube editor, you will see that there is an aggregation usage property. Change it from default to none.

_-_-_ Dave

|||Thank you !

Ok, now I created 2 NamedCalculation on my DataSourceView. The first one is for 'real' value with aggregate function = SUM, and for the second NamedCalculation, I put 'min/max' values, with aggregate function = NONE.

But when I browse data, I can't see my data with aggregate function=None. But when I change the aggregate function, I can see my data. It's like if the none aggreagte function doesn't work correctly. Do you know this problem?