Can someone test something in AA for MS SQL?

[Alan Lee]

I recently upgraded from a Win 7 machine to Win 10, upgraded from an older version of AA to the current version and upgraded from an old SQL ODBC driver to a new one. Many changes and thus many variables.

After all of the above I have a few things that no longer work and I am stumped as to why and am looking for someone with a MS SQL server backend to test something.

I have many passive link tables that use a custom query to retrieve data. These will refresh but will not allow you to successfully edit the data source as it will give me various error messages that rang from :"not connected to data source" or "invalid syntax". Once again, I can right click and refresh without issue.

The test:

1. Create a passive table with a simple select statement like below using native syntax for MS SQL server. I just need to group by something and sum a number. This works for me.

select item,
sum(qty_shipped) as total_qty
from sales_table
group by item


2. Modify the passive table above to the below. This does not work in the Edit Data Source but works every where else I test it in AA like the SQL command window and every other place I can run a query. Apparently AA does not like the embedded case statement.

select item,
sum(case when qty_shipped<100 then qty_shipped else 0 end) as total_qty_lt_100,
sum(case when qty_shipped>=100 then qty_shipped else 0 end) as total_qty_ge_100,
from sales_table
group by item

Interestingly enough i found this command a5_passiveLinkTableDefine() and i can create the above but it will not let me edit it. The problem with that is it will not let you run a5_passiveLinkTableDefine() using the same table name as it knows the table already exists so you have to delete the table to make change which means you loose any operation, report, etc that you created using the original table you created.

Thanks in advance.

[Al Buchholz]

Create a set with only the passive linked table. Put any objects - queries, reports, operations in the set.

That's been SOP/best practice for a long time.

[Ted Giles]

Suggest you submit a bug report.
Also, when I had a big M/s update today, I was locked IN to a running app which Icouldn't close.
You could always try duplicating the table and all the attachments and fiddle with that as you will still have the original.

[Ted Giles]

@Al.
Does that apply to Active Link tables too?

[Alan Lee]

i submitted a bug report. the query I am asking someone to test works everywhere except in the create/edit passive table. I can can even preview the result set after I edit but it will not allow me to save it after i previewed. This used to work without an issue and have done it many times and have tables that i copied from my old laptop that have this logic in and I can refresh them i just can't save them after I edit them.

[Ted Giles]

Look at the documentation about Passive Link Tables. There are options there for editing the scrips.

[Alan Lee]

I can't find anything. If I could find an Alpha command that would allow you to edit an existing passive table via Xbasic I would be OK. I found how to create one but it does not allow you to edit an existing one.

[Al Buchholz]

I build most passive linked tables with an import operation using AlphaDAO.

Then you can modify the operation or create xbasic to use in a script or User Defined Function.

Gives me a lot more options of how to modify what is happening.

[Alan Lee]

Can you try to build and edit one with a case statement like I mentioned above? It doesn't like the embedded case statement now for some reason.

How long does it usually take to get a reply on a bug report?

[Alan Lee]

AlphaDAO import does the same thing.

When I paste the below query in the select records I can preview records and it works but when I click OK I get the error.

select item_num,
sum(case when qty_shipped<100 then Qty_Shipped else 0 end) as total_qty_lt_100,
sum(case when qty_shipped>=100 then Qty_Shipped else 0 end) as total_qty_ge_100
from Sales_Transaction_Fact
where item_num in ('primpof111','primpof4612') and Invoice_Date_Key between 20200101 and 20200131
group by Item_Num

sql_preview.pngsql_error.png

[madtowng]

Now that we can see the error being supplied by sql server, it would help to see the actual sql code.

[Alan Lee]

Hmmmm, I think I have found a work around using AlphaDAO import. I build most of the import using the genie, dump it out as Xbasic, paste my query in the SQLSelect statement, run the script once to create passive table. You can right click to refresh. You can edit the SQLSelect in the script and rerun the script to alter the passive table. Once the table matches the SQLSelect then just right click to refresh or refresh via Xbasic. In a way a pain but in another way better because the SQL is easier to read in script than in the Source Editor. I'm going to tinker with it a little more but I think it's usable.

[Alan Lee]

