0
votes

La vue SQL ne semble pas complètement abstraire les données de l'original?

Une vue a été créée pour extraire des données et les utiliser dans plusieurs rapports, vwRPWIP_POST.

    FROM
        #vwRPWIP_POST wp

Elle renvoie les données attendues:

Le problème est que lorsque les gens entrent des caractères alphabétiques dans la colonne MReference , en particulier les trois premiers caractères, SQL renvoie une erreur lorsque ces caractères alphabétiques sont comparés à un julien date (par exemple 203).

MReference contient généralement des dates juliennes avec un autre désignateur alphabétique et ressemble généralement à 203-MU mais continuera à être autorisé à contenir des données incriminées. Lorsqu'il y a des données offensantes, ces enregistrements seront simplement supprimés du rapport.

En utilisant T-SQL LEFT , je compare 203 à la date julienne d'aujourd'hui, fais un calcul et basé sur ce résultat construit un rapport des enregistrements appropriés. Parfois, MReference contient des chaînes comme "DUMMY", "SPACERS", "RED-MU", etc.

J'ai donc retravaillé vwRPWIP_POST uniquement inclure les enregistrements où la première colonne de MReference est numérique en utilisant la dernière clause AND de la clause WHERE . Cela fonctionne et lorsque j'édite toutes les lignes dans SSMS, je ne vois que les enregistrements où au moins le premier caractère est numérique.

La procédure stockée est ici:

IF OBJECT_ID('tempdb..#vwRPWIP_POST') IS NOT NULL
    DROP TABLE #vwRPWIP_POST

SELECT * INTO #vwRPWIP_POST
FROM
(
SELECT TOP (100) PERCENT Job, MDescription, MWarehouse, MReference, TrnDate
FROM CompanyE.dbo.WipJobPost AS wjp
WHERE (Job LIKE '%P') 
AND (MWarehouse = 'F') 
AND (TrnDate > DATEADD(DAY, - 395, GETDATE())) 
AND (MReference LIKE '[0-9][0-9][0-9]%')
)A

Avec les modifications apportées à la vue, les lignes 18 et 23 ne sortent plus d'erreur ni 38 "AND LEFT (wp.MReference, 3)

Msg 245, niveau 16, état 1, procédure usp12DAYRESINREPORT, ligne 11 [Batch Start Line 2]
La conversion a échoué lors de la conversion de la valeur varchar «P» en type de données int.

Il échoue sur la première des MReference "P" qu'il rencontre, ce sont les données sans la dernière clause AND dans la vue afin que je puisse extraire les données incriminées: p >

Cette dernière clause AND de usp12DAYRESINREPORT semble être capable de voir à travers la vue et de revenir aux données brutes et aux enregistrements et effectue la comparaison là-bas !?

J'ai essayé de faire cette dernière comparaison dans l'interface de création de rapports Access mais j'obtiens #Type! problèmes lors de l'utilisation de VAL () pour convertir le champ MReference en chaîne.

Pourquoi cela dure-t-il "AND LEFT (MReference, 3)

Voici quelques données avec de bons et de mauvais enregistrements

2000410P    2 5/8" TBI-HEAVY                F   DUMMIES     2020-03-11 00:00:00.000
2033480P    2 5/8" TBI-LIGHT                F   DUMMIES     2020-07-09 00:00:00.000
1939490P    2 5/8" TBI-LIGHT                F   DUMIES      2020-05-19 00:00:00.000
1932751P    2 5/8" TBI-LIGHT                F   DUM         2019-07-24 00:00:00.000
C139930P    2 5/8" TBI-HEAVY W/RESIN        F   C139930P    2020-06-10 00:00:00.000
C139930P    3 7/8" TBI-HEAVY W/RESIN        F   C139930P    2020-06-10 00:00:00.000
1901300P    1 3/4" TBI LAP-LIGHT W/RESIN    F   94-L        2020-04-07 00:00:00.000
2070150P    2 5/8" TBI-HEAVY W/RESIN        F   51-P        2020-02-21 00:00:00.000
RD83870P    2 5/8" ALI-LGT W/RESIN          F   365-P       2020-01-02 00:00:00.000
1935170P    2 5/8" TBI-HEAVY W/RESIN        F   365-P       2020-01-02 00:00:00.000
1970701P    2 5/8" TBI-HEAVY W/RESIN        F   365-P       2020-01-02 00:00:00.000
1970701P    2 5/8" TBI-HEAVY W/RESIN        F   365-P       2020-01-02 00:00:00.000

