Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • odbc command



    Hi Statalist,

    The following lines runs perfectly:

    Code:
    odbc load, exec("SELECT NDRLD_View.dbo.xvw_tblPractice.PracticeLSPN FROM (NDRLD_View.dbo.xvw_tblPractice INNER JOIN NDRLD_View.dbo.xvw_tblPracticeUser ON NDRLD_View.dbo.xvw_tblPractice.PracticeID = NDRLD_View.dbo.xvw_tblPracticeUser.PracticeID)") dsn("NDRLD_View")

    But when I break it to two lines I get error messages which is "unmatched quote":

    Code:
    odbc load, exec("SELECT NDRLD_View.dbo.xvw_tblPractice.PracticeLSPN FROM ///
    (NDRLD_View.dbo.xvw_tblPractice INNER JOIN NDRLD_View.dbo.xvw_tblPracticeUser ON NDRLD_View.dbo.xvw_tblPractice.PracticeID = NDRLD_View.dbo.xvw_tblPracticeUser.PracticeID)") dsn("NDRLD_View")


    Could you please let me know how I can break it to two lines or more? I need to run a very long codes inside "exec".


  • #2
    I wonder whether you did use this code from a do file. Also, make sure the parenthesis is with the previous parcel of the code.
    Best regards,

    Marcos

    Comment


    • #3
      Thanks for the reply.

      Yes I'm using a do file and the parenthesis is correct too.

      Comment


      • #4
        The problem is that you are attempting to continue a string across two lines, this is not supported by the line continuation operator (although I cannot find support for that assertion in the Stata documentation I've looked at thus far).

        I think your better approach would be to create the long lines as a local macro separately from the exec command.
        Code:
        . local odcmd ///
        > "SELECT NDRLD_View.dbo.xvw_tblPractice.PracticeLSPN FROM " ///
        > "(NDRLD_View.dbo.xvw_tblPractice INNER JOIN NDRLD_View.dbo.xvw_tblPracticeUser ON " /// 
        > "NDRLD_View.dbo.xvw_tblPractice.PracticeID = NDRLD_View.dbo.xvw_tblPracticeUser.PracticeID)"
        
        . display "`odcmd'"
        SELECT NDRLD_View.dbo.xvw_tblPractice.PracticeLSPN FROM (NDRLD_View.dbo.xvw_tblPractice INNER JO
        > IN NDRLD_View.dbo.xvw_tblPracticeUser ON NDRLD_View.dbo.xvw_tblPractice.PracticeID = NDRLD_Vie
        > w.dbo.xvw_tblPracticeUser.PracticeID)
        
        . odbc load, exec("`odcmd'") dsn("NDRLD_View")

        Comment


        • #5


          I ran the following codes:

          Code:
          local odcmd ///
          "SELECT NDRLD_View.dbo.xvw_tblPractice.PracticeLSPN FROM " ///
          "(NDRLD_View.dbo.xvw_tblPractice INNER JOIN NDRLD_View.dbo.xvw_tblPracticeUser ON " ///
          "NDRLD_View.dbo.xvw_tblPractice.PracticeID = NDRLD_View.dbo.xvw_tblPracticeUser.PracticeID)"
          display "`odcmd'"
          odbc load, exec("`odcmd'") dsn("NDRLD_View")
          Then I got

          Code:
          . display "`odcmd'"
          
          
          SELECT NDRLD_View.dbo.xvw_tblPractice.PracticeLSPN FROM (NDRLD_View.dbo.xvw_tblPra
          > ctice INNER JOIN NDRLD_View.dbo.xvw_tblPracticeUser ON NDRLD_View.dbo.xvw_tblPra
          > ctice.PracticeID = NDRLD_View.dbo.xvw_tblPracticeUser.PracticeID)
          
          . odbc load, exec("`odcmd'") dsn("NDRLD_View") 
          table() or exec() is required
          r(198);



          Comment


          • #6
            You typed your three commands into your do file, but then you highlighted the first two of them and ran them, and then highlighted the third command and ran it.. That will not work when you are using local macros. When running a part of a do-file, Stata creates a temporary do file containing the selected line(s) and then runs the temporary do-file. Since local macros are - as their name suggests - local to the do-file within which they are created, the local macro odcmd vanished as soon as the first temporary do-file was exited, so the command you tried to run was effectively
            Code:
            odbc load, exec("") dsn("NDRLD_View")
            which is consistent with the error message Stata gave you.

            Comment


            • #7
              I selected all lines and ran them.

              I selected

              Code:
              local odcmd ///
              "SELECT NDRLD_View.dbo.xvw_tblPractice.PracticeLSPN FROM " ///
              "(NDRLD_View.dbo.xvw_tblPractice INNER JOIN NDRLD_View.dbo.xvw_tblPracticeUser ON " ///
              "NDRLD_View.dbo.xvw_tblPractice.PracticeID = NDRLD_View.dbo.xvw_tblPracticeUser.PracticeID)"
              display "`odcmd'"
              odbc load, exec("`odcmd'") dsn("NDRLD_View")
              the result was:

              Code:
              . local odcmd ///
              > "SELECT NDRLD_View.dbo.xvw_tblPractice.PracticeLSPN FROM " ///
              > "(NDRLD_View.dbo.xvw_tblPractice INNER JOIN NDRLD_View.dbo.xvw_tblPracticeUser ON " ///
              > "NDRLD_View.dbo.xvw_tblPractice.PracticeID = NDRLD_View.dbo.xvw_tblPracticeUser.PracticeID)"
              
              . display "`odcmd'"
              SELECT NDRLD_View.dbo.xvw_tblPractice.PracticeLSPN FROM (NDRLD_View.dbo.xvw_tblPractice INNER JOIN
              >  NDRLD_View.dbo.xvw_tblPracticeUser ON NDRLD_View.dbo.xvw_tblPractice.PracticeID = NDRLD_View.db
              > o.xvw_tblPracticeUser.PracticeID)
              
              . odbc load, exec("`odcmd'") dsn("NDRLD_View") 
              table() or exec() is required
              r(198);


              Comment


              • #8
                Maybe try getting rid of the parentheses from your SQL statement.

                Comment


                • #9
                  Thanks for all replies. I finally got it. The following works perfectly:

                  Code:
                  #delimit ;
                  local odcmd
                  "SELECT NDRLD_View.dbo.xvw_tblPractice.PracticeLSPN FROM
                  (NDRLD_View.dbo.xvw_tblPractice INNER JOIN NDRLD_View.dbo.xvw_tblPracticeUser ON
                  NDRLD_View.dbo.xvw_tblPractice.PracticeID = NDRLD_View.dbo.xvw_tblPracticeUser.PracticeID)";
                  #delimit cr
                  odbc load, exec("`odcmd'") dsn("NDRLD_View")

                  Comment


                  • #10
                    The following will work perfectly, too.
                    Code:
                    local odcmd ///
                        SELECT NDRLD_View.dbo.xvw_tblPractice.PracticeLSPN FROM ///
                            NDRLD_View.dbo.xvw_tblPractice INNER JOIN NDRLD_View.dbo.xvw_tblPracticeUser ON ///
                            NDRLD_View.dbo.xvw_tblPractice.PracticeID = NDRLD_View.dbo.xvw_tblPracticeUser.PracticeID
                    odbc load, exec("`odcmd';") dsn(NDRLD_View)
                    As will this
                    Code:
                    local db NDRLD_View.dbo.xvw_tbl
                    local sql_statement ///
                        SELECT A.PracticeLSPN FROM `db'Practice AS A ///
                            INNER JOIN `db'PracticeUser AS B ///
                            ON A.PracticeID = B.PracticeID;
                    odbc load, exec("`sql_statement'"), dsn(NDRLD_View)
                    You might want to talk with your DBA or developers about naming conventions. It will save you a lot of unnecessary typing and debugging grief. As they say: Practice, Practice, Practice.

                    Comment


                    • #11
                      An easier approach is to change the end of line delimiter with #d ; . For some reason what gets passed to the exec option seems to be handled a bit differently than other commands (with respect to line breaking), but changing the end of line delimiter prior to calling the command and terminating the command with a semicolon seems to do the trick when I’ve run into this issue.

                      Comment

                      Working...
                      X