Results 1 to 7 of 7

Thread: If statement

  1. #1
    Join Date
    Jan 2004
    Posts
    164

    Unanswered: If statement

    This is a view that I am working on. It is complete already, but now that I ran it I noticed that I would like to have a If statement in there to where qty_ship would = whatever is in qty_sales only is if qty_ship is null.

    I am not really good at If statements and was hopein someone could help.

    Here is the view:

    select
    SUBSTRING(CONVERT( CHAR(11),DATE_CUST_INVOICE,20), 1,7) as inv_period ,
    inv_numb, inv_line_numb, cust_code, s.item_code, salesrep_code,
    lot_code_source, qty_sales, qty_ship, 'SALES' as 'TYPE', fpo_number

    from opcsahf as s
    left OUTER join inuldspx as i
    on (inv_numb = substring(lot_code_or_inv_numb,1,6)
    and inv_line_numb = substring(lot_code_or_inv_numb,7,3)
    and s.item_code = i.item_code)
    left OUTER join spfbtfpo as f
    on substring(lot_code_source,6,6) = f.batch_numb
    where datepart(year,date_cust_invoice) = '2004'

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    Not sure what you want but I'd try a case-statement or isnull instead of IF.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    CASE qty_ship ISNULL THEN qty_Sales ELSE qtyship END

    EDIT: Damn...sniped again
    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.

  4. #4
    Join Date
    Jan 2004
    Posts
    164
    That Case statement worked good. Thanks again for all the help. The final code is:

    create view sales_with_lots1
    as
    select
    SUBSTRING(CONVERT( CHAR(11),DATE_CUST_INVOICE,20), 1,7) as inv_period ,
    inv_numb, inv_line_numb, cust_code, s.item_code, salesrep_code,
    lot_code_source =
    Case
    When lot_code_source is Null Then 'Not Found'
    Else lot_code_source
    End,

    qty_sales,
    qty_ship =
    Case
    When qty_ship is Null Then qty_sales
    Else qty_ship
    End,
    'SALES' as 'TYPE', fpo_number

    from opcsahf as s
    left OUTER join inuldspx as i
    on (inv_numb = substring(lot_code_or_inv_numb,1,6)
    and inv_line_numb = substring(lot_code_or_inv_numb,7,3)
    and s.item_code = i.item_code)
    left OUTER join spfbtfpo as f
    on substring(lot_code_source,6,6) = f.batch_numb
    where datepart(year,date_cust_invoice) = '2004'

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You guys must REALLY like Case statements!

    qty_ship = coalesce(qty_ship, qty_sales)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Oh, what the heck. Let's just fix up your whole statement.

    create view sales_with_lots1
    as
    select CONVERT(CHAR(13),DATE_CUST_INVOICE,120) as inv_period,--You want the hour, right?
    inv_numb,
    inv_line_numb,
    cust_code,
    s.item_code,
    salesrep_code,
    coalesce(lot_code_source, 'Not Found') as lot_code_source,
    qty_sales,
    coalesce(qty_ship, qty_sales) as qty_ship,
    'SALES' as TYPE,
    fpo_number
    from opcsahf as s
    left outer join inuldspx as i
    on inv_numb = left(lot_code_or_inv_numb,6)
    and inv_line_numb = substring(lot_code_or_inv_numb,7,3)
    and s.item_code = i.item_code
    left outer join spfbtfpo as f
    on substring(lot_code_source,6,6) = f.batch_numb
    where year(date_cust_invoice) = 2004

    Happy Birthday.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jan 2004
    Posts
    164
    Thanks for all the help.

Posting Permissions

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