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 Chriss Miller 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. john76

    case statement in a select?

    Try this: Select Status = Case Field When 'A' Then 'X' When 'B' Then 'Y' When 'C' Then 'Z' End HTH, John
  2. john76

    [b]Getting results from multiple rows to be displayed on same line[/b]

    Glad I could help, try it out and see if you can adapt it to your situation, I'd try and finish but another problem just got dumped on me ( this one from my boss, I guess it takes priority ;-) ). Gotta go for now. John
  3. john76

    [b]Getting results from multiple rows to be displayed on same line[/b]

    OK, I got some code from page 384 of "Microsoft SQL Server 2000 Bible" by Paul Nielsen and did some testing: -- Note: This can be cut and pasted into Query Analyzer without modification and run as is. Use tempdb Go Set NoCount On -- Set up the testing table If Exists( Select 1...
  4. john76

    [b]Getting results from multiple rows to be displayed on same line[/b]

    I'm not sure of the coding off the top of my head but a recursive select is generally the way to pivot row data into column data when the number of records is unknown. I'll see if I can find some example code. John
  5. john76

    Error converting data type decimal to decimal.

    ESquared, that is a very good point. OK then, if decimal/numeric data types of different precisions are considered different data types, why couldn't the SQL Server team include the precision of each type in the error message? ie: Error converting data type decimal (20, 5) to decimal (20, 3)...
  6. john76

    Error converting data type decimal to decimal.

    Glad I could help, I've run into this sort of issue before and I find that it's always best to explicitly define the value before assigning it to the variable. Sometimes SQL Server acts so dumb... John
  7. john76

    PowerPlay cube build time

    First of all, I agree with all of the above in that 84 seems to be incredibly excessive and most likely should be split among several cubes. Secondly, I don't believe that the build time you stated is unreasonable, unless you have half a dozen other cubes with similar times that need to be...
  8. john76

    Error converting data type decimal to decimal.

    Just for curiosity's sake, try: SET @CullSlabs = CAST(CAST(ISNULL(@CullSlabs, 0) as decimal(16,3)) / 2000.0 AS decimal(16,3)) And see what happens. John
  9. john76

    Datetime in Sql Server

    SQL Server 2000 only has the datetime and smalldatetime types for storing date information. However, if you only supply the date portion and not the time, then SQL Server will automatically append the time of midnight (00:00:00.000) to the date when storing. This can work perfectly well in...
  10. john76

    Issue with roll back

    SQLHunter, Sorry, been away for a while. I am not sure what you mean here, can you explain further? Since you seem to be interested in all three paths succeeding together or not at all, I'll try to explain my original post a little more clearly. To have multiple "paths" in a single...
  11. john76

    Issue with roll back

    It is actually possible to have 3 separate workflows in a single transaction, but there are a few caveates(sp?). First, all of the tasks must use the same connection. This also means that as only a single task may execute at a time, SQL Server may do a lot of switching between workflows during...
  12. john76

    Multiple Case End

    Try wrapping your case statements inside of coalesce functions with the alternate path of '' or ' '. Sample as follows works: Declare @Col1 int, @Col2 int, @Col3 int Set @Col1 = 0 Set @Col2 = 0 Set @Col3 = 3 Select Col1 = @Col1, Col2 = @Col2, Col3 = @Col3...
  13. john76

    Selecting by Date only in Date-Timestamp problem

    Try: Select * From [elvis].[EP3_14] Where Convert(varchar, ID_502, 101) = '08/09/2004' HTH, John
  14. john76

    Date Conversion Problem

    I'm a bit leary of performing comparisons using Between, <, <=, >, and >= on character data. If what you are trying to accomplish is to select all records where the MYDATE field is (using your example) equal to January 14, 2004 or January 15, 2004 then you might try: Select * From TableA...
  15. john76

    Alter Table Syntax (Column Order)

    Try doing the modification in Enterprise Manager instead of Query Analyzer. I believe that in the background it actually does recreate the table, but the end result is that the columns are in the order you put them in. HTH, John
  16. john76

    How to tell (programatically) if job is currently executing

    I ran a search on this forum on the keywords "agent job" and it turned up a helpful thread: thread183-869335 Using that I came up with this code: If Exists( Select * From tempdb..sysobjects Where Name Like '#xp_results%' )Drop Table #xp_results CREATE TABLE #xp_results (job_id...
  17. john76

    How to tell (programatically) if job is currently executing

    Hi all, I am trying to set up a schedule for a data warehouse refresh and I need to be able to code some logic that can determine if the job is already active. pseudocode: --Begin job If (data is ready){ If (job is not running){ Run Job "Refresh Warehouse" } } Else{ Exit } I am...
  18. john76

    Remove spaces

    Take a look at the Replace function: Set @desc = Replace(@desc, ' ', '') Set @desc = Replace(@desc, '<', '[') Set @desc = Replace(@desc, '>', ']') This code should (ie - haven't tested it) remove all spaces and change <> characters to [] characters. HTH, John
  19. john76

    Views and Queries

    Just thought I'd add my 2 bits. (Normal) Views do not run faster than a query, as vongrunt said. However, if you can write a stored procedure that accomplishes the same task, that does run faster than a view/query. The reason is that the stored procedure is compiled on the first use, a query...
  20. john76

    Can DTS Connect to a DB2 Database?

    correction: Second query would look like this: Select * From OpenQuery(AS400, Select * From MYLIB.MYTABLE Where MYFIELD = 4 ) John

Part and Inventory Search

Back
Top