Search results for query: *

Hello, Using a security trace tool and the GP resource (Window display and technical names), I have a main GP Window Description table with the Series, Product, window_display_name, window_technical_name, RESID and USRCLASS. I use this with an outer left join on the RESID to the...

Hello, My automated process pulls shipment information and updates Great Plains. Via a trigger on my custom import table, the order is updated with the Freight Amount: if exists(SELECT 1 FROM [dbo].[TBZ_Shipments_Import] INNER JOIN [dbo].[SOP10100] ON...

Hello, I've been trying to handle this on the back end (see post "Duplicate Key Error in SOP10106"). Because I need to always have an initial insert, a VBA solution seems to be what's needed. Basically, I just need to to have the User Defined window open and have a default (static) value...

Hello, I've been researching, and cannot find a way to pass a value from one table as an input parameter into another. So, Table1 has an invoice number, and based on this Table2 would be populated with the items for that invoice. I guess you could call this a cascading parameter, except I would...

OK, this one has me pulling what little hair I have left OUT for the past two days. If someone can offer a solution I would be most obliged. OK, so I have this trigger on the SOP10100 table which fires on inserted: CREATE TRIGGER [dbo].[trig_updateUserDefined] ON [dbo].[SOP10100] FOR INSERT...

Hello, However it happens, either by Update or Insert, when a Back Order is transferred to an Order, an SOPNUMBE of soptype = 2 (order) is being inserted into the SOP10106 (user defined table) as a new record, or the Back Order is updating to an Order retaining the comments, as does an Order...

Hello, I have a DTS package which basically imports the contents of a flat file into a table. The problem for me is, by default it is appending to the current records, and I need it to only store the records we're bring in at that time. I'm already archiving the records anyway, but I need the...

Hello, Is it possible to auto increment a number in an insert trigger. For instance: ALTER TRIGGER [dbo].[trig_insertFreightLine] ON dbo.Test_Shipments_Import FOR INSERT, UPDATE, DELETE AS if exists (select * from inserted) begin insert SOP10102 select distinct...

Hello, I have this trigger which inserts a line for a freight amount into another table, but I need to update two fields with the same value. This value for the freight amount only comes in as one column from the source table. Is there a way to populate the second field with that value? The...

Hello, I have a DTS Package which connects to an FTP server, pulls and process a file. I am having a challenge with deleting the file on the FTP server after I pull it. is this possible from the DTS package? Is it an FTP setting? I confirmed that I have permissions to delete files from the FTP...

Hello, This select is used to populate a drop-down in a report, showing the cust# and name concatenated and is used as an input parameter for a stored proc: SELECT DISTINCT [CUSTOMER] = LTRIM(RTRIM(RM00101.CUSTNMBR)) + ' - ' + LTRIM(RTRIM(RM00101.CUSTNAME)) FROM RM00101 WHERE...

Hello, I have this stored proc which selects customers with a 60-day bal who are not on hold which works fine, but now I need to turn this same proc into an update. I removed the groupings and inserted the update statement as you can see which is commented below, but is this the correct way to...

Hello, I'm trying to Use an initial CASE statement, then if that condition is satisfied, go into the next CASE statement: [DISC WARNING] = CASE ACTNUMBR_1 WHEN '4211' THEN (SOP10102.CRDTAMNT / 2) CASE (SOP10102.CRDTAMNT / 2) <> 50.0 THEN (SOP10102.CRDTAMNT / 2) ELSE null END END Can...

Hello, I'm trying to run this update (testing with the select statement first) with a group by and keep getting "An aggregate may not appear in the set list of an UPDATE statement": An aggregate may not appear in the set list of an UPDATE statement: update SY00500 set NUMOFTRX = count...

Hello, this stored proc runs fine, but I'm not sure how to join it with another query/join I need in the result: Here is what I have now: ALTER PROCEDURE [dbo].[udsp_DR_Customer_Payment_History] ( @customer varchar(32), @checkNumber varchar(32) ) AS BEGIN SELECT [Customer]...

Hello, In my DTS Package, I have this Visual Basic ActiveX Script, I use for testing to make sure the FTP operation was successful. I'd like to keep it inthe DTS package for future testing, but don't want the msgbox to pop up when running as a scheduled task in production. Is there a way to...

Hello, I am trying to add a where clause to make sure I have unique tracking numbers going in. But where I try to add a check in my where clause, it complains with: "The column prefix 'SOP10107' does not match with a table name or alias name used in the query." Here is the trigger with...

Hello, This is an excerpt from my stored proc, which I use to build a SQL string with and execute. I have an input parameter, @checkNumber varchar(21) = NULL, and I'm trying to get it into this SQL statement like I have with others, but because I'm building a SQL string it doesn't like the...

Hello, I have the following sql statement in my stored proc to handle a divide by zero error: [DISCOUNT %] = CASE SUM(SOP10100.SUBTOTAL) WHEN 0 THEN 0 ELSE CAST(ROUND(SUM(SOP10100.TRDISAMT) / SUM(SOP10100.SUBTOTAL) * 100.0,2) AS NUMERIC (19, 2 )) END, But now I also need to use in this...

Hello, I have this percentage calc for fields in my stored proc: [DISCOUNT %] = CAST(ROUND (SOP10100.TRDISAMT / SOP10200. XTNDPRCE * 100.0,2) AS NUMERIC (19, 2 )), [DISC WARNING] = CASE WHEN CAST(ROUND(SOP10100 .TRDISAMT / SOP10200.XTNDPRCE * 100.0,2) AS NUMERIC (19, 2)) <> 50.00 THEN...