Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Location
    Pennsylvania
    Posts
    4

    Unanswered: Creating SQL View - syntax is wrong

    We are creating a view in SQL to format data to DTS to another database. Here is the section of the view which does not work -- eliminating this section from the query allows the view to be created:

    CASE APTran.LineNbr
    WHEN < '0' THEN 'O'
    --Offsetting Document (or transaction line as we have (AP Liab. AP Cash Entry).
    WHEN > '0' THEN 'D'
    --Regular Document (or transaction line as we have (This is either a Debit or a Credit line not AP Liab. AP Cash Entry).
    ELSE 'E'
    END AS JED_DIST_OR_OFF,


    I am attaching the whole query stmt here.

    Thanks very much for all assistance.
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Is LineNbr numeric?

    --EDIT:

    Can you tell us the error?

    Might help
    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
    Sep 2003
    Location
    Pennsylvania
    Posts
    4
    The error received in Query analyzer is:
    Server: Msg 170, Level 15, State 1, Procedure xvr_03901EXPORTER, Line 107
    Line 107: Incorrect syntax near '<'.
    Server: Msg 170, Level 15, State 1, Procedure xvr_03901EXPORTER, Line 133
    Line 133: Incorrect syntax near 'APTran'.
    Server: Msg 170, Level 15, State 1, Procedure xvr_03901EXPORTER, Line 186
    Line 186: Incorrect syntax near 'APDoc'.

    The field aptran.linenbr is datatype smallint.

    Thanks, Dave Spangler

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Code:
       CASE 
          WHEN cast(APTran.LineNbr as int) < 0 THEN 'O'
          --Offsetting Document (or transaction line as we have (AP Liab. AP Cash Entry).
          WHEN cast(APTran.LineNbr as int) > 0 THEN 'D'
          --Regular Document (or transaction line as we have (This is either a Debit or a Credit line not AP Liab. AP Cash Entry).
          ELSE 'E'
       END AS JED_DIST_OR_OFF,
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hey, based on the code you submitted before I clicked on Post, I thought (here's that magic word again ) that it was a varchar field!

    Code:
       CASE 
          WHEN APTran.LineNbr < 0 THEN 'O'
          --Offsetting Document (or transaction line as we have (AP Liab. AP Cash Entry).
          WHEN APTran.LineNbr > 0 THEN 'D'
          --Regular Document (or transaction line as we have (This is either a Debit or a Credit line not AP Liab. AP Cash Entry).
          ELSE 'E'
       END AS JED_DIST_OR_OFF,
    And you probably need to test for NULL unless this is taken care of by the 'IsNullable' property of the field.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by rdjabarov
    Hey, based on the code you submitted before I clicked on Post, I thought (here's that magic word again ) that it was a varchar field!

    Code:
       CASE 
          WHEN APTran.LineNbr < 0 THEN 'O'
          --Offsetting Document (or transaction line as we have (AP Liab. AP Cash Entry).
          WHEN APTran.LineNbr > 0 THEN 'D'
          --Regular Document (or transaction line as we have (This is either a Debit or a Credit line not AP Liab. AP Cash Entry).
          ELSE 'E'
       END AS JED_DIST_OR_OFF,
    And you probably need to test for NULL unless this is taken care of by the 'IsNullable' property of the field.
    Hey, get your own ideas...

    And why would they have to check for Nulls Null would give them an "E"
    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.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Yup, forgot about ELSE
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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