Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2011
    Posts
    4

    Unanswered: conversion script problem

    Hello, just found this site. Was looking for an online resource that would be able to evaluate a sql script when I am out of options for a resolution ... yes, I am new and learning. Right now I have a need to convert some data from one system to another/SQL. I am getting the following message when I run the following script ... more explanation below ...

    Msg 102, Level 15, State 1, Line 13
    Incorrect syntax near '1'.

    USE FACTS

    insert AcclamareFACTS.dbo.SalesOrder
    select newid() as SalesOrderId,
    /*declare @custid uniqueidentifier
    set @custid = newid() where */
    newid() from (SELECT DISTINCT CUSTID FROM SalesOrder) as CustomerId,
    (SELECT WarehouseId FROM Warehouse where warehouseid = '1B03076D-328B-488F-8969-B9C73BAE7DDE') as WarehouseId,-- Inserted/used a warehouse GUID from Demo_SRCE
    (SELECT TermId FROM ARTerm where TermId = 'f2228be8-8211-4dbe-8625-7ec6e834f325') as TermId,
    (SELECT SalesforceId FROM salesforce where SalesforceId = '380bc433-710b-457e-ae42-5e39e5b5262') as SalesForceId,
    SODOCID as SalesOrderNumber,
    '1' as RevisionNumber,
    case
    when DOCTYPE = 'B' then 0
    when DOCTYPE = 'S' then 1
    when DOCTYPE = 'I' then 1
    when DOCTYPE = 'O' then 1
    else 0
    end as SalesOrderType,
    0 as IsShipComplete,
    0 as ShippingIsOutsideCityLimits,
    null as ShippingName
    null as LockId
    null as HoldNsf
    null as EstimatedTax
    null as HoldUserId
    null as SourceId
    null as BLDiscountId
    null as BLDiscount
    null as AllowBackorder
    null as ShippingBLDiscount
    null as ShippingSurcharge
    null as ShippingFreight
    null as ShippingFreeFreight
    null as ShippingHandlingFee
    null as ShippingInsuranceFee
    null as ShippingMinimumOrderCharge
    null as ShippingEstimatedTax
    null as InvoiceCount
    null as FieldSalesForceId
    null as FreightAllowanceId
    null as FreightChargeId
    null as OnlyPrintTotals
    null as QuoteId
    from sales_order

    I have brought data from a csv file into a table in SQL. The table is "sales_order". I need to get the information in this table into the appropriate fields of the AcclamareFACTS.dbo.SalesOrder table.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Looks like you are missing a lot of commas separating all the null fields. It's one place to start, anyway.

  3. #3
    Join Date
    Oct 2011
    Posts
    4

    yes, but

    Thanks, yes, in my example I was ... my bad ... but not in my script.

    Even with the commas in place ... same problem as described.

    Msg 102, Level 15, State 1, Line 13
    Incorrect syntax near '1'.

    stopping at line 13. If I change line 13 to a "SELECT" format as I did with the lines above line 13, then it would run and I would get stopped at the next line ???

    Any idea what "SET DATABASE CONTEXT" means? Maybe something to do with my problem?

    Thanks
    Last edited by Tom R; 10-28-11 at 11:52. Reason: provide additional information

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    (SELECT WarehouseId FROM Warehouse where warehouseid = '1B03076D-328B-488F-8969-B9C73BAE7DDE') as WarehouseId,-- Inserted/used a warehouse GUID from Demo_SRCE
    (SELECT TermId FROM ARTerm where TermId = 'f2228be8-8211-4dbe-8625-7ec6e834f325') as TermId,
    (SELECT SalesforceId FROM salesforce where SalesforceId = '380bc433-710b-457e-ae42-5e39e5b5262') as SalesForceId,
    I have to ask where you got this horrible code.
    "SELECT WarehouseId FROM Warehouse where warehouseid = '1B03076D-328B-488F-8969-B9C73BAE7DDE'" returns the same damn value you just supplied as a parameter.
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34

    RE: conversion script problem

    Typo, maybe?

    Code:
    USE FACTS
    GO
    
    INSERT AcclamareFACTS.dbo.SalesOrder
    SELECT
      newid() as SalesOrderId
     ,(SELECT DISTINCT CUSTID FROM SalesOrder) as CustomerId
     ,(SELECT WarehouseId FROM Warehouse where warehouseid = '1B03076D-328B-488F-8969-B9C73BAE7DDE') as WarehouseId -- Inserted/used a warehouse GUID from Demo_SRCE
     ,(SELECT TermId FROM ARTerm where TermId = 'f2228be8-8211-4dbe-8625-7ec6e834f325') as TermId
     ,(SELECT SalesforceId FROM salesforce where SalesforceId = '380bc433-710b-457e-ae42-5e39e5b5262') as SalesForceId
     ,SODOCID as SalesOrderNumber
     ,'1' as RevisionNumber
     ,case
        when DOCTYPE = 'B' then 0
        when DOCTYPE = 'S' then 1
        when DOCTYPE = 'I' then 1
        when DOCTYPE = 'O' then 1
        else 0
      end as SalesOrderType
     ,0 as IsShipComplete
     ,0 as ShippingIsOutsideCityLimits
     ,null as ShippingName
     ,null as LockId
     ,null as HoldNsf
     ,null as EstimatedTax
     ,null as HoldUserId
     ,null as SourceId
     ,null as BLDiscountId
     ,null as BLDiscount
     ,null as AllowBackorder
     ,null as ShippingBLDiscount
     ,null as ShippingSurcharge
     ,null as ShippingFreight
     ,null as ShippingFreeFreight
     ,null as ShippingHandlingFee
     ,null as ShippingInsuranceFee
     ,null as ShippingMinimumOrderCharge
     ,null as ShippingEstimatedTax	
     ,null as InvoiceCount
     ,null as FieldSalesForceId
     ,null as FreightAllowanceId
     ,null as FreightChargeId
     ,null as OnlyPrintTotals
     ,null as QuoteId	
    FROM sales_order

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Hmm...It may help if you post the original piece of code that is tossing the error. It could be that whatever the parser is complaining about is not in this version of the query.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You're still failing to see how pointless this subquery is:
    SELECT WarehouseId FROM Warehouse where warehouseid = '1B03076D-328B-488F-8969-B9C73BAE7DDE'
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Well, the original poster has not chimed back in since your post, but if it makes you feel better, I see how pointless that is, unless maybe the record does not even exist (but why it wouldn't after he put it specifically in the query, I am not sure). The original question was about a syntax error, though.

  9. #9
    Join Date
    Oct 2011
    Posts
    4

    conversion script

    Sorry, had meeting.

    Ok, to blindman's point, yes, I realize ... and no doubt I should not have included that code so as to unintentionally distract you from my question. So blindman, the reason for the "sensless" code is because I have been trying to figure out why I am getting this.

    Msg 102, Level 15, State 1, Line 13
    Incorrect syntax near '1'.

    I threw in the select statements (and senseless code) in an attempt to understand what is causing my syntax near '1' ... because before I had the senseless code, I was getting the same syntax Msg where you see the senseless code.

    The script is long. I've merely included part of it because I am getting the syntax error for each line where there is not a pointless subquery.

    Anyway, am fine with any observations either of you or others have. I can handle the comments; possibly all part of what it takes to get a solution.

    I am assuming that "SET DATABASE CONTEXT" has no meaning to either of you.

    Thanks

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    "SET DATABASE CONTEXT" has no meaning, because it does not appear in the query. I do not think that is a command in SQL Server. It is more common to use "USE dbname to switch database context. Normally folks put a "GO" between the USE statement and the query to ensure that object resolution happens after the switch, but you are not even getting that far.

  11. #11
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    The first rule in debugging a problem is to simplify the problem.

    Your query is returning dozens and dozens of columns.

    Simplify !!!

    Remove half the columns you are returning. Does the problem still exists? then the problem is somewhere in the columns you have remaining. If the problem went away, the problem is with the columns you previously removed.

    Keep on doing that until you have simplified the problem down to the smallest combination of factors.

    Then, if you still can't figure it out, post the simplified code and we will all have another look.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Copying your code verbatim:
    Code:
    USE FACTS
    
    insert AcclamareFACTS.dbo.SalesOrder
    select newid() as SalesOrderId,
    /*declare @custid uniqueidentifier
    set @custid = newid() where */
    newid() from (SELECT DISTINCT CUSTID FROM SalesOrder) as CustomerId, 
    (SELECT WarehouseId FROM Warehouse where warehouseid = '1B03076D-328B-488F-8969-B9C73BAE7DDE') as WarehouseId,-- Inserted/used a warehouse GUID from Demo_SRCE
    (SELECT TermId FROM ARTerm where TermId = 'f2228be8-8211-4dbe-8625-7ec6e834f325') as TermId,
    (SELECT SalesforceId FROM salesforce where SalesforceId = '380bc433-710b-457e-ae42-5e39e5b5262') as SalesForceId,
    SODOCID as SalesOrderNumber,
    '1' as RevisionNumber,
    case
    when DOCTYPE = 'B' then 0
    when DOCTYPE = 'S' then 1
    when DOCTYPE = 'I' then 1
    when DOCTYPE = 'O' then 1
    else 0
    end as SalesOrderType,
    0 as IsShipComplete,
    0 as ShippingIsOutsideCityLimits,
    null as ShippingName
    null as LockId
    null as HoldNsf
    null as EstimatedTax
    null as HoldUserId
    null as SourceId
    null as BLDiscountId
    null as BLDiscount
    null as AllowBackorder
    null as ShippingBLDiscount
    null as ShippingSurcharge
    null as ShippingFreight
    null as ShippingFreeFreight
    null as ShippingHandlingFee
    null as ShippingInsuranceFee
    null as ShippingMinimumOrderCharge
    null as ShippingEstimatedTax 
    null as InvoiceCount
    null as FieldSalesForceId
    null as FreightAllowanceId
    null as FreightChargeId
    null as OnlyPrintTotals
    null as QuoteId 
    from sales_order
    ...the red FROM is causing the error message.

    You probably need to look at other issues that have been suggested above, but this ought to get you past your original stumbling block and into more agressive debugging!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    Oct 2011
    Posts
    4

    thanks

    Ah ... indeed that was my problem. Thanks much ... I'm learning.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You have to understand, Tom, that when code has dozens of obvious and glaring errors, its harder to pick out the one that is your current stumbling block.
    That's why, when you post code like that, our inclination is to address the entire scripts and help you clean it up, rather than search for the cause of one error.
    As Practical said, post the simplest code that reproduces the error you are seeing. In deriving this code, you may well find the source of the issue yourself.
    If it's not practically useful, then it's practically useless.

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

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    blindman makes a great point... While we certainly CAN find problems like this, it is usually better to fix the underlying problem. I just thought it was worth a minute or two to solve your immediate problem before suggesting that you review the prior suggestions.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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