Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2006
    Posts
    4

    Unanswered: Sql If Statements

    I have some trade data. One colum is tran_status_mtf. within that column is "settled/traded", "cancelled", and "revised". I want to write and SQL statement that says if the trade is "settled" or "traded" display "A" in my output. How do I do this? I am new to SQL. Thanks in advance guys!!!

  2. #2
    Join Date
    Oct 2003
    Location
    Rhodesia
    Posts
    28
    Try this :

    SELECT IF( tran_status_mtf = 'settled' or tran_status_mtf = 'traded' , 'A', NULL) FROM YOUR_TABLE;

  3. #3
    Join Date
    Feb 2006
    Posts
    4
    Thanks for the input but how do I do it if I already have the following code?

    select sourcecode='35', tran_#_mtf, pmf_udstr#3, tran_status_mtf, trantypetextmtf, cusip_mtf, qtyunitsl_s_hld, Broker_mtf='888', tranprice_mtf, + SPACE (1), trade_date_mtf, settle_date_mtf,+ SPACE (2), coupon_rate_mtf, ytm, stwcurrent,+ SPACE (1), ai_pur_sold_mtf, sec_fee_mtf,+ SPACE (2), commission_mtf, total_amt_mtf, + SPACE (2) from avivaholdings2_rpt where trade_date_mtf > CURRENT_TIMESTAMP -30 AND commission_mtf = '0'

  4. #4
    Join Date
    Oct 2003
    Location
    Rhodesia
    Posts
    28

    Lightbulb

    Try this:
    SELECT sourcecode = '35',
    tran_#_mtf,
    pmf_udstr#3,
    IF( tran_status_mtf = 'settled' or tran_status_mtf = 'traded' , 'A', tran_status_mtf) AS TRANS_STATUS_MTF,
    trantypetextmtf,
    cusip_mtf,
    qtyunitsl_s_hld,
    Broker_mtf = '888',
    tranprice_mtf,
    + SPACE (1),
    trade_date_mtf,
    settle_date_mtf,
    + SPACE (2),
    coupon_rate_mtf,
    ytm, stwcurrent,
    + SPACE (1),
    ai_pur_sold_mtf,
    sec_fee_mtf,
    + SPACE (2),
    commission_mtf,
    total_amt_mtf, + SPACE (2)
    from avivaholdings2_rpt where trade_date_mtf > CURRENT_TIMESTAMP -30 AND commission_mtf = '0'

    If you have more than 2 values to compare against a CASE statement can also be used
    i.e. in this case you just have 'settled' and 'traded' to return 'A' but if you have say 'settled' to return 'A' and 'traded' to return 'B' and 'bought' to return 'C' you want to use a CASE statement.
    Last edited by macjoubert; 02-22-06 at 17:11.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what is this -- + SPACE(1) ???

    why the addition? you cannot add strings, you can only concatenate them, and in mysql you need the CONCAT function for that purpose

    also, you can't do this -- sourcecode = '35'

    i have a very strong feeling that you aren't using mysql, correct?

    i would advise using CASE in all, um, cases, because it is standard sql, whereas IF isn't

    for example, CASE will work in microsoft sql server, but IF won't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2006
    Posts
    4
    How do you suggest I code this then for it to work? this is what I have so far. And I am not using MYSQL. I am new to SQL...thanks

    SELECT sourcecode = '35',
    tran_#_mtf,
    pmf_udstr#3,
    CASE( tran_status_mtf = 'settled' or tran_status_mtf = 'traded' , 'A', tran_status_mtf) AS TRANS_STATUS_MTF,
    trantypetextmtf,
    cusip_mtf,
    qtyunitsl_s_hld,
    Broker_mtf = '888',
    tranprice_mtf,
    + SPACE (1),
    trade_date_mtf,
    settle_date_mtf,
    + SPACE (2),
    coupon_rate_mtf,
    ytm, stwcurrent,
    + SPACE (1),
    ai_pur_sold_mtf,
    sec_fee_mtf,
    + SPACE (2),
    commission_mtf,
    total_amt_mtf, + SPACE (2)
    from avivaholdings2_rpt where trade_date_mtf > CURRENT_TIMESTAMP -30 AND commission_mtf = '0'




    Server: Msg 170, Level 15, State 1, Line 4
    Line 4: Incorrect syntax near '='.

  7. #7
    Join Date
    Oct 2003
    Location
    Rhodesia
    Posts
    28
    The IF should work in either DB2 or MYSQL since both your arguments result in the same result set i.e. 'A' , anyways to use CASE in MYSQL try this

    SELECT sourcecode = '35',
    tran_#_mtf,
    pmf_udstr#3,
    CASE WHEN tran_status_mtf = 'settled' THEN 'A' WHEN tran_status_mtf = 'traded' THEN 'A' ELSE tran_status_mtf END AS TRANS_STATUS_MTF,
    trantypetextmtf,
    cusip_mtf,
    qtyunitsl_s_hld,
    Broker_mtf = '888',
    tranprice_mtf,
    + SPACE (1),
    trade_date_mtf,
    settle_date_mtf,
    + SPACE (2),
    coupon_rate_mtf,
    ytm, stwcurrent,
    + SPACE (1),
    ai_pur_sold_mtf,
    sec_fee_mtf,
    + SPACE (2),
    commission_mtf,
    total_amt_mtf, + SPACE (2)
    from avivaholdings2_rpt where trade_date_mtf > CURRENT_TIMESTAMP -30 AND commission_mtf = '0'

    What database are you using ?
    Last edited by macjoubert; 02-23-06 at 00:07.

  8. #8
    Join Date
    Feb 2006
    Posts
    4

    Sql Dates!!!!!!

    I have the following code..how do I make the date format MMDDYYYY???



    SELECT sourcecode = '35',
    tran_#_mtf,Sequence = '0',
    pmf_udstr#3 = 'AVAF70115102',
    CASE WHEN tran_status_mtf = 'settled' THEN 'A' WHEN tran_status_mtf = 'traded' THEN 'A' WHEN tran_status_mtf = 'canceled' THEN 'D' WHEN tran_status_mtf = 'revised' THEN 'C' ELSE tran_status_mtf END AS TRANS_STATUS_MTF,
    trantypetextmtf,
    cusip_mtf,
    qtyunitsl_s_hld = LTRIM(qtyunitsl_s_hld)+SPACE(7-LEN(LTRIM(qtyunitsl_s_hld))),
    Broker_mtf = '888',
    tranprice_mtf = LTRIM(tranprice_mtf)+SPACE(7-LEN(LTRIM(tranprice_mtf)))
    + SPACE (1), FXRate = '0',

    CONVERT(CHAR(10), trade_date_mtf, 101),
    CONVERT(CHAR(10), settle_date_mtf, 101),
    + SPACE (2),
    coupon_rate_mtf = LTRIM(coupon_rate_mtf)+SPACE(7-LEN(LTRIM(coupon_rate_mtf))),
    ytm = LTRIM(ytm)+SPACE(10-LEN(LTRIM(ytm))),
    stwcurrent,
    + SPACE (1),
    ai_pur_sold_mtf = LTRIM(ai_pur_sold_mtf)+SPACE(10-LEN(LTRIM(ai_pur_sold_mtf))),
    sec_fee_mtf,
    + SPACE (2),
    commission_mtf,
    CASE WHEN commission_mtf = '0' THEN 'Z' WHEN commission_mtf > '0' THEN 'T' ELSE commission_mtf END AS commission_MTF,
    total_amt_mtf = LTRIM(total_amt_mtf)+SPACE(8-LEN(LTRIM(total_amt_mtf))), + SPACE (2)
    from avivaholdings2_rpt where trade_date_mtf > CURRENT_TIMESTAMP -30

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you must do it in the db then look at the inbuilt MySQL function DATE_FORMAT(). However I would of thought that this would be best done inthe poresentational layer.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    rather than reply to a PM use the date_format function
    the prototype is:-
    dateformat(<mydatecolumn>,'<my date format specification>)

    to find details of the date specification I'd suggest RTFM'ing the function or failing WWGS (What would google say / search)

    using 'date_format' and mysql as the parameters gave upwards of 100 references some of which http://www.mysqlfreaks.com/statements/59.php looked relevant.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    WTF is the plus sign in front of SPACE(2)?

    you cannot use the plus sign to concatenate strings in mysql

    also, you cannot use the equals sign in a SELECT either

    i've seen this query before somewhere -- yeah, here

    are you sure you're using mysql?

    if not, please don't post in the mysql forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i tell you what, i'm going to merge your two threads and move them to the microsoft sql server forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Oct 2003
    Location
    Rhodesia
    Posts
    28
    Quote Originally Posted by r937
    WTF is the plus sign in front of SPACE(2)?

    you cannot use the plus sign to concatenate strings in mysql


    also, you cannot use the equals sign in a SELECT either
    Sorry but this is not true, you can indeed use an '=' in a select statement

    E.G.
    Select column1= '10' from table;

    will result in a boolean interpretation of column 1 values against '10', in other words, the result set will be 0 (for a no match) or 1 (for a match).

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    also, you cannot use the equals sign in a SELECT either
    In some versions of MySQL, you can't use an equal sign within a SELECT statement. In newer (4 and later) versions, an equal sign generates a comparison operation (except in InnoDB for some reason). In older (pre 3.1) versions, it assigned a column name like it does in SQL Server.

    Try this one in SQL Server (any version):
    Code:
    SELECT frog = name
       FROM dbo.sysobjects
    -PatP

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    mac, pat, thanks for straightening me out

    you are correct but buddy who posted in the mysql forum is almost certainly looking for the sql server effect, to assign a column alias

    it's so rare that i see someone actually using the mysql perversion, er, i mean, version which interprets the equality as a boolean expression, that it escaped my memory

    of course you can do that in mysql, but you shouldn't (you should use CASE instead)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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