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

    How to create a database object using SQLPlus from the operating system command line

    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...
  2. cantor001

    correlated sub querries with aggregate functions using mutiple joins

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

    correlated sub querries with aggregate functions using mutiple joins

    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.
  4. cantor001

    correlated sub querries with aggregate functions using mutiple joins

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

    correlated sub querries with aggregate functions using mutiple joins

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

    correlated sub querries with aggregate functions using mutiple joins

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

    How to create a database object using SQLPlus from the operating system command line

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

    ORA-01722 error but column is numeric

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

    ORA-01722 error but column is numeric

    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...
  10. cantor001

    RE: SSRS 2005 Report/Sub-Report help

    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...
  11. cantor001

    SSRS 2000 Drill Through Reports

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

    Why is SSMS IntelliSence Lame?

    Yes
  13. cantor001

    Emailing directly from job

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

    Why is SSMS IntelliSence Lame?

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

    Is Order By that costly?

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

    SQL Select Statement Help Needed

    The pks are the book_key fields in both tables..." Yes, I think I'm going to have to pass on this one and leave it with r937. If book_key is the pk for both tables then I'm not sure how you would relate a book to its author using just these two entities. good luck
  17. cantor001

    SQL Select Statement Help Needed

    Yes, once again I have no idea why I am using all these brackets! My own code never uses reserved words for object naming and I never alias a table with its own name. Weird. More importantly, in addition to the above comments note that while the brackets, and the alias used in line 6 are merely...
  18. cantor001

    SQL Select Statement Help Needed

    From what I understand of the schema this should do the trick - but, respectfully, I think the model needs to separate the relationship between author and book into a separate entity. DECLARE @author_name VARCHAR(100); SET @author_name = 'Limeback, Rudy' SELECT book.[book_type]...
  19. cantor001

    SQL Select Statement Help Needed

    Sure, what are the pks for Book and Author tables ?
  20. cantor001

    SQL Select Statement Help Needed

    Posting an answer is a little tricky I'm a little unclear how you are modelling this. One possible structure for this would be: Author ====== author_id author_name Book ==== book_id book_name book_type BookAuthor (assoc entity) ========== author_id book_id If you can post the schema that...

Part and Inventory Search

Back
Top