Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. JRO061601

    help with outer join

    got it to work, thanks!
  2. JRO061601

    help with outer join

    ...b.customer_id, b.c12_gross_amt from customer_computed_value b, sample_custs c where b.customer_id = c.customer_id union select x.* from ( select 'Order Basket', e.customer_id, sum(d.total_gross_amt) from order_basket d, sample_custs e where d.customer_id =...
  3. JRO061601

    ReportNet CrossTab problem

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

    ReportNet CrossTab problem

    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...
  5. JRO061601

    ReportNet CrossTab problem

    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...
  6. JRO061601

    Can't sort RunningSum metric by Custom Group

    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...
  7. JRO061601

    Is this SQL possible?

    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.
  8. JRO061601

    Is this SQL possible?

    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...
  9. JRO061601

    SELECT * in MSTR?

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

    SELECT * in MSTR?

    ...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 is to display the result set in MSTR. I'm guessing the xtab engine wouldn't...
  11. JRO061601

    SELECT * in MSTR?

    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...
  12. JRO061601

    Force Join on Count from Lookup Table

    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...
  13. JRO061601

    Force Join on Count from Lookup Table

    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...
  14. JRO061601

    Force Join on Count from Lookup Table

    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...
  15. JRO061601

    Force Join on Count from Lookup Table

    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...
  16. JRO061601

    Force Join on Count from Lookup Table

    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...
  17. JRO061601

    Force Join on Count from Lookup Table

    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...
  18. JRO061601

    WHERE clause use '=' instead of 'IN' ?

    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.
  19. JRO061601

    WHERE clause use '=' instead of 'IN' ?

    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.
  20. JRO061601

    WHERE clause use '=' instead of 'IN' ?

    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 '='...

Part and Inventory Search

Back
Top