Recursive CTE Multi-Level Explosion in Exact MAX

 

 

Below is a recursive CTE that does a multi-level explosion for the current effective date.

;with mlBOM

as

(

select  PARPRT_02, COMPRT_02

, QTYPER_02, EFFDTE_02

from Product_Structure

WHERE PARPRT_02 = 'ENTER PARENT PART'

union all -- CTE recursion

select  n.PARPRT_02, n.COMPRT_02

, n.QTYPER_02, N.EFFDTE_02

from Product_Structure n

inner join mlBOM c on c.COMPRT_02 = n.PARPRT_02

)

-- final select aggregating values

-- displays unique PARPRT_02 - COMPRT_02 combinations

select  PARPRT_02, COMPRT_02, QTYPER_02, EFFDTE_02

from mlBOM

WHERE EFFDTE_02 <= GETDATE() OR EFFDTE_02 IS NULL

group by  PARPRT_02, COMPRT_02, QTYPER_02, EFFDTE_02

;

Exact MAX Manufacturing System Training is available

Web: http://www.max.exactamerica.com

Training: https://training.exactamerica.com/

Portal: https://customers.exact.com

SQL 2005 Error Message when running DBCC CheckDB - Configuration option 'allow updates' changed from 0 to 0. Run the RECONFIGURE statement to install.

Then get:

 

Msg 5834, Level 16, State 1, Line 1
The affinity mask specified conflicts with the IO affinity mask specified. Use the override option to force this configuration.

 

Use this to fix it:

 

sp_configure 'Allow Updates', 0
RECONFIGURE WITH OVERRIDE

Subquery Inner Join with Date Manipulation

SELECT     SUM(COST_15 * RUNTTL_15) AS EXT
FROM         dbo.Transaction_History AS TH
INNER JOIN
(SELECT     PRTNUM_15 AS PART, STKID_15 AS STK, CAST(MAX(CAST(CONVERT(DATE, TNXDTE_15, 120) AS CHAR(10)) + ' ' + LEFT(TNXTIM_15, 2) + ':' + SUBSTRING(TNXTIM_15,
                       3, 2) + ':' + RIGHT(TNXTIM_15, 2)) AS DATETIME) AS DT
FROM         dbo.Transaction_History
WHERE     (TNXCDE_15 IN ('A', 'C', 'I', 'R', 'S', 'N', 'T', 'F')) AND TNXDTE_15 < '2012-12-01'
GROUP BY PRTNUM_15, STKID_15) AS TH1 ON TH.PRTNUM_15 = TH1.PART AND TH.STKID_15 = TH1.STK AND
CAST(CAST(CONVERT(DATE, TH.TNXDTE_15, 120) AS CHAR(10)) + ' ' + LEFT(TH.TNXTIM_15, 2) + ':' + SUBSTRING(TH.TNXTIM_15, 3, 2) + ':' + RIGHT(TH.TNXTIM_15, 2) AS DATETIME)
= TH1.DT

SQL Server 2008 Can’t see .bak files in Restore or Backup folders

To solve this you need to grant permissions in the folders to the service account you used to run the SQL Server services

 

Vincent Stefanetti

How to Pass Multi-Valued Parameters to another Report

Right click the cell you want to use to go to the other report

Select Text Box Properties

Select Action

Select Go to report

Specify a report and select the one you created that you want to go to.

Note:

The report you are going to needs to have the same settings as the parameters on the main report.

on the Go to report create the parameter with the same name as the main report

make it the parameter multi-valued

create a DISTINCT list data set for the multi-valued parameter to select from

i.e. SELECT DISTINCT COMCDE_01 FROM [TABLE OR VIEW NAME]

I also trimmed it to make it look better in the parameter drop down.

i.e. SELECT DISTINCT RTRIM(COMCDE_01) AS COMCCDE_02 FROM [TABLE OR VIEW NAME]

add the SQL IN statement in the Where cause i.e. WHERE COMCDE_01 IN (@COMCDE)

----

Click the Add buttom

the drop down will pickup the parameter from the Go to report

Click the Fx

Use =Split(Join(Parameters!COMCDE.Value, ","), ",") expression to create the multi-value parameter list that the Go to report will use.

Click OK

I had to set it up like this to make it work right for my DB

Hope this helps,

Vincent Stefanetti

Simple SQL Update with Join and Where Exists

UPDATE Part_Master
SET RECVEN_01 = VENID_10
from Part_Master as P INNER join Order_Master as O ON P.PRTNUM_01 = O.PRTNUM_10
WHERE EXISTS
(SELECT PRTNUM_10, VENID_10
FROM Part_Master as P join Order_Master as O ON P.PRTNUM_01 = O.PRTNUM_10
WHERE TYPE_01 = 'B' OR TYPE_01 = 'O'
GROUP BY PRTNUM_10, VENID_10)