Recent content by cantor001

Q. Who da man ? A. YOU da man. Spot on. My script now looks like this and I keep to keep my beloved whitespace... This is great as I use makefiles to deploy my scripts (locally) and if I couldn't get it to work then I'd be in deep doodoo. echo exit | sqlplus...

Stout fella, well done. Anyone who managed to get the correct result with my meadering input deserves a medal :-) Good night.

Right. Ignore my posts, I shouldn't have posted anything at all with my brain currently at half mast. Normally I try everything out _before_ I post a reply but I thought I could get away with winging it and all I've done is confuse myself. I'm off to bed.

Sorry, I've had a long day and not thinking straight, yes, you are on the right track...I got confused by th layout of thw query. This is also untested but looks like what you've already got. Well done :-) SELECT DISTINCT m.CompanyName, LEFT(m.ID, 8) AS ARNO, SUM(subs.sub_cnt) AS sub_cnt...

Yes, it won't work, these two datasets are mutually exclusive, they don't need to be joined, they need to be combined.

I've had a brief look and the only thing that seems to leap out at me is that LEFT OUTER JOIN you have between your 2 sub and comp derived tables, I think that this bit... GROUP BY Adv_MemNo) AS subs ON subs.adv_memno = LEFT(m.ID, 8) LEFT OUTER JOIN should read GROUP BY Adv_MemNo) AS subs ON...

New question please. I'd like to run a script that creates a database object from the command line. Simple. Now this is from the operating system command line (which happens to be Windows), not SQL Developer, not TOAD, not PL/SQL. And before you ask yup I've done the usual (google, resident...

Solved it. What a performance. The view 'REPORTING.vw_work_order_sample_result' had a number of dependent views, one of which had an ORDER BY clause put in while I was testing it's output. Obviously a view with an order by is a no-no (I vaguely remember being a bit surprised at the time that it...

Help, I've been using Oracle for 3 weeks and can't get a simple query to work. Googled it, asked the local database guru, looked at Oracle ref books, searched forum threads, gave up :-) Its only when I use a column from the joined table that the query fails - never seen anything like it...

Sure, from your description I'm taking it that you are talking about a drillthrough report as opposed to an embedded subreport. Add a report parameter to the second report specifying the person's unique identifier, then select the report component in the main report you wish to use for the link...

1 Can I drill through to a report not in the same directory as the existing report? Yes, you can do this by setting the folder as well as the report name, for example I specify the following relative path+report name to have many main reports call a subreport in a subfolder...

Yes

One alternative to using count() would be if exists(select null .....) I'm sure there are others.

I am not sure what the policy is for pushing a particular product on these forums but I use SQLPrompt

One crucial question I forgot to ask: "What is the purpose in ordering the result set ?" If you don't need to do it then the problem goes away! "Possible to get a copy of the query plan for the select containing the order by clause ?" Sorry, should hqve been clearer, what I am after is the...