Toute aide ou explication serait grandement appréciée. P >

EDIT Correctif appliqué grâce à David Browne.

Le jeu d'enregistrements a dû être abstrait plus loin que la vue ne le fait apparemment. La sélection du jeu d'enregistrements dans une table temporaire avec uniquement des enregistrements acceptables a accompli cela. Voici le code de résumé:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp12DAYRESINREPORT]   
    @parFromDate NVARCHAR(5)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @FromDate NVARCHAR(5),@Locn NVARCHAR(10) = 'PRES';
    SET @FromDate = @parFromDate;

    SELECT DISTINCT 
        A.Job, A.JobDescription, A.QtyToMake, A.JobDeliveryDate,
        A.MDescription, A.MWarehouse, A.MReference, A.HierHead1,
        A.OperCompleted, A.WorkCentre, 
        B.LocnSummed QtyCompleted
    FROM
        (SELECT 
             wm.Job, wm.JobDescription, wm.JobDeliveryDate, wm.QtyToMake,
             wp.MDescription, wp.MWarehouse, wp.MReference, wal.HierHead1
        , wal.OperCompleted, wal.QtyCompleted, wal.WorkCentre
        , LEFT(wm.JOB,7) AS MID7
    FROM
        TablesCoE.dbo.vwRPWIP_POST wp
    INNER JOIN
        (TablesCoE.dbo.vwRPWIP_ALLLAB wal
        INNER JOIN
            TablesCoE.dbo.vwRPWIP_Master wm
        ON
            wal.Job = wm.Job)
    ON 
        wp.Job = wm.Job
    WHERE wp.MWarehouse = 'F'
        AND LEFT(wp.MReference,3) <= @FromDate
        AND wal.OperCompleted <> 'Y'
        AND (wal.WorkCentre = 'PRES'
            OR wal.WorkCentre = 'VPRS')
    )A
INNER JOIN
(
SELECT LEFT(JobRel,6)Job,SUBSTRING(JobRel,8,2)Rel
    ,LocnRecordsAggregatedSummed LocnSummed
FROM
    (
    SELECT JobRel,SUM(LocnRecordsAggregated)LocnRecordsAggregatedSummed
    FROM
        (
        SELECT LEFT(matl_nbr,9)JobRel,LocnRecordsAggregated
        FROM
            (
                SELECT DISTINCT matl_nbr,LocnRecordsAggregated = 1
                FROM mtrk_CompanyE.dbo.trxn_hstd
                WHERE locn_to = @Locn
            )A
        )B
    GROUP BY B.JobRel
    )C
)B
ON
    (LEFT(A.Job,6) = B.Job COLLATE SQL_Latin1_General_CP1_CI_AS
        AND SUBSTRING(A.Job,7,1) = RIGHT(B.Rel COLLATE SQL_Latin1_General_CP1_CI_AS,1))
WHERE
    LocnSummed <> QtyToMake
    --AND LEFT(MReference,3) <= @FromDate - 10;

END

Avec la clause FROM mise à jour:

SELECT TOP (100) PERCENT 
    Job, MDescription, MWarehouse, MReference, TrnDate
FROM            
    CompanyE.dbo.WipJobPost AS wjp
WHERE
    (Job LIKE '%P') 
    AND (MWarehouse = 'F') 
    AND (TrnDate > DATEADD(DAY, - 395, GETDATE())) 
    AND (LEFT(MReference, 1) LIKE '[0-9]')

Inclus à nouveau la vérification de suppression de la table temporaire à la fin du sp.

sql-server stored-procedures sql-view

1 commentaires

Une explication des raisons pour lesquelles la vue initiale ne semble pas suffisamment abstraite du jeu d'enregistrements me serait très utile.

3 Réponses :

0
votes

Vous vous assurez uniquement que la première lettre est numérique, pas les 3 premières - cela pourrait-il être le problème?

Au lieu de

AND MReference LIKE '[0-9][0-9][0-9]%'

, j'utiliserais quelque chose comme

AND (LEFT(MReference, 1) LIKE '[0-9]')

Ou s'ils peuvent contenir de 1 à 3 chiffres, alors j'écrirais une petite fonction à valeur scalaire pour renvoyer la valeur INT du premier 1 à 3 chiffres avant le «-» et utilisez celui-ci dans la vue.

