Results 1 to 7 of 7

Thread: Odd Sql Problem

  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Odd Sql Problem

    If I run this sql statement:

    SELECT order_numb, stat = case
    when status = 'N/S' then 'zN/S'
    when status = 'ready' then '0Ready'
    else cast('a' + status as varchar(13))
    end
    FROM rptCS_Sched

    I get a result like this:

    04017.0023 zN/S
    04023.0559 0Ready
    04127.0311 aJan 21 2005
    etc

    So far so good....

    Now I want to remove the left hand letter in that status column,
    and I try nesting the query, like this:

    select order_numb, right(max(stat),len(max(stat))-1) as Status from (
    SELECT order_numb, stat = case
    when status = 'N/S' then 'zN/S'
    when status = 'ready' then '0Ready'
    else cast('a' + status as varchar(13))
    end
    FROM rptCS_Sched
    ) s
    group by order_numb

    Now I get:
    04017.0023 N/S
    04023.0559 Ready
    04127.0311 an 21 2005

    For some reason, it removes 2 characters when there is a date contained in the string. Any body have any ideas?
    Inspiration Through Fermentation

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Post the Table DDL
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    CREATE TABLE [dbo].[rptCS_Sched] (
    [id] [int] IDENTITY (1, 1) NOT NULL ,
    [Order_Numb] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [product] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [tons] [real] NOT NULL ,
    [status] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [bill_to_numb] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [cust_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [request_date] [smalldatetime] NULL ,
    [LoadNum] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [OrderStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ItemStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SchedID] [bigint] NULL ,
    [Sched_Ord_Status] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]
    GO
    Inspiration Through Fermentation

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Not for me....the whole thing doesn't make sense though...

    Do you have a real world example?

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE [dbo].[myrptCS_Sched] (
    [id] [int] IDENTITY (1, 1) NOT NULL ,
    [Order_Numb] [varchar] (10) ,
    [status] [varchar] (13)
    )
    GO
    
    INSERT INTO  myrptCS_Sched (Order_Numb, status)
    SELECT '04017.0023', 'N/S' UNION ALL
    SELECT '04023.0559', 'Ready' UNION ALL
    SELECT '04127.0311', 'Jan 21 2005'
    GO
    
    
    SELECT    order_numb
    	, RIGHT(MAX(stat),LEN(MAX(stat))-1) as Status 
      FROM (
    	SELECT    order_numb
    		, stat = CASE WHEN status = 'N/S'   THEN 'zN/S'
    			      WHEN status = 'ready' THEN '0Ready'
    						    ELSE cast('a' + status as varchar(13))
    			 END
    	  FROM myrptCS_Sched
    	) s
    GROUP BY order_numb
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myrptCS_Sched
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    The table is a base recordset for a report. It gets truncated and repopulated hourly. The table shows the production status of each item on an order, so an order number can occur multiple times on the report (once for each item).
    For each item, there is one of three statuses(?):
    N/S = Not Scheduled
    Ready = Product is already in inventory
    Date = The date that production is scheduled for the item.

    Now I've been asked to show an order summary on the report, and let the users drill into the detail only if they need to. I decided to add a column to the table (Sched_Ord_Status), and go back and populate that column after the rest of the table has been populated. I need it to show the "worst case" status for the order. N/S being worst, and Ready being best. If everything on the order is Ready, except for one item, which is N/S, then the order status is N/S.
    Inspiration Through Fermentation

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    If it helps at all, the initial sql I posted is part of this statement:

    update rptCs_Sched set sched_ord_status = d.Status
    from rptcs_sched inner join (
    select order_numb, right(max(stat),len(max(stat))-1) as Status from (
    SELECT order_numb, stat = case
    when status = 'N/S' then 'zN/S'
    when status = 'ready' then '0Ready'
    else cast('a' + status as varchar(13))
    end
    FROM rptCS_Sched
    ) s
    group by order_numb) d on rptcs_sched.order_numb = d.order_numb
    Inspiration Through Fermentation

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well did you cut and paste my code to see that the sympton you describe is not happening with the code I posted?

    Noe how about the DDL for the other table and some DML to populate it with some sample data like I did...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •