Search results for query: *

got it to work, thanks!

I can't seem to get an outer join working the way I want. Given the following: 1) the 'sample_custs' view will return a sample of ~100 customer IDs. 2) the 'customer_computed_value' table will have all the customer IDs. 3) the 'order_basket' table will only have a subset of the customer IDs...

Yes, I made sure our developer tried the obvious after you suggested it, and he did. Filter will not work.

Won't that take the rows I want (with the 'filler' Months) out of the result set before it gets crosstabbed? Would defeat the purpose...

I'm trying to develop a report that has Months along the row headers and Categories along the column headers, with a single measure. I have an outer join in place so that a Month gets pulled onto the report, even if there are no records for that Month in any of the Categories. The report...

I keep running into the problem outlined in TN5700-72X-1001. I want to create reports using Custom Groups that show Cumulative Frequency (using the RunningSum function), but the metric is not calculated in the order of the Custom Group. Anyone else run into this and figure out a workaround...

Thanks for the reply. I meant to say "rows in the fact table that are AND aren't in the lookup table" - hence the outer join. Didn't think it was possible, but wanted to check.

Can MSTR generate this? SELECT A.ATTRIBUTE, B.COUNT(PK) FROM LOOKUP_TBL A, FACT_TBL B WHERE A.PK=B.PK (+) GROUP BY A.ATTRIBUTE So I want to count rows in the fact table that aren't in the lookup table, group by a higher level attribute, and have the extra show up as a row with a NULL as the...

nlim, how would you get the sql engine to drop the 'group by col_id' clause in the outer query in your example? thanks...

I'm trying to work around a proprietary database quirk with the RANK function. If I have a query like this: SELECT COL_ID, RANK(FACT) FROM TABLE GROUP BY COL_ID it fails. I need to build a query like this: SELECT *, RANK(FACT) FROM ( SELECT COL_ID, FACT FROM TABLE GROUP BY COL_ID) The goal...

Has anyone tried to generate a query in MSTR that does a SELECT *? I can only get so far, creating an attribute defined as - ApplySimple("*"). But when I put it in a query, MSTR thinks it's a column and aliases it as 'CustCol'. And the bigger question, even if we could generate the syntax...

OK, I got it to work, but I had to unscientifically toy with it first. I tried your latest suggestions, but still no luck, so I checked the MSTR kbase and found this tech note: TN5200-7X0-0090 - "The filter is ignored when running a report containing the count metric of an attribute that has a...

Thanks for following up. I'm off site today and probably tomorrow, but will pick it up again on Monday. I did update the schema, didn't re-boot the i-server though. I see your logic, but I'm not sure why the engine is ignoring the filter criteria. If I get a chance, I'll test it in the...

Still not getting it. The Customer attribute is already linked to column CUST_ID on both tables already. I changed the ApplySimple expression to use a column that is only on the RESPONSE fact table. I defined a filter as [Response Force Join]@ID=1 under the custom expression option. The tool...

I'm not quite getting it. Here is where I am: - created attribute, custom mapping, where it sees the column CUST_ID only on the RESPONSE table (not sure if this matters, but CUST_ID is only part of the PK on table RESPONSE. for [OUTBOUND TABLE KEY FIELD], CUST_ID is all you need, and not the...

Sorry, I was on hiatus from this client for a few weeks. I still have the problem. sunnyphoenixier - this does not work. HOUSEHOLD_ID is not on the fact tables. specifying a factID under the count parameters using a column from the OUTBOUND or RESPONSE tables results in a SQL generation...

I have a lookup table, CUST, with columns: CUST_ID HOUSEHOLD_ID I have two fact tables, OUTBOUND and RESPONSE, where CUST_ID is part of the primary key. I want to create a report that has two counts: 1) COUNT (DISTINCT A.HOUSEHOLD_ID) FROM CUST A, OUTBOUND B WHERE A.CUST_ID=B.CUST_ID...

Ha. No worries. I've down roads like that before. I recently had to twist a DBA's arm to allow me to use a datetime column has a primary key and join on it, instead of a surrogate integer column.

The DBA asked for it (Oracle 9i). I thought as much, but I really don't have the credentials to effectively argue it. But if it can't be done, then it can't be done. Thanks. It is a little peculiar the engine uses "IN" for a single element, but "=" for a single ID though.

If I define a prompt on an attribute ID:DESC, and set the min/max number of elements to 1, can I make the engine generate SQL using WHERE X = Y instead of using WHERE X IN (Y)? For that matter, if I create a static filter that only qualifies on one attribute element, can I force the use of '='...