1 commentaires

Christoph - oui, j'ai ajouté cette logique et nous avons probablement tapé en même temps. Même résultats.

1
votes

Considérez

AND  MReference between '000-' and '999-Z'

Ce qui est traité de la même manière que la vue. SQL Server est libre de traiter les critères de la clause WHERE dans n'importe quel ordre, y compris en appliquant les critères de la requête externe avant ceux de la CTE / Sous-requête / vue.

Vous devez matérialiser la sous-requête dans un temp table ou variable de table, ou écrivez des critères qui peuvent être réorganisés, comme:

case when LEFT(MReference, 1) LIKE '[0-9]' then cast(LEFT(MReference,3) as int) else 0 end <= 100

Vous pouvez essayer un critère qui est susceptible d’être évalué en premier, comme

with q as
(
SELECT Job, MDescription, MWarehouse, MReference, TrnDate
FROM            
    CompanyE.dbo.WipJobPost AS wjp
WHERE
    (Job LIKE '%P') 
    AND (MWarehouse = 'F') 
    AND (TrnDate > DATEADD(DAY, - 395, GETDATE())) 
    AND (LEFT(MReference, 1) LIKE '[0-9]')
)
select *
from q
where LEFT(MReference,3) <= 100

mais ce ne serait pas une garantie.

2 commentaires

David Brown - Rendez-vous donc uniquement possible de voir les données dans sa propre table temporaire sans accéder à d'autres données?

David Brown - Merci. Ainsi, l'abstraction des enregistrements dans une table temporaire ne permettait alors que l'interrogation de ce jeu d'enregistrements, empêchant les enregistrements incriminés de la table brute d'origine. Réponse acceptée.

0
votes

J'essaierais d'extraire la date julienne avec une fonction comme celle-ci:

SELECT 123 AS expected, dbo.ExtractJulianDate('123-Foo') AS actual
UNION ALL 
SELECT 12 AS expected, dbo.ExtractJulianDate('12-Foo') AS actual
UNION ALL 
SELECT 1 AS expected, dbo.ExtractJulianDate('1-Foo') AS actual
UNION ALL 
SELECT NULL AS expected, dbo.ExtractJulianDate(NULL) AS actual
UNION ALL 
SELECT NULL AS expected, dbo.ExtractJulianDate('') AS actual
UNION ALL 
SELECT NULL AS expected, dbo.ExtractJulianDate('-5-Foo') AS actual
UNION ALL 
SELECT NULL AS expected, dbo.ExtractJulianDate('123') AS actual
UNION ALL 
SELECT NULL AS expected, dbo.ExtractJulianDate('Foo') AS actual

Utilisation:

SELECT Job, MDescription, MWarehouse, MReference, dbo.ExtractJulianDate(MReference) AS JulianDate, TrnDate
FROM dbo.WipJobPost AS wjp

Tests: p >

CREATE FUNCTION ExtractJulianDate(@Reference NVARCHAR(MAX))
RETURNS INT
AS
BEGIN
  IF (@Reference IS NULL) RETURN NULL;
  DECLARE @Token AS NVARCHAR(4);
  DECLARE @DelimiterIndex AS INT;
  SET @Token = LEFT(@Reference, 4);
  SET @DelimiterIndex = CHARINDEX('-', @Token, 2)
  -- Hardcoded to remain fast
  IF (@DelimiterIndex < 2) RETURN NULL;
  IF (@DelimiterIndex = 4) BEGIN
    IF (@Token LIKE '[0-9][0-9][0-9]%') RETURN CAST(LEFT(@Token, 3) AS INT);
    RETURN NULL;
  END
  IF (@DelimiterIndex = 3) BEGIN
    IF (@Token LIKE '[0-9][0-9]%') RETURN CAST(LEFT(@Token, 2) AS INT);
    RETURN NULL;
  END
  IF (@DelimiterIndex = 2) BEGIN
    IF (@Token LIKE '[0-9]%') RETURN CAST(LEFT(@Token, 1) AS INT);
  END
  RETURN NULL;
END

1 commentaires

Et bien sûr, vous pouvez toujours optimiser cela pour être exécuté une seule fois au moment INSERT au lieu de chaque SELECT en rendant le schéma de fonction lié et en l'utilisant dans une colonne calculée que vous pouvez même persister et indexer ...