SQL nightmare

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Shataken
    New Member
    • Jun 2007
    • 15
    #1

    SQL nightmare

    I am trying to convert an application writtten in Access to vb.net. Alot of the SQL statements are long. Can someone take a look at the following Access SQL statemnt and tell me what SQL statement would look like? This statement works in Access, but the INSERT INTO and FROM tables are the same???
    BTW,, A load number looks like this "########-A" or "########-B" etc.

    [CODE=sql]
    INSERT INTO Statements ( MachineID, ShipDate, InvoicedDate, OutgoingLoadNum , ShipAdd1, ShipAdd2, ShipCity, ShipState, ShipZip, ShipZipPlus4, BillAdd1, BillAdd2, BillCity, BillState, BillZip, BillZipPlus4, CustomerName, IsStatement )
    SELECT DISTINCT
    IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="A",DMin( 'MachineID','St atements')-1,
    IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="B",DMin( "[MachineID]","[Statements]")-2,
    IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="C",DMin( "[MachineID]","[Statements]")-3,
    IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="D",DMin( "[MachineID]","[Statements]")-4,
    IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="E",DMin( "[MachineID]","[Statements]")-5,
    IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="F",DMin( "[MachineID]","[Statements]")-6,
    IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="G",DMin( "[MachineID]","[Statements]")-7,
    IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="H",DMin( "[MachineID]","[Statements]")-8,
    IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="I",DMin( "[MachineID]","[Statements]")-9)))))))))
    AS MachID, Statements.Ship Date, Statements.Invo icedDate, Statements.Outg oingLoadNum, Statements.Ship Add1, Statements.Ship Add2, Statements.Ship City, Statements.Ship State, Statements.Ship Zip, Statements.Ship ZipPlus4, Statements.Bill Add1, Statements.Bill Add2, Statements.Bill City, Statements.Bill State, Statements.Bill Zip, Statements.Bill ZipPlus4, Statements.Cust omerName, -1 AS IsStatemnt
    FROM Statements;[/CODE]
    Tags: None
    • nico5038
      Recognized Expert Specialist
      • Nov 2006
      • 3080
      #2
      Originally posted by Shataken
      I am trying to convert an application writtten in Access to vb.net. Alot of the SQL statements are long. Can someone take a look at the following Access SQL statemnt and tell me what SQL statement would look like? This statement works in Access, but the INSERT INTO and FROM tables are the same???
      BTW,, A load number looks like this "########-A" or "########-B" etc.

      [CODE=sql]
      INSERT INTO Statements ( MachineID, ShipDate, InvoicedDate, OutgoingLoadNum , ShipAdd1, ShipAdd2, ShipCity, ShipState, ShipZip, ShipZipPlus4, BillAdd1, BillAdd2, BillCity, BillState, BillZip, BillZipPlus4, CustomerName, IsStatement )
      SELECT DISTINCT
      IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="A",DMin( 'MachineID','St atements')-1,
      IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="B",DMin( "[MachineID]","[Statements]")-2,
      IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="C",DMin( "[MachineID]","[Statements]")-3,
      IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="D",DMin( "[MachineID]","[Statements]")-4,
      IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="E",DMin( "[MachineID]","[Statements]")-5,
      IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="F",DMin( "[MachineID]","[Statements]")-6,
      IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="G",DMin( "[MachineID]","[Statements]")-7,
      IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="H",DMin( "[MachineID]","[Statements]")-8,
      IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="I",DMin( "[MachineID]","[Statements]")-9)))))))))
      AS MachID, Statements.Ship Date, Statements.Invo icedDate, Statements.Outg oingLoadNum, Statements.Ship Add1, Statements.Ship Add2, Statements.Ship City, Statements.Ship State, Statements.Ship Zip, Statements.Ship ZipPlus4, Statements.Bill Add1, Statements.Bill Add2, Statements.Bill City, Statements.Bill State, Statements.Bill Zip, Statements.Bill ZipPlus4, Statements.Cust omerName, -1 AS IsStatemnt
      FROM Statements;[/CODE]
      This nested IIF statements are checking for the character after the first "-" sign.
      When "A" the minimum MachineID from the table Statements is corrected with -1, when "B" with -2, etc.

      It would have been shorter to use the corretce ascii value of these characters :-)

      Nic;o)

        Comment

        Previous template Next
        Working...