Query works in SQL Manager, but not in SC

Applications SQL Builder
, , , ,
#1

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:

[SIZE=12px]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.[/SIZE]

Thanks for looking…comments welcomed…Help is very appreciated

:slight_smile:

#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

#3

As Kdriscoll wrote before me, create a view in your db.
Then in SC you just need a very simple

select * from viewname
#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.[SIZE=12px] [/SIZE]

[SIZE=12px]Invalid object name ‘AAsales_order’[/SIZE]

#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?

#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?

#7

Yes, I tried Select * from [SIZE=12px]AAsales_order. and looks like it has accepted my pleas. Now to get it formatted correctly. Thanks all![/SIZE]

#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 [SIZE=12px]Tipo de aplicaci?n:[/SIZE] Grid [SIZE=12px]Estado:[/SIZE] Ok

but when I try to execute, this is the result: [TABLE=“class: scErrorTable, align: center, cellpadding: 0, cellspacing: 0, width: 320, height: 30”]
[TR]
[TD=“class: scErrorTitle, align: left”]Error[/TD]
[/TR]
[TR]
[TD=“class: scErrorMessage, align: center”]Se produjo un error al acceder a la base de datos

		SelectLimit(SELECT as codigo, as nombre, as estado from b_personal , 12, 0)[/TD]
	[/TR]
 [/TABLE]

Could you help me!

#9

Never mind, for unknown reason is working now. hehehe :slight_smile: