SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard big_al's Avatar
    Join Date
    May 2000
    Location
    Victoria, Australia
    Posts
    1,661
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Help With Query!

    Hello everyone

    I am having the most annoying time with this query, and just cannot quite get this thing to do what I want.

    I have an exact replica of a legacy (4D) database in Sql Server (the designers of this database did not have a good naming convention so excuse the field names, I will be converting them in good time ).

    What I am trying to achieve is select all items and all notes attached to the item (in stock_notes) that either have a note_sequence of 1.0 or 2.0.

    Now not ALL items have a sequence of 2.0, but all have 1.0, BUT 2.0 holds the Author of the item, so if the item does not have a sequence of 2.0 then I wish to have a 'N/A' displayed. Fine I have that working, BUT if and item has both then I am getting 2 rows returned, I ONLY WANT 1..

    Hope this makes sence.

    Code below

    Code:
    SET NOCOUNT ON
    SELECT  
    		dbo.[PRONTO:stock_master].stock_code AS ProntoID, 
     Author = (CASE WHEN MAX([PRONTO:stock_notes].stock_note_sequence) 
    				  > 1 THEN [PRONTO:stock_notes].stock_note ELSE 'N/A' END),
     dbo.[PRONTO:stock_master].stock_group AS ProntoGroup, 
    		dbo.[PRONTO:stock_master].stk_user_group#1 AS StockUserGroup, 
     dbo.[PRONTO:stock_master].stk_user_group#2 AS StockUserGroup2, 
    		dbo.[PRONTO:stock_master].stk_description AS Desc1, 
     dbo.[PRONTO:stock_master].stk_desc_line_2 AS Desc2, 
    		dbo.[PRONTO:stock_master].stk_desc_line_3 AS Desc3, 
     dbo.[PRONTO:stock_master].stk_unit_desc AS UnitDesc, 
    		dbo.[PRONTO:stock_master].stk_pack_qty AS PackQty, 
     dbo.[PRONTO:stock_master].stk_std_cost AS StandardCost, 
    		dbo.[PRONTO:stock_master].stk_replacement_cost AS ReplacementCost, 
     dbo.[PRONTO:stock_master].stk_sales_cost AS SalesCost, 
    		dbo.[PRONTO:stock_master].stk_creation_date AS DateCreated, 
     dbo.[PRONTO:stock_master].stk_creation_date, 
    		dbo.[PRONTO:stock_master].stk_apn_number, 
     dbo.[PRONTO:stock_master].stk_pack_desc AS PackDesc, 
    		dbo.[PRONTO:stock_master].stk_date_last_change AS DateLastChanged, 
     dbo.[PRONTO:stock_master].stk_sales_cost AS Cost
    FROM		 
     [PRONTO:stock_master] 
    LEFT JOIN
    		[PRONTO:stock_notes] ON [PRONTO:stock_master].stock_code = [PRONTO:stock_notes].stock_code
    WHERE	 
     ([PRONTO:stock_notes].stock_note_type = N'sd') 
     AND (NOT ([PRONTO:stock_master].stock_code LIKE N'Z%')) 
     AND (NOT ([PRONTO:stock_master].stk_condition_code = N'D')) 
     AND ([PRONTO:stock_notes].stock_note_sequence <= 2.0)
    GROUP BY
     [PRONTO:stock_master].stock_code,
     [PRONTO:stock_notes].stock_note_sequence,
     [PRONTO:stock_notes].stock_note,
     [PRONTO:stock_master].stock_group,
     [PRONTO:stock_master].stk_user_group#1,
     [PRONTO:stock_master].stk_user_group#2,
     [PRONTO:stock_master].stk_description,
     [PRONTO:stock_master].stk_desc_line_2,
     [PRONTO:stock_master].stk_desc_line_3,
     [PRONTO:stock_master].stk_unit_desc,
     [PRONTO:stock_master].stk_pack_qty,
     [PRONTO:stock_master].stk_std_cost,
     [PRONTO:stock_master].stk_replacement_cost,
     [PRONTO:stock_master].stk_sales_cost,
     [PRONTO:stock_master].stk_creation_date,
     [PRONTO:stock_master].stk_apn_number,
     [PRONTO:stock_master].stk_pack_desc,
     [PRONTO:stock_master].stk_date_last_change
    ORDER BY 
     [PRONTO:stock_master].stock_code


    Any help would be greatly appreciated!
    .NET Code Monkey

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    try this --
    Code:
    select M.stock_code      as ProntoID
         , coalesce(note2.stock_note_sequence,'N/A') as Author 
         , M.stock_group AS ProntoGroup
         , M.stk_user_group#1 AS StockUserGroup
         , M.stk_user_group#2 AS StockUserGroup2
         , M.stk_description AS Desc1
         , M.stk_desc_line_2 AS Desc2
         , M.stk_desc_line_3 AS Desc3
         , M.stk_unit_desc AS UnitDesc
         , M.stk_pack_qty AS PackQty
         , M.stk_std_cost AS StandardCost
         , M.stk_replacement_cost AS ReplacementCost
         , M.stk_sales_cost AS SalesCost
         , M.stk_creation_date AS DateCreated
         , M.stk_creation_date
         , M.stk_apn_number
         , M.stk_pack_desc AS PackDesc
         , M.stk_date_last_change AS DateLastChanged
         , M.stk_sales_cost AS Cost
      from [PRONTO:stock_master] as M
    left outer
      join [PRONTO:stock_notes] as note1
        on M.stock_code 
         = note1.stock_code
       and note1.stock_note_type = N'sd'
       and note1.stock_note_sequence = 1
    left outer
      join [PRONTO:stock_notes] as note2
        on M.stock_code 
         = note2.stock_code
       and note2.stock_note_type = N'sd'
       and note2.stock_note_sequence = 2 
     where not M.stock_code LIKE N'Z%'
       and not M.stk_condition_code = N'D' 
       and (
           note1.stock_code is not null
        or note2.stock_code is not null
           )
    order 
        by M.stock_code
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard big_al's Avatar
    Join Date
    May 2000
    Location
    Victoria, Australia
    Posts
    1,661
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks rudy!

    Looks on track, but I am getting an odd error in QA

    Server: Msg 8114, Level 16, State 5, Line 1
    Error converting data type varchar to float.

    I cannot see why this error is popping up, any ideas?

    Thanks again
    .NET Code Monkey

  4. #4
    SitePoint Wizard big_al's Avatar
    Join Date
    May 2000
    Location
    Victoria, Australia
    Posts
    1,661
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Never mind I got it, was the line

    coalesce(note2.stock_note_sequence,'N/A') as Author

    ment to have been coalesce(note2.stock_note,'N/A') as Author


    Mate you truely are a champion! If I'm ever in Canada or your ever in OZ, I OW YOU A COUPLE OF BEERS
    .NET Code Monkey

  5. #5
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Uh oh, Ozzy vs Canuck beer-a-thon? Better give Molson/Fosters about a year's notice to stock up.


Bookmarks

Posting Permissions

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