Below is the script that works. SQL is in it.

dim connectionString as c
connectionString = "::Name::dwp"

delete options
dim options as p
options.ConsolidateArguments= .t.
options.AddTablesToDatabase= .t.
options.ShowProgress= .t.
options.AllowCancel= .t.


delete a_import
dim a_import[0] as p


a_import[].ObjectName = "<Custom SQL Query>"
a_import[..].SQLSelectStatement = <<%sql%
SELECT
stf.Source_Id,
stf.Company_Num,
stf.Customer_Num,
cx.Customer_Name,
cx.Credit_hold_flag,
cx.Sales_Person_1_Key,
spd.Sales_Person_Name,
stf.Ship_To_Address_Key,
cad.address_type,
cad.Ship_To_Name,
cad.Ship_To_Num,
cad.Address_1,
cad.Address_2,
cad.Address_3,
cad.City,
cad.State,
cad.Zip,
Min(stf.Invoice_Date_Key) AS dt_first_sale,
Max(stf.Invoice_Date_Key) AS dt_last_sale,
Sum(case when dd.year_num=2018 then stf.Extended_Price else 0 end) AS sales_2018,
Sum(case when dd.year_num=2018 then (stf.Extended_Price - stf.Extended_Cost)else 0 end) AS gp_2018,
Sum(case when dd.year_num=2019 then stf.Extended_Price else 0 end) AS sales_2019,
Sum(case when dd.year_num=2019 then (stf.Extended_Price - stf.Extended_Cost)else 0 end) AS gp_2019,
Sum(case when dd.year_num=2020 then stf.Extended_Price else 0 end) AS sales_2020,
Sum(case when dd.year_num=2020 then (stf.Extended_Price - stf.Extended_Cost)else 0 end) AS gp_2020
FROM Sales_Transaction_Fact as stf
LEFT OUTER JOIN Customer_Xref as cx
on stf.source_id=cx.source_id and stf.company_num=cx.company_num and stf.customer_num=cx.customer_num
LEFT OUTER JOIN Sales_Person_Dim as spd
ON cx.Sales_Person_1_Key = spd.Sales_Person_Key
LEFT OUTER JOIN Customer_Address_Dim as cad
ON stf.Ship_To_Address_Key = cad.Customer_Address_Key
LEFT OUTER JOIN Company_Dim as cd
ON stf.Company_Key = cd.Company_Key
INNER JOIN Date_Dim as dd
ON stf.Invoice_Date_Key = dd.Date_Key
where stf.Source_Id='aplus'
GROUP BY
stf.Source_Id,
stf.Company_Num,
stf.Customer_Num,
cx.Customer_Name,
cx.Credit_hold_flag,
cx.Sales_Person_1_Key,
spd.Sales_Person_Name,
stf.Ship_To_Address_Key,
cad.address_type,
cad.Ship_To_Name,
cad.Ship_To_Num,
cad.Address_1,
cad.Address_2,
cad.Address_3,
cad.City,
cad.State,
cad.Zip
%sql%
a_import[..].SQLType = "Native"
a_import[..].Arguments = ""
a_import[..].ImportType = "Create passive-link table"
a_import[..].LocalTableName = a5.get_path() + chr(92) + "sql_test_4"+".dbf"
a_import[..].LocalTableFieldDef = ""
a_import[..].OverwriteOption = "Overwrite without prompting"


dim flagSilent as l
delete args
dim args as sql::arguments
flagSilent = .f.
delete p
dim p as p

p = a5_AlphaDAO_Import(connectionString,a_import,options,flagSilent,args)
'p has an .hasError and .errorText property
'a_import[] has the following properties for each item: .countRecordsImported, .hasError, .errorText, .userCancelled

[Alan Lee]

I am pretty sure that message is not valid because i execute the query in a number of places, SSMS for sure, and MS SQL executes it without hesitation. I am 99.9% sure the query or the server are not the problem.

Thanks!!

[madtowng]

Sorry about that...I was hoping to see the code under the error.
With that said, are there any rows with null values in the column(s) you're using in the statement.
I can experiment, but it would really be helpful to have your data, and preferably the create table
definition for the table involved.