Professional Web Applications Themes

SQL conversion help needed - Microsoft SQL / MS SQL Server

Hello, I've just been given a horribly designed Access system to upsize to SQL Server. So far, it's been long, annoying exercises in sifting through spaghetti, but now I've got a query that is doing my head in! Can I have some suggestions as to what to do with this: select iif (isNull(Sale.ShippingFlag),Trim(Trim(Customer.Titl e) + ' ' + Trim(Customer.FirstName) + Trim(Customer.SurName)),Iif(IsNull(Sale.ShippingCo mpany),Sale.ContactID,Trim (Courier.ContactName))) FROM Sale INNER JOIN Customer ON Sale.CustID = Customer.CustID INNER JOIN Courier ON Sale.CourierID = Courier.CourierID WHERE Sale.SaleNO = [parSaleNo] (parSaleNo being a parameter that's passed to the query) This is a simple version - I've got ...

  1. #1

    Default SQL conversion help needed

    Hello,

    I've just been given a horribly designed Access system to upsize to SQL
    Server. So far, it's been long, annoying exercises in sifting through
    spaghetti, but now I've got a query that is doing my head in!

    Can I have some suggestions as to what to do with this:
    select iif (isNull(Sale.ShippingFlag),Trim(Trim(Customer.Titl e) + ' ' +
    Trim(Customer.FirstName) +
    Trim(Customer.SurName)),Iif(IsNull(Sale.ShippingCo mpany),Sale.ContactID,Trim
    (Courier.ContactName))) FROM Sale INNER JOIN Customer ON Sale.CustID =
    Customer.CustID INNER JOIN Courier ON Sale.CourierID = Courier.CourierID
    WHERE Sale.SaleNO = [parSaleNo]

    (parSaleNo being a parameter that's passed to the query)
    This is a simple version - I've got variants on this 'approach' that go on
    for about 50 lines in a 1024X768 resolution!

    The main questions I've got are:
    Can I use nested IIF's in SQL Server's select statements in the same way as
    Access?
    I know the difference between Access's IsNull and SQL's IsNull, but is there
    any way in SQL that I can do something like Access's? i.e. check one field,
    and then use one of two other fields if it's null? Case works to a point,
    but doesn't seem to be able to handle complex statements, or anything with
    more than one condition. Maybe I've got the manual upside down... :(
    Can I join fields together in SQL as above, and, if so, should I? Something
    tells me this is probably bad practice, but I can't put my finger on why.
    What do I do with all the 'Trim' statements, and can I use a 'trim'
    equivalent on another 'trim' as above?
    Where can I get a stiff drink? I've got another 800+ queries like this to go
    through!!! :o

    Thanks!!!

    SR



    Scott Guest

  2. #2

    Default Re: SQL conversion help needed

    Look up CASE (the closest thing to IIF) and COALESCE (the equivalent of Nz)
    in SQL Books Online (BOL). CASE expressions can be nested.

    HTH,
    Bob Barrows

    Scott R wrote: 
    Trim(Customer.SurName)),Iif(IsNull(Sale.ShippingCo mpany),Sale.ContactID,Trim 



    Bob Guest

  3. #3

    Default Re: SQL conversion help needed

    "Joe Celko" wrote in message 

    Why don't you post this in comp.databases-ms.access.
    No guts no glory:).


    Groucho Guest

  4. #4

    Default Re: SQL conversion help needed

    No, no - Groucho was challenging Joe to post his "Access stinks" statement
    in cdma. You posted your question in the right place.

    Bob

    Scott R wrote: 
    >>
    >> Why don't you post this in comp.databases-ms.access.
    >> No guts no glory:).[/ref][/ref]



    Bob Guest

  5. #5

    Default Re: SQL conversion help needed

    Oops. Well, thanks anyway! :)

    "Bob Barrows" <com> wrote in message
    news:phx.gbl... [/ref]
    >
    >
    >[/ref]


    Scott Guest

Similar Threads

  1. Conversion - CF 5 to CF MX 7
    By lco in forum Coldfusion - Getting Started
    Replies: 1
    Last Post: April 14th, 04:22 PM
  2. dxf conversion
    By ersc14 webforumsuser@macromedia.com in forum Macromedia Freehand
    Replies: 1
    Last Post: November 17th, 11:29 PM
  3. XML to CSV Conversion
    By Ravi in forum Perl / CGI
    Replies: 4
    Last Post: August 19th, 01:39 AM
  4. A bit OT: conversion mp3 to wav
    By vdemart@supereva.it in forum Debian
    Replies: 2
    Last Post: July 8th, 08:10 AM
  5. Help needed for TimeStamp conversion between MySQL and PHP
    By Andy Hassall in forum PHP Development
    Replies: 1
    Last Post: July 6th, 08:50 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139