Announcement

Collapse
No announcement yet.

Query works in SQL Manager, but not in SC

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Query works in SQL Manager, but not in SC

    Hi Everyone,
    I'm trying to put a online version of our sales order out on our intranet. We usually access the information through Crystal, so I thought to take the sql from there make some adjustments and thank you Ma'am. Anyway out the gate sc says no go.

    Here's the query;

    SELECT "SO_Master"."ORDDTE_27", "SO_Master"."ORDNUM_27", "SO_Master"."COMNT1_27", "SO_Master"."COMNT2_27", "SO_Master"."CUSTID_27", "SO_Master"."COMNT3_27", "SO_Master"."ORDID_27"
    , "Sales_Rep_Master"."SLSNME_26", "SO_Master"."FOB_27", "Customer_Master"."DSCRTE_23", "SO_Master"."COMMIS_27", "SO_Master"."CUSTPO_27", "SO_Master"."TERMS_27", "SO_Master"."NAME_27"
    , "SO_Master"."ADDR1_27", "SO_Master"."ADDR2_27", "SO_Master"."CITY_27", "SO_Master"."STATE_27", "SO_Master"."ZIPCD_27", "SO_Master"."CNTRY_27", "Customer_Master"."NAME_23"
    , "Customer_Master"."ADDR1_23", "Customer_Master"."ADDR2_23", "Customer_Master"."CITY_23", "Customer_Master"."STATE_23", "Customer_Master"."ZIPCD_23", "Customer_Master"."CNTRY_23"
    , "Customer_Master"."ADDR3_23", "Customer_Master"."ADDR4_23", "SO_Master"."ADDR3_27", "SO_Master"."ADDR4_27", "SO_Master"."ADDR5_27", "Customer_Master"."ADDR5_23", "SO_Master"."STYPE_27"
    , "SO_Master"."REP1_27", "SO_Detail"."LINNUM_28", "SO_Detail"."DELNUM_28", "Part_Sales"."PMDES1_29", "SO_Detail"."PRICE_28", "SO_Detail"."CURQTY_28", "SO_Detail"."PRTNUM_28"
    , "SO_Detail"."SLSUOM_28", "SO_Detail"."CURDUE_28", "SO_Master"."SHPVIA_27", "SO_Detail"."ORDNUM_28"
    FROM ((("GNCMAX5"."dbo"."SO_Master" "SO_Master" LEFT OUTER JOIN "GNCMAX5"."dbo"."Customer_Master" "Customer_Master" ON "SO_Master"."CUSTID_27"="Customer_Master"."CUS TID_ 23") LEFT OUTER JOIN "GNCMAX5"."dbo"."Sales_Rep_Master" "Sales_Rep_Master" ON "SO_Master"."REP1_27"="Sales_Rep_Master"."SLSR EP_2 6") LEFT OUTER JOIN "GNCMAX5"."dbo"."SO_Detail" "SO_Detail" ON "SO_Master"."ORDNUM_27"="SO_Detail"."ORDNUM_28 ") LEFT OUTER JOIN "GNCMAX5"."dbo"."Part_Sales" "Part_Sales" ON "SO_Detail"."PRTNUM_28"="Part_Sales"."PRTNUM_2 9"
    WHERE "SO_Master"."ORDNUM_27"='20084224'
    ORDER BY "SO_Master"."ORDNUM_27", "SO_Detail"."LINNUM_28", "SO_Detail"."DELNUM_28"



    This is the error I get:

    Incorrect syntax near 'GNCMAX5'.

    At first I thought sc didn't like the DB name on everything or the duplication of the table names so I got rid if them. Error just kept moving farther down the line.


    Thanks for looking....comments welcomed.....Help is very appreciated

    Last edited by onyxtacular; 03-04-2016, 06:03 PM.
    HA! We're all just a little crazy.

  • #2
    If I were you I would create a view based on your side
    And use that for Scriptcase
    It gets confused my joins

    My 2 cents

    Kevin

    Comment


    • #3
      As Kdriscoll wrote before me, create a view in your db.
      Then in SC you just need a very simple
      Code:
      select * from viewname

      Comment


      • #4
        I created a view in SQL Manager and it accesses fine while in SQL Manager, but when I put the command SQL builder and get this:

        An error occurred while accessing the database.

        Invalid object name 'AAsales_order'


        Last edited by onyxtacular; 03-05-2016, 09:36 PM.
        HA! We're all just a little crazy.

        Comment


        • #5
          I wonder if it has something to do with the database is MSSQL. I get an ambiguous field error if I do not specify the table name sometimes
          Error while accessing the database:
          Ambiguous column name 'PRTNUM_06'.
          select PRTNUM_06, PRTNUM_06 from dbo.Part_Stock where PRTNUM_06 = '' order by PRTNUM_06

          Does anyone connect to a MSSQL DB? What connection type do you use?

          HA! We're all just a little crazy.

          Comment


          • #6
            I don't use MSSQL but once in a DB engine you have "hidden" all table names in a view, in SC all you need to point to is the view name.
            Did you try to use "select * from viewname" in the SQL select statement setting of a SC grid app?
            Last edited by robydago; 03-06-2016, 04:56 PM.

            Comment


            • #7
              Originally posted by robydago View Post
              I don't use MSSQL but once in a DB engine you have "hidden" all table names in a view, in SC all you need to point to is the view name.
              Did you try to use "select * from viewname" in the SQL select statement setting of a SC grid app?

              Yes, I tried Select * from AAsales_order. and looks like it has accepted my pleas. Now to get it formatted correctly. Thanks all!
              HA! We're all just a little crazy.

              Comment


              • #8
                I created a view:

                "CREATE OR REPLACE VIEW b_personal as SELECT
                CODIGO,
                CONCAT_WS(' ',
                PNOMBRE,
                SNOMBRE,
                TNOMBRE,
                APELLIDOP,
                APELLIDOM,
                APELLIDOC) as NOMBRE,
                ESTADO
                from personal"

                then create a grid with this query:
                "
                Select CODIGO,
                NOMBRE,
                ESTADO
                from
                b_personal
                "
                No problem when I compile it
                b_personal Tipo de aplicación: Grid Estado: Ok

                but when I try to execute, this is the result:
                Error
                Se produjo un error al acceder a la base de datos

                SelectLimit(SELECT as codigo, as nombre, as estado from b_personal , 12, 0)

                Could you help me!
                Last edited by apolo512; 04-22-2016, 02:43 PM.

                Comment


                • #9
                  Originally posted by apolo512 View Post
                  I created a view:

                  "CREATE OR REPLACE VIEW b_personal as SELECT
                  CODIGO,
                  CONCAT_WS(' ',
                  PNOMBRE,
                  SNOMBRE,
                  TNOMBRE,
                  APELLIDOP,
                  APELLIDOM,
                  APELLIDOC) as NOMBRE,
                  ESTADO
                  from personal"

                  then create a grid with this query:
                  "
                  Select CODIGO,
                  NOMBRE,
                  ESTADO
                  from
                  b_personal
                  "
                  No problem when I compile it
                  b_personal Tipo de aplicación: Grid Estado: Ok

                  but when I try to execute, this is the result:
                  Error
                  Se produjo un error al acceder a la base de datos

                  SelectLimit(SELECT as codigo, as nombre, as estado from b_personal , 12, 0)

                  Could you help me!
                  Never mind, for unknown reason is working now. hehehe

                  Comment

                  Working...
                  X