Message d'erreur lors d'une exécution d'une formule

bonjour je suis débutant sur SQL server 2012
Lorsque j'ai exécuté ma requête j'ai un message : Error message : divide by zero error encountered
C'est un problème au niveau de la formule "fnCP"

voici la requête :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

SELECT dbo.ViewDLG_Aggregate.DLG_BHD_BTCH_BatchID_value, dbo.ViewDLG_Aggregate.StartTime, dbo.ViewDLG_Aggregate.EndTime, dbo.ViewDLG_Aggregate.TotalLength, dbo.ViewDLG_Aggregate.MinDLG_CTRL_COLD_DiameterPVXY, dbo.ViewDLG_Aggregate.MaxDLG_CTRL_COLD_DiameterPVXY, dbo.ViewDLG_Aggregate.AvgDLG_CTRL_COLD_DiameterPVXY, dbo.ViewDLG_Aggregate.StdevDLG_CTRL_COLD_DiameterPVXY, dbo.ViewDLG_Aggregate.MinDLG_CTRL_COLD_OvalityPV, dbo.ViewDLG_Aggregate.MaxDLG_CTRL_COLD_OvalityPV, dbo.ViewDLG_Aggregate.AvgDLG_CTRL_COLD_OvalityPV, dbo.ViewDLG_Aggregate.StdevDLG_CTRL_COLD_OvalityPV, dbo.BHD.BHD_BHD_BTCH_BatchID_value, dbo.BHD.BHD_BHD_BTCH_PresetLength_value, dbo.BHD.BHD_BHD_BTCH_RecipeName_value, dbo.BHD.BHD_BHD_BTCH_StatisticsON_value, dbo.BHD.BHD_CTRL_COLD_DiameterLSL_value, dbo.BHD.BHD_CTRL_COLD_DiameterSP_value, dbo.BHD.BHD_CTRL_COLD_DiameterUSL_value, dbo.BHD.BHD_CTRL_COLD_OvalityUSL_value, dbo.fnCp(dbo.BHD.BHD_CTRL_COLD_DiameterUSL_value, dbo.BHD.BHD_CTRL_COLD_DiameterLSL_value, dbo.ViewDLG_Aggregate.StdevDLG_CTRL_COLD_DiameterPVXY) AS Cp_COLD_Diameter, dbo.fnCpK(dbo.BHD.BHD_CTRL_COLD_DiameterUSL_value, dbo.BHD.BHD_CTRL_COLD_DiameterLSL_value, dbo.ViewDLG_Aggregate.AvgDLG_CTRL_COLD_DiameterPVXY, dbo.ViewDLG_Aggregate.StdevDLG_CTRL_COLD_DiameterPVXY) AS CpK_COLD_Diameter FROM dbo.BHD INNER JOIN dbo.ViewDLG_Aggregate ON dbo.BHD.BHD_BHD_BTCH_BatchID_value = dbo.ViewDLG_Aggregate.DLG_BHD_BTCH_BatchID_value

---

voici la formule :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

USE [Dexline_10] GO /****** Object: UserDefinedFunction [dbo].[fnCp] Script Date: 12/08/2016 13:58:38 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER function [dbo].[fnCp] (@USL real,@LSL real, @StdPV real) returns real begin declare @CP real if @StdPV=0 begin set @CP = 999999 end set @CP = (@USL - @LSL)/(6*@StdPV) return @CP end

---

Si quelqu'un peut m'aider ....
Merci bien :)

L'erreur est dans la fonction :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

begin declare @CP real if @StdPV=0 begin set @CP = 999999 end else begin set @CP = (@USL - @LSL)/(6*@StdPV) end return @CP end

---

Merci je vais regardé dans la formule , je vois pas ou elle se situe .... :(, de plus c'est une fonction a déjà été utilisé pour un mise en service sans problème ...

Je t'ai donné la solution. Il manque juste un else :roll:

Bonjour,

Vous testez bien si le dénominateur vaut zéro (et attribuez alors une valeur arbitraire à la variable de retour), mais le calcul n'est pas mis dans un ELSE et est donc effectué dans tous les cas, même lorsque le dénominateur vaut zéro.

Ajoutez un ELSE, ou faites directement un RETURN dans le IF afin de sortir de la fonction.

[EDIT]grillé par al1_24[/EDIT]

Citation:

---

Envoyé par al1_24

Je t'ai donné la solution. Il manque juste un else :roll:

---

Juste ! merci bien ... ;)

Bonjour,

Le problème avec les fonctions scalaires, c'est qu'elles sont exécutées une fois par ligne de résultat, avec des effets néfastes sur le comportement de l'optimiseur.
Si l'on transforme la fonction scalaire en fonction de table en ligne, on élimine souvent ces deux effets :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

CREATE FUNCTION fnCp ( @USL real ,@LSL real , @StdPV real ) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT CASE @StdPV WHEN 0 THEN 999999 ELSE (@USL - @LSL)/(6*@StdPV) END AS Cp )

---

Ici la clause WITH SCHEMABINDING indique au moteur qu'il doit faire des vérifications supplémentaires, notamment déterminer si la fonction accède à des données, système ou non, si la fonction est déterministe, ... Ces propriétés influent sur l'optimiseur : si la fonction est déterministe, précise et liée au schéma (sous-entendu : la fonction ne peut pas changer de définition au cours de l'exécution d'une requête), alors le moteur utilise des règles qui ne sont pas les mêmes que lorsque ce n'est pas le cas, ce qui peut le conduire, par exemple, à ne pas produire de spools, ...

La requête devient alors :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

SELECT AG.DLG_BHD_BTCH_BatchID_value , AG.StartTime , AG.EndTime , AG.TotalLength , AG.MinDLG_CTRL_COLD_DiameterPVXY , AG.MaxDLG_CTRL_COLD_DiameterPVXY , AG.AvgDLG_CTRL_COLD_DiameterPVXY , AG.StdevDLG_CTRL_COLD_DiameterPVXY , AG.MinDLG_CTRL_COLD_OvalityPV , AG.MaxDLG_CTRL_COLD_OvalityPV , AG.AvgDLG_CTRL_COLD_OvalityPV , AG.StdevDLG_CTRL_COLD_OvalityPV , BHD.BHD_BHD_BTCH_BatchID_value , BHD.BHD_BHD_BTCH_PresetLength_value , BHD.BHD_BHD_BTCH_RecipeName_value , BHD.BHD_BHD_BTCH_StatisticsON_value , BHD.BHD_CTRL_COLD_DiameterLSL_value , BHD.BHD_CTRL_COLD_DiameterSP_value , BHD.BHD_CTRL_COLD_DiameterUSL_value , BHD.BHD_CTRL_COLD_OvalityUSL_value , CP.Cp AS Cp_COLD_Diameter , dbo.fnCpK(BHD.BHD_CTRL_COLD_DiameterUSL_value, BHD.BHD_CTRL_COLD_DiameterLSL_value, AG.AvgDLG_CTRL_COLD_DiameterPVXY, AG.StdevDLG_CTRL_COLD_DiameterPVXY) AS CpK_COLD_Diameter FROM dbo.BHD AS BHD INNER JOIN dbo.ViewDLG_Aggregate AS AG ON BHD.BHD_BHD_BTCH_BatchID_value = AG.DLG_BHD_BTCH_BatchID_value CROSS APPLY dbo.fnCp(BHD.BHD_CTRL_COLD_DiameterUSL_value, BHD.BHD_CTRL_COLD_DiameterLSL_value, AG.StdevDLG_CTRL_COLD_DiameterPVXY) AS CP

---

Avec un peu de change, on peut faire de même avec la fonction dbo.fnCpK().

@++ ;)

Bonjour merci pour vos informations
A tout hasard vous connaissez pas un livre type pdf , avec les explications de SQL , comme les fonctions , triggers , et bien plus ,... ?
merci :)

Si, bien sur.... Ceux que nous avons écrit :

Pièce jointe 217975Pièce jointe 217976

Mais ne rêvez pas en pdf cela n'existe pas.... Pour la bonne et simple raison, que les éditeurs ne gagnerait plus d'argent et que par conséquent il n'y aurait plus de livre (c'est d'ailleurs ce qui se passe dans l'édition US dont la production d'ouvrages techniques à fortement diminuée depuis quelques années...)

A +

Merci je vais regardé sur internet pour liens de téléchargement légale !;)

Question sur le système " SCHEMABINDING"

Ma fonction n'est plus dans " scalar valued functions" mais dans " table valued function" , je suppose que c'est logique vu que cette fonction travail sur la table et pas sur les lignes comme vous me l'avez décrit , juste ?

Par contre dans ma rêquete j'ai un message d'erreur " imposible d'afficher sous forme de colone,..." quand j'introduit et je comprend pas ... :(

Code:

---

CROSS APPLY dbo.fnCptest1(BHD.BHD_CTRL_COLD_DiameterUSL_value, BHD.BHD_CTRL_COLD_DiameterLSL_value, AG.StdevDLG_CTRL_COLD_DiameterPVXY) AS CP

---

Citation:

---

cette fonction travail sur la table et pas sur les lignes comme vous me l'avez décrit , juste ?

---

En fait la fonction scalaire renvoie une valeur, et la fonction de table renvoie une table (dans le cas présent, d'une seule ligne et d'une seule colonne).
En revanche le fonction scalaire est appelée une fois par ligne de résultat, alors que la fonction de table n'est (généralement) appelée qu'une seule fois.

Pourriez-vous donner le libellé exact et/ou le numéro de l'erreur SVP ? Je ne parviens pas à la retrouver dans sys.messages.
Cela me paraît un peu bizarre, car syntaxiquement, la requête est correcte.

Pour faire encore plus simple, vous pourriez déplacer le CASE de la fonction directement entre le SELECT et le FROM de la requête.

@++ ;)

Citation:

---

Envoyé par elsuket

Pourriez-vous donner le libellé exact et/ou le numéro de l'erreur SVP ? Je ne parviens pas à la retrouver dans sys.messages.
Cela me paraît un peu bizarre, car syntaxiquement, la requête est correcte.

@++ ;)

---

Voici le message , la vue s'execute bien mais je vois pas la colonne lol
Pièce jointe 218004

Je viens de voir aussi quelques chose QUE je comprend pas .
En faite j'utilise un OPC server qui crée et update mes tables dans SQL . Ma table BHD et la table de référence car elle contient la recette et le batch . Donc j'ai crée un numéro qui s’incrémente automatiquement par SQL à chaque fois que OPC server incrémente ma table . Sauf que ici se matin j'ai remarqué que ma table est passée de 77 a 10075!
Les procédures stockée sont geré via OPC server

J'ai mis le script sous forme de balise ( en espérant pas avoir de problème avec le gérant du site) car trop grand à envoyé en fichier .Si vous avez le temps de jeté un petit coup d’œil se serait le top ;);)

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017

USE [master] GO /****** Object: Database [Dexline_10] Script Date: 16/08/2016 13:41:33 ******/ CREATE DATABASE [Dexline_10] CONTAINMENT = NONE ON PRIMARY ( NAME = N'Dexline_10', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLBN\MSSQL\DATA\Dexline_10.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'Dexline_10_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLBN\MSSQL\DATA\Dexline_10_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO ALTER DATABASE [Dexline_10] SET COMPATIBILITY_LEVEL = 110 GO IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC [Dexline_10].[dbo].[sp_fulltext_database] @action = 'enable' end GO ALTER DATABASE [Dexline_10] SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE [Dexline_10] SET ANSI_NULLS OFF GO ALTER DATABASE [Dexline_10] SET ANSI_PADDING OFF GO ALTER DATABASE [Dexline_10] SET ANSI_WARNINGS OFF GO ALTER DATABASE [Dexline_10] SET ARITHABORT OFF GO ALTER DATABASE [Dexline_10] SET AUTO_CLOSE ON GO ALTER DATABASE [Dexline_10] SET AUTO_CREATE_STATISTICS ON GO ALTER DATABASE [Dexline_10] SET AUTO_SHRINK OFF GO ALTER DATABASE [Dexline_10] SET AUTO_UPDATE_STATISTICS ON GO ALTER DATABASE [Dexline_10] SET CURSOR_CLOSE_ON_COMMIT OFF GO ALTER DATABASE [Dexline_10] SET CURSOR_DEFAULT GLOBAL GO ALTER DATABASE [Dexline_10] SET CONCAT_NULL_YIELDS_NULL OFF GO ALTER DATABASE [Dexline_10] SET NUMERIC_ROUNDABORT OFF GO ALTER DATABASE [Dexline_10] SET QUOTED_IDENTIFIER OFF GO ALTER DATABASE [Dexline_10] SET RECURSIVE_TRIGGERS OFF GO ALTER DATABASE [Dexline_10] SET DISABLE_BROKER GO ALTER DATABASE [Dexline_10] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO ALTER DATABASE [Dexline_10] SET DATE_CORRELATION_OPTIMIZATION OFF GO ALTER DATABASE [Dexline_10] SET TRUSTWORTHY OFF GO ALTER DATABASE [Dexline_10] SET ALLOW_SNAPSHOT_ISOLATION OFF GO ALTER DATABASE [Dexline_10] SET PARAMETERIZATION SIMPLE GO ALTER DATABASE [Dexline_10] SET READ_COMMITTED_SNAPSHOT OFF GO ALTER DATABASE [Dexline_10] SET HONOR_BROKER_PRIORITY OFF GO ALTER DATABASE [Dexline_10] SET RECOVERY SIMPLE GO ALTER DATABASE [Dexline_10] SET MULTI_USER GO ALTER DATABASE [Dexline_10] SET PAGE_VERIFY CHECKSUM GO ALTER DATABASE [Dexline_10] SET DB_CHAINING OFF GO ALTER DATABASE [Dexline_10] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) GO ALTER DATABASE [Dexline_10] SET TARGET_RECOVERY_TIME = 0 SECONDS GO USE [Dexline_10] GO /****** Object: StoredProcedure [dbo].[OPCAlarm_To_LRM_LRM] Script Date: 16/08/2016 13:41:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[OPCAlarm_To_LRM_LRM] (@inAlarmID nvarchar(255), @inAlarmType nvarchar(255), @inAlarmGroup nvarchar(255), @inPriority int, @inAlarmText nvarchar(255), @inActive bit, @inAcked bit, @inTimeDelay int, @inAlarmValue float, @inClearedValue float, @inAlarmLimit float, @inAlarmDateTime DateTime, @inClearedDateTime DateTime, @inAckedDateTime DateTime, @inAckedUser nvarchar(255), @inUnits nvarchar(255), @inDocument nvarchar(255), @inComment nvarchar(255)) AS INSERT INTO LRM ([AlarmID], [AlarmType], [AlarmGroup], [Priority], [AlarmText], [Active], [Acked], [TimeDelay], [AlarmValue], [ClearedValue], [AlarmLimit], [AlarmDateTime], [ClearedDateTime], [AckedDateTime], [AckedUser], [Units], [Document], [Comment]) VALUES (@inAlarmID, @inAlarmType, @inAlarmGroup, @inPriority, @inAlarmText, @inActive, @inAcked, @inTimeDelay, @inAlarmValue, @inClearedValue, @inAlarmLimit, @inAlarmDateTime, @inClearedDateTime, @inAckedDateTime, @inAckedUser, @inUnits, @inDocument, @inComment) GO /****** Object: StoredProcedure [dbo].[OPCAlarm_To_LRM1_LRM] Script Date: 16/08/2016 13:41:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[OPCAlarm_To_LRM1_LRM] (@inAlarmID nvarchar(255), @inAlarmType nvarchar(255), @inAlarmGroup nvarchar(255), @inPriority int, @inAlarmText nvarchar(255), @inActive bit, @inAcked bit, @inTimeDelay int, @inAlarmValue float, @inClearedValue float, @inAlarmLimit float, @inAlarmDateTime DateTime, @inClearedDateTime DateTime, @inAckedDateTime DateTime, @inAckedUser nvarchar(255), @inUnits nvarchar(255), @inDocument nvarchar(255), @inComment nvarchar(255)) AS INSERT INTO LRM1 ([AlarmID], [AlarmType], [AlarmGroup], [Priority], [AlarmText], [Active], [Acked], [TimeDelay], [AlarmValue], [ClearedValue], [AlarmLimit], [AlarmDateTime], [ClearedDateTime], [AckedDateTime], [AckedUser], [Units], [Document], [Comment]) VALUES (@inAlarmID, @inAlarmType, @inAlarmGroup, @inPriority, @inAlarmText, @inActive, @inAcked, @inTimeDelay, @inAlarmValue, @inClearedValue, @inAlarmLimit, @inAlarmDateTime, @inClearedDateTime, @inAckedDateTime, @inAckedUser, @inUnits, @inDocument, @inComment) GO /****** Object: StoredProcedure [dbo].[OPCSystems_To_BHD_BHD] Script Date: 16/08/2016 13:41:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[OPCSystems_To_BHD_BHD] (@inDataValue1 bigint, @inDataValue2 bigint, @inDataValue3 nvarchar(100), @inDataValue4 bit, @inDataValue5 real, @inDataValue6 real, @inDataValue7 real, @inDataValue8 real, @inDataValue9 nvarchar(100), @inDataValue10 nvarchar(50), @inDataValue11 bigint, @inDataValue12 bigint) AS INSERT INTO [BHD] ([BHD_BHD_BTCH_BatchID_value], [BHD_BHD_BTCH_PresetLength_value], [BHD_BHD_BTCH_RecipeName_value], [BHD_BHD_BTCH_StatisticsON_value], [BHD_CTRL_COLD_DiameterLSL_value], [BHD_CTRL_COLD_DiameterSP_value], [BHD_CTRL_COLD_DiameterUSL_value], [BHD_CTRL_COLD_OvalityUSL_value], [BHD_BHD_BTCH_PartNo_value_Value], [BHD_BHD_BTCH_Customer_value_Value], [BHD_BHD_BTCH_ShopFloorNo_value_Value], [BHD_BHD_BTCH_WorkOrderNo_value_Value]) VALUES (@inDataValue1, @inDataValue2, @inDataValue3, @inDataValue4, @inDataValue5, @inDataValue6, @inDataValue7, @inDataValue8, @inDataValue9, @inDataValue10, @inDataValue11, @inDataValue12) GO /****** Object: StoredProcedure [dbo].[OPCSystems_To_DLG_DLG] Script Date: 16/08/2016 13:41:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[OPCSystems_To_DLG_DLG] (@inDate DateTime, @inDataValue1 bigint, @inDataValue2 bigint, @inDataValue3 real, @inDataValue4 real, @inDataValue5 real, @inDataValue6 int, @inDataValue7 real, @inDataValue8 int, @inDataValue9 int, @inDataValue10 int, @inDataValue11 int, @inDataValue12 int, @inDataValue13 int, @inDataValue14 int, @inDataValue15 int, @inDataValue16 int, @inDataValue17 int, @inDataValue18 int, @inDataValue19 int, @inDataValue20 int, @inDataValue21 int, @inDataValue22 int, @inDataValue23 int, @inDataValue24 int, @inDataValue25 int, @inDataValue26 int, @inDataValue27 int, @inDataValue28 int, @inDataValue29 int, @inDataValue30 int, @inDataValue31 int, @inDataValue32 int, @inDataValue33 int, @inDataValue34 int, @inDataValue35 int, @inDataValue36 int, @inDataValue37 int, @inDataValue38 int, @inDataValue39 int, @inDataValue40 real, @inDataValue41 int, @inDataValue42 int, @inDataValue43 int, @inDataValue44 int, @inDataValue45 int, @inDataValue46 int, @inDataValue47 int, @inDataValue48 int, @inDataValue49 int, @inDataValue50 int, @inDataValue51 int, @inDataValue52 int, @inDataValue53 int, @inDataValue54 int, @inDataValue55 int, @inDataValue56 int, @inDataValue57 int, @inDataValue58 int, @inDataValue59 int, @inDataValue60 int, @inDataValue61 int, @inDataValue62 int, @inDataValue63 int, @inDataValue64 int, @inDataValue65 int, @inDataValue66 int, @inDataValue67 int, @inDataValue68 real, @inDataValue69 int, @inDataValue70 int, @inDataValue71 real) AS INSERT INTO [DLG] ([DateAndTime], [DLG_BHD_BTCH_BatchID_value], [DLG_BHD_BTCH_Length_value], [DLG_CTRL_COLD_DiameterPVX_value], [DLG_CTRL_COLD_DiameterPVXY_value], [DLG_CTRL_COLD_DiameterPVY_value], [DLG_EXTA_MIS_PressurePV_value], [DLG_EXTA_MOT_ScrewCurrentPV_value], [DLG_EXTA_MOT_ScrewSpeedPV_value], [DLG_EXTA_MOT_ScrewSpeedSP_value], [DLG_EXTA_MOT_ScrewTorquePV_value], [DLG_EXTA_TMP_Barrel01OUT_value], [DLG_EXTA_TMP_Barrel01PV_value], [DLG_EXTA_TMP_Barrel01SP_value], [DLG_EXTA_TMP_Barrel02OUT_value], [DLG_EXTA_TMP_Barrel02PV_value], [DLG_EXTA_TMP_Barrel02SP_value], [DLG_EXTA_TMP_Barrel03OUT_value], [DLG_EXTA_TMP_Barrel03PV_value], [DLG_EXTA_TMP_Barrel03SP_value], [DLG_EXTA_TMP_Barrel04OUT_value], [DLG_EXTA_TMP_Barrel04PV_value], [DLG_EXTA_TMP_Barrel04SP_value], [DLG_EXTA_TMP_BreechPV_value], [DLG_EXTA_TMP_BreechSP_value], [DLG_EXTA_TMP_ClampOUT_value], [DLG_EXTA_TMP_ClampPV_value], [DLG_EXTA_TMP_ClampSP_value], [DLG_EXTA_TMP_FlangeOUT_value], [DLG_EXTA_TMP_FlangePV_value], [DLG_EXTA_TMP_FlangeSP_value], [DLG_EXTA_TMP_Head1OUT_value], [DLG_EXTA_TMP_Head1PV_value], [DLG_EXTA_TMP_Head1SP_value], [DLG_EXTA_TMP_Head2OUT_value], [DLG_EXTA_TMP_Head2PV_value], [DLG_EXTA_TMP_Head2SP_value], [DLG_EXTA_TMP_MeltTempPV_value], [DLG_EXTA_TMP_MeltTempSP_value], [DLG_EXTB_MIS_PressurePV_value], [DLG_EXTB_MOT_CurrentPV_value], [DLG_EXTB_MOT_ScrewSpeedPV_value], [DLG_EXTB_MOT_ScrewSpeedSP_value], [DLG_EXTB_MOT_TorquePV_value], [DLG_EXTB_TMP_Barrel01OUT_value], [DLG_EXTB_TMP_Barrel01PV_value], [DLG_EXTB_TMP_Barrel01SP_value], [DLG_EXTB_TMP_Barrel02OUT_value], [DLG_EXTB_TMP_Barrel02PV_value], [DLG_EXTB_TMP_Barrel02SP_value], [DLG_EXTB_TMP_Barrel03OUT_value], [DLG_EXTB_TMP_Barrel03PV_value], [DLG_EXTB_TMP_Barrel03SP_value], [DLG_EXTB_TMP_Barrel04OUT_value], [DLG_EXTB_TMP_Barrel04PV_value], [DLG_EXTB_TMP_Barrel04SP_value], [DLG_EXTB_TMP_BreechPV_value], [DLG_EXTB_TMP_BreechSP_value], [DLG_EXTB_TMP_ClampOUT_value], [DLG_EXTB_TMP_ClampPV_value], [DLG_EXTB_TMP_ClampSP_value], [DLG_EXTB_TMP_FlangeOUT_value], [DLG_EXTB_TMP_FlangePV_value], [DLG_EXTB_TMP_FlangeSP_value], [DLG_EXTB_TMP_Head3PV_value], [DLG_EXTB_TMP_Head3SP_value], [DLG_EXTB_TMP_MeltTempPV_value], [DLG_EXTB_TMP_MeltTempSP_value], [DLG_TKUP_MOT_SpeedPV_value], [DLG_TRG1_TMP_temperaturePV_value], [DLG_TRG1_TMP_temperatureSP_value], [DLG_CTRL_COLD_OvalityPV_Value]) VALUES (@inDate, @inDataValue1, @inDataValue2, @inDataValue3, @inDataValue4, @inDataValue5, @inDataValue6, @inDataValue7, @inDataValue8, @inDataValue9, @inDataValue10, @inDataValue11, @inDataValue12, @inDataValue13, @inDataValue14, @inDataValue15, @inDataValue16, @inDataValue17, @inDataValue18, @inDataValue19, @inDataValue20, @inDataValue21, @inDataValue22, @inDataValue23, @inDataValue24, @inDataValue25, @inDataValue26, @inDataValue27, @inDataValue28, @inDataValue29, @inDataValue30, @inDataValue31, @inDataValue32, @inDataValue33, @inDataValue34, @inDataValue35, @inDataValue36, @inDataValue37, @inDataValue38, @inDataValue39, @inDataValue40, @inDataValue41, @inDataValue42, @inDataValue43, @inDataValue44, @inDataValue45, @inDataValue46, @inDataValue47, @inDataValue48, @inDataValue49, @inDataValue50, @inDataValue51, @inDataValue52, @inDataValue53, @inDataValue54, @inDataValue55, @inDataValue56, @inDataValue57, @inDataValue58, @inDataValue59, @inDataValue60, @inDataValue61, @inDataValue62, @inDataValue63, @inDataValue64, @inDataValue65, @inDataValue66, @inDataValue67, @inDataValue68, @inDataValue69, @inDataValue70, @inDataValue71) GO /****** Object: UserDefinedFunction [dbo].[fnCp] Script Date: 16/08/2016 13:41:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE function [dbo].[fnCp] (@USL real,@LSL real, @StdPV real) returns real begin declare @CP real if @StdPV=0 begin set @CP = 999999 end else set @CP = (@USL - @LSL)/(6*@StdPV) return @CP end   GO /****** Object: UserDefinedFunction [dbo].[fnCpK] Script Date: 16/08/2016 13:41:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE function [dbo].[fnCpK] (@USL real,@LSL real, @AvgPV real, @StdevPV real) returns real begin declare @CpK real declare @CpL real declare @CpU real   if @StdevPV=0 set @CpK = 888   else begin   set @CpU = (@USL - @AvgPV)/(3*@StdevPV) set @CpL = (@AvgPV - @LSL)/(3*@StdevPV)   if @CpU<=@CpL set @CpK = @CpU else set @CpK = @CpL   end   return @CpK   end   GO /****** Object: UserDefinedFunction [dbo].[fnCpU] Script Date: 16/08/2016 13:41:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE function [dbo].[fnCpU] (@USL real, @AvgPV real, @StdevPV real) returns real begin   declare @CpU real   if @StdevPV=0 begin set @CpU = 999999 end else set @CpU = (@USL - @AvgPV)/(3*@StdevPV)   return @CpU end   GO /****** Object: Table [dbo].[BHD] Script Date: 16/08/2016 13:41:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[BHD]( [BHD_BHD_BTCH_BatchID_value] [bigint] NULL, [BHD_BHD_BTCH_PresetLength_value] [bigint] NULL, [BHD_BHD_BTCH_RecipeName_value] [nvarchar](100) NULL, [BHD_BHD_BTCH_StatisticsON_value] [bit] NULL, [BHD_CTRL_COLD_DiameterLSL_value] [real] NULL, [BHD_CTRL_COLD_DiameterSP_value] [real] NULL, [BHD_CTRL_COLD_DiameterUSL_value] [real] NULL, [BHD_CTRL_COLD_OvalityUSL_value] [real] NULL, [BHD_BHD_BTCH_PartNo_value_Value] [nvarchar](100) NULL, [BHD_BHD_BTCH_Customer_value_Value] [nvarchar](50) NULL, [BHD_BHD_BTCH_ShopFloorNo_value_Value] [bigint] NULL, [BHD_BHD_BTCH_WorkOrderNo_value_Value] [bigint] NULL, [ID] [bigint] IDENTITY(0,1) NOT NULL, CONSTRAINT [PK_BHD] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]   GO /****** Object: Table [dbo].[DLG] Script Date: 16/08/2016 13:41:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DLG]( [DateAndTime] [datetime] NOT NULL, [DLG_BHD_BTCH_BatchID_value] [bigint] NULL, [DLG_BHD_BTCH_Length_value] [bigint] NULL, [DLG_CTRL_COLD_DiameterPVX_value] [real] NULL, [DLG_CTRL_COLD_DiameterPVXY_value] [real] NULL, [DLG_CTRL_COLD_DiameterPVY_value] [real] NULL, [DLG_EXTA_MIS_PressurePV_value] [int] NULL, [DLG_EXTA_MOT_ScrewCurrentPV_value] [real] NULL, [DLG_EXTA_MOT_ScrewSpeedPV_value] [int] NULL, [DLG_EXTA_MOT_ScrewSpeedSP_value] [int] NULL, [DLG_EXTA_MOT_ScrewTorquePV_value] [int] NULL, [DLG_EXTA_TMP_Barrel01OUT_value] [int] NULL, [DLG_EXTA_TMP_Barrel01PV_value] [int] NULL, [DLG_EXTA_TMP_Barrel01SP_value] [int] NULL, [DLG_EXTA_TMP_Barrel02OUT_value] [int] NULL, [DLG_EXTA_TMP_Barrel02PV_value] [int] NULL, [DLG_EXTA_TMP_Barrel02SP_value] [int] NULL, [DLG_EXTA_TMP_Barrel03OUT_value] [int] NULL, [DLG_EXTA_TMP_Barrel03PV_value] [int] NULL, [DLG_EXTA_TMP_Barrel03SP_value] [int] NULL, [DLG_EXTA_TMP_Barrel04OUT_value] [int] NULL, [DLG_EXTA_TMP_Barrel04PV_value] [int] NULL, [DLG_EXTA_TMP_Barrel04SP_value] [int] NULL, [DLG_EXTA_TMP_BreechPV_value] [int] NULL, [DLG_EXTA_TMP_BreechSP_value] [int] NULL, [DLG_EXTA_TMP_ClampOUT_value] [int] NULL, [DLG_EXTA_TMP_ClampPV_value] [int] NULL, [DLG_EXTA_TMP_ClampSP_value] [int] NULL, [DLG_EXTA_TMP_FlangeOUT_value] [int] NULL, [DLG_EXTA_TMP_FlangePV_value] [int] NULL, [DLG_EXTA_TMP_FlangeSP_value] [int] NULL, [DLG_EXTA_TMP_Head1OUT_value] [int] NULL, [DLG_EXTA_TMP_Head1PV_value] [int] NULL, [DLG_EXTA_TMP_Head1SP_value] [int] NULL, [DLG_EXTA_TMP_Head2OUT_value] [int] NULL, [DLG_EXTA_TMP_Head2PV_value] [int] NULL, [DLG_EXTA_TMP_Head2SP_value] [int] NULL, [DLG_EXTA_TMP_MeltTempPV_value] [int] NULL, [DLG_EXTA_TMP_MeltTempSP_value] [int] NULL, [DLG_EXTB_MIS_PressurePV_value] [int] NULL, [DLG_EXTB_MOT_CurrentPV_value] [real] NULL, [DLG_EXTB_MOT_ScrewSpeedPV_value] [int] NULL, [DLG_EXTB_MOT_ScrewSpeedSP_value] [int] NULL, [DLG_EXTB_MOT_TorquePV_value] [int] NULL, [DLG_EXTB_TMP_Barrel01OUT_value] [int] NULL, [DLG_EXTB_TMP_Barrel01PV_value] [int] NULL, [DLG_EXTB_TMP_Barrel01SP_value] [int] NULL, [DLG_EXTB_TMP_Barrel02OUT_value] [int] NULL, [DLG_EXTB_TMP_Barrel02PV_value] [int] NULL, [DLG_EXTB_TMP_Barrel02SP_value] [int] NULL, [DLG_EXTB_TMP_Barrel03OUT_value] [int] NULL, [DLG_EXTB_TMP_Barrel03PV_value] [int] NULL, [DLG_EXTB_TMP_Barrel03SP_value] [int] NULL, [DLG_EXTB_TMP_Barrel04OUT_value] [int] NULL, [DLG_EXTB_TMP_Barrel04PV_value] [int] NULL, [DLG_EXTB_TMP_Barrel04SP_value] [int] NULL, [DLG_EXTB_TMP_BreechPV_value] [int] NULL, [DLG_EXTB_TMP_BreechSP_value] [int] NULL, [DLG_EXTB_TMP_ClampOUT_value] [int] NULL, [DLG_EXTB_TMP_ClampPV_value] [int] NULL, [DLG_EXTB_TMP_ClampSP_value] [int] NULL, [DLG_EXTB_TMP_FlangeOUT_value] [int] NULL, [DLG_EXTB_TMP_FlangePV_value] [int] NULL, [DLG_EXTB_TMP_FlangeSP_value] [int] NULL, [DLG_EXTB_TMP_Head3PV_value] [int] NULL, [DLG_EXTB_TMP_Head3SP_value] [int] NULL, [DLG_EXTB_TMP_MeltTempPV_value] [int] NULL, [DLG_EXTB_TMP_MeltTempSP_value] [int] NULL, [DLG_TKUP_MOT_SpeedPV_value] [real] NULL, [DLG_TRG1_TMP_temperaturePV_value] [int] NULL, [DLG_TRG1_TMP_temperatureSP_value] [int] NULL, [DLG_CTRL_COLD_OvalityPV_Value] [real] NULL, CONSTRAINT [DLGDataTimeStamp] PRIMARY KEY CLUSTERED ( [DateAndTime] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]   GO /****** Object: Table [dbo].[LRM] Script Date: 16/08/2016 13:41:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[LRM]( [LRM_ID] [bigint] IDENTITY(0,1) NOT NULL, [LRM_BHD_BTCH_BatchID_value] [bigint] NULL, [LRM_BHD_BTCH_Length_value] [bigint] NULL, [AlarmID] [nvarchar](255) NULL, [AlarmType] [nvarchar](255) NULL, [AlarmGroup] [nvarchar](255) NULL, [Priority] [int] NULL, [AlarmText] [nvarchar](255) NULL, [Active] [bit] NULL, [Acked] [bit] NULL, [TimeDelay] [int] NULL, [AlarmValue] [float] NULL, [ClearedValue] [float] NULL, [AlarmLimit] [float] NULL, [AlarmDateTime] [datetime] NULL, [ClearedDateTime] [datetime] NULL, [AckedDateTime] [datetime] NULL, [AckedUser] [nvarchar](255) NULL, [Units] [nvarchar](255) NULL, [Document] [nvarchar](255) NULL, [Comment] [nvarchar](255) NULL, CONSTRAINT [PK_LRM1] PRIMARY KEY CLUSTERED ( [LRM_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]   GO /****** Object: View [dbo].[ViewDLG_Aggregate] Script Date: 16/08/2016 13:41:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[ViewDLG_Aggregate] AS SELECT DLG_BHD_BTCH_BatchID_value, MIN(DateAndTime) AS StartTime, MAX(DateAndTime) AS EndTime, MAX(DLG_BHD_BTCH_Length_value) AS TotalLength, MIN(DLG_CTRL_COLD_DiameterPVXY_value) AS MinDLG_CTRL_COLD_DiameterPVXY, MAX(DLG_CTRL_COLD_DiameterPVXY_value) AS MaxDLG_CTRL_COLD_DiameterPVXY, AVG(DLG_CTRL_COLD_DiameterPVXY_value) AS AvgDLG_CTRL_COLD_DiameterPVXY, STDEV(DLG_CTRL_COLD_DiameterPVXY_value) AS StdevDLG_CTRL_COLD_DiameterPVXY, MIN(DLG_CTRL_COLD_OvalityPV_Value) AS MinDLG_CTRL_COLD_OvalityPV, MAX(DLG_CTRL_COLD_OvalityPV_Value) AS MaxDLG_CTRL_COLD_OvalityPV, AVG(DLG_CTRL_COLD_OvalityPV_Value) AS AvgDLG_CTRL_COLD_OvalityPV, STDEV(DLG_CTRL_COLD_OvalityPV_Value) AS StdevDLG_CTRL_COLD_OvalityPV FROM dbo.DLG GROUP BY DLG_BHD_BTCH_BatchID_value   GO /****** Object: View [dbo].[ViewBHD_Report] Script Date: 16/08/2016 13:41:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[ViewBHD_Report] AS SELECT dbo.ViewDLG_Aggregate.DLG_BHD_BTCH_BatchID_value, dbo.ViewDLG_Aggregate.StartTime, dbo.ViewDLG_Aggregate.EndTime, dbo.ViewDLG_Aggregate.TotalLength, dbo.ViewDLG_Aggregate.MinDLG_CTRL_COLD_DiameterPVXY, dbo.ViewDLG_Aggregate.MaxDLG_CTRL_COLD_DiameterPVXY, dbo.ViewDLG_Aggregate.AvgDLG_CTRL_COLD_DiameterPVXY, dbo.ViewDLG_Aggregate.StdevDLG_CTRL_COLD_DiameterPVXY, dbo.ViewDLG_Aggregate.MinDLG_CTRL_COLD_OvalityPV, dbo.ViewDLG_Aggregate.MaxDLG_CTRL_COLD_OvalityPV, dbo.ViewDLG_Aggregate.AvgDLG_CTRL_COLD_OvalityPV, dbo.ViewDLG_Aggregate.StdevDLG_CTRL_COLD_OvalityPV, dbo.BHD.BHD_BHD_BTCH_BatchID_value, dbo.BHD.BHD_BHD_BTCH_PresetLength_value, dbo.BHD.BHD_BHD_BTCH_RecipeName_value, dbo.BHD.BHD_BHD_BTCH_StatisticsON_value, dbo.BHD.BHD_CTRL_COLD_DiameterLSL_value, dbo.BHD.BHD_CTRL_COLD_DiameterSP_value, dbo.BHD.BHD_CTRL_COLD_DiameterUSL_value, dbo.BHD.BHD_CTRL_COLD_OvalityUSL_value, dbo.fnCp(dbo.BHD.BHD_CTRL_COLD_DiameterUSL_value, dbo.BHD.BHD_CTRL_COLD_DiameterLSL_value, dbo.ViewDLG_Aggregate.StdevDLG_CTRL_COLD_DiameterPVXY) AS Cp_COLD_Diameter, dbo.fnCpK(dbo.BHD.BHD_CTRL_COLD_DiameterUSL_value, dbo.BHD.BHD_CTRL_COLD_DiameterLSL_value, dbo.ViewDLG_Aggregate.AvgDLG_CTRL_COLD_DiameterPVXY, dbo.ViewDLG_Aggregate.StdevDLG_CTRL_COLD_DiameterPVXY) AS CpK_COLD_Diameter, dbo.fnCpU(dbo.BHD.BHD_CTRL_COLD_OvalityUSL_value, dbo.ViewDLG_Aggregate.AvgDLG_CTRL_COLD_OvalityPV, dbo.ViewDLG_Aggregate.StdevDLG_CTRL_COLD_OvalityPV) AS CpU_COLD_Ovality FROM dbo.BHD INNER JOIN dbo.ViewDLG_Aggregate ON dbo.BHD.BHD_BHD_BTCH_BatchID_value = dbo.ViewDLG_Aggregate.DLG_BHD_BTCH_BatchID_value   GO /****** Object: UserDefinedFunction [dbo].[fnCptest1] Script Date: 16/08/2016 13:41:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- Batch submitted through debugger: SQLQuery3.sql|7|0|C:\Users\PC\AppData\Local\Temp\~vs134B.sql CREATE FUNCTION [dbo].[fnCptest1]   ( @USL real ,@LSL real , @StdPV real ) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT CASE @StdPV WHEN 0 THEN 999999 ELSE (@USL - @LSL)/(6*@StdPV) END AS Cp ) GO /****** Object: View [dbo].[TestView1] Script Date: 16/08/2016 13:41:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[TestView1] AS SELECT AG.DLG_BHD_BTCH_BatchID_value, AG.StartTime, AG.EndTime, AG.TotalLength, AG.MinDLG_CTRL_COLD_DiameterPVXY, AG.MaxDLG_CTRL_COLD_DiameterPVXY, AG.AvgDLG_CTRL_COLD_DiameterPVXY, AG.StdevDLG_CTRL_COLD_DiameterPVXY, AG.MinDLG_CTRL_COLD_OvalityPV, AG.MaxDLG_CTRL_COLD_OvalityPV, AG.AvgDLG_CTRL_COLD_OvalityPV, AG.StdevDLG_CTRL_COLD_OvalityPV, BHD.BHD_BHD_BTCH_BatchID_value, BHD.BHD_BHD_BTCH_PresetLength_value, BHD.BHD_BHD_BTCH_RecipeName_value, BHD.BHD_BHD_BTCH_StatisticsON_value, BHD.BHD_CTRL_COLD_DiameterLSL_value, BHD.BHD_CTRL_COLD_DiameterSP_value, BHD.BHD_CTRL_COLD_DiameterUSL_value, BHD.BHD_CTRL_COLD_OvalityUSL_value FROM dbo.BHD AS BHD INNER JOIN dbo.ViewDLG_Aggregate AS AG ON BHD.BHD_BHD_BTCH_BatchID_value = AG.DLG_BHD_BTCH_BatchID_value CROSS APPLY dbo.fnCpTest1(BHD.BHD_CTRL_COLD_DiameterUSL_value, BHD.BHD_CTRL_COLD_DiameterLSL_value, AG.StdevDLG_CTRL_COLD_DiameterPVXY) AS CP   GO /****** Object: View [dbo].[View_Report] Script Date: 16/08/2016 13:41:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[View_Report] AS SELECT DLG_BHD_BTCH_BatchID_value, StartTime, EndTime, TotalLength, MinDLG_CTRL_COLD_DiameterPVXY, MaxDLG_CTRL_COLD_DiameterPVXY, AvgDLG_CTRL_COLD_DiameterPVXY, StdevDLG_CTRL_COLD_DiameterPVXY, MinDLG_CTRL_COLD_OvalityPV, MaxDLG_CTRL_COLD_OvalityPV, AvgDLG_CTRL_COLD_OvalityPV, StdevDLG_CTRL_COLD_OvalityPV, BHD_BHD_BTCH_BatchID_value, BHD_BHD_BTCH_PresetLength_value, BHD_BHD_BTCH_RecipeName_value, BHD_BHD_BTCH_StatisticsON_value, BHD_CTRL_COLD_DiameterLSL_value, BHD_CTRL_COLD_DiameterSP_value, BHD_CTRL_COLD_DiameterUSL_value, BHD_CTRL_COLD_OvalityUSL_value, Cp_COLD_Diameter, CpK_COLD_Diameter, CpU_COLD_Ovality FROM dbo.ViewBHD_Report   GO ALTER TABLE [dbo].[BHD] ADD CONSTRAINT [DF_BHD_BHD_BHD_BTCH_BatchID_value] DEFAULT ((0)) FOR [BHD_BHD_BTCH_BatchID_value] GO ALTER TABLE [dbo].[DLG] WITH CHECK ADD CONSTRAINT [FK_DLG_BHD] FOREIGN KEY([DLG_BHD_BTCH_BatchID_value]) REFERENCES [dbo].[BHD] ([ID]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[DLG] CHECK CONSTRAINT [FK_DLG_BHD] GO /****** Object: Trigger [dbo].[trLRMBatchID_OnInsert] Script Date: 16/08/2016 13:41:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo].[trLRMBatchID_OnInsert] ON [dbo].[BHD] AFTER INSERT AS BEGIN   declare @Temp nvarchar(200)       UPDATE [dbo].[BHD]   set @Temp = [ID], [BHD_BHD_BTCH_BatchID_value] =@Temp where [BHD_BHD_BTCH_BatchID_value]= 0   END   GO /****** Object: Trigger [dbo].[trLRMBatchID_OnInsert1] Script Date: 16/08/2016 13:41:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create TRIGGER [dbo].[trLRMBatchID_OnInsert1] ON [dbo].[LRM] AFTER INSERT AS BEGIN   declare @Alarm nvarchar(200) declare @AlarmTruncated nvarchar(200) declare @BatchID bigint declare @Length bigint declare @PosLength integer declare @PosAlarm integer     UPDATE [dbo].[LRM1]   set   @Alarm = i.[AlarmText], @PosLength = CHARINDEX('|',@Alarm,1)+ 1, @PosAlarm = CHARINDEX('|',@Alarm,@PosLength)+ 1, @AlarmTruncated = SUBSTRING(@Alarm,@PosAlarm,100), @BatchID = convert(bigint,SUBSTRING(@Alarm,1,@PosLength-2)), @Length = convert(bigint,SUBSTRING(@Alarm,@PosLength,@PosAlarm-@PosLength-1)), AlarmText = @AlarmTruncated, [LRM_BHD_BTCH_BatchID_value] = @BatchID, [LRM_BHD_BTCH_Length_value] = @Length   FROM Inserted i WHERE [dbo].[LRM1].LRM_ID = i.LRM_ID     END   GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] Begin DesignProperties = Begin PaneConfigurations = Begin PaneConfiguration = 0 NumPanes = 4 Configuration = "(H (1[5] 4[29] 2[30] 3) )" End Begin PaneConfiguration = 1 NumPanes = 3 Configuration = "(H (1 [50] 4 [25] 3))" End Begin PaneConfiguration = 2 NumPanes = 3 Configuration = "(H (1 [50] 2 [25] 3))" End Begin PaneConfiguration = 3 NumPanes = 3 Configuration = "(H (4 [30] 2 [40] 3))" End Begin PaneConfiguration = 4 NumPanes = 2 Configuration = "(H (1 [56] 3))" End Begin PaneConfiguration = 5 NumPanes = 2 Configuration = "(H (2 [66] 3))" End Begin PaneConfiguration = 6 NumPanes = 2 Configuration = "(H (4 [50] 3))" End Begin PaneConfiguration = 7 NumPanes = 1 Configuration = "(V (3))" End Begin PaneConfiguration = 8 NumPanes = 3 Configuration = "(H (1[56] 4[18] 2) )" End Begin PaneConfiguration = 9 NumPanes = 2 Configuration = "(H (1 [75] 4))" End Begin PaneConfiguration = 10 NumPanes = 2 Configuration = "(H (1[66] 2) )" End Begin PaneConfiguration = 11 NumPanes = 2 Configuration = "(H (4 [60] 2))" End Begin PaneConfiguration = 12 NumPanes = 1 Configuration = "(H (1) )" End Begin PaneConfiguration = 13 NumPanes = 1 Configuration = "(V (4))" End Begin PaneConfiguration = 14 NumPanes = 1 Configuration = "(V (2))" End ActivePaneConfig = 0 End Begin DiagramPane = Begin Origin = Top = 0 Left = 0 End Begin Tables = End End Begin SQLPane = End Begin DataPane = Begin ParameterDefaults = "" End Begin ColumnWidths = 9 Width = 284 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 End End Begin CriteriaPane = Begin ColumnWidths = 11 Column = 1440 Alias = 900 Table = 1176 Output = 720 Append = 1400 NewValue = 1170 SortType = 1356 SortOrder = 1416 GroupBy = 1350 Filter = 1356 Or = 1350 Or = 1350 Or = 1350 End End End ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'TestView1' GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'TestView1' GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] Begin DesignProperties = Begin PaneConfigurations = Begin PaneConfiguration = 0 NumPanes = 4 Configuration = "(H (1[8] 4[45] 2[6] 3) )" End Begin PaneConfiguration = 1 NumPanes = 3 Configuration = "(H (1 [50] 4 [25] 3))" End Begin PaneConfiguration = 2 NumPanes = 3 Configuration = "(H (1 [50] 2 [25] 3))" End Begin PaneConfiguration = 3 NumPanes = 3 Configuration = "(H (4 [30] 2 [40] 3))" End Begin PaneConfiguration = 4 NumPanes = 2 Configuration = "(H (1 [56] 3))" End Begin PaneConfiguration = 5 NumPanes = 2 Configuration = "(H (2 [66] 3))" End Begin PaneConfiguration = 6 NumPanes = 2 Configuration = "(H (4 [50] 3))" End Begin PaneConfiguration = 7 NumPanes = 1 Configuration = "(V (3))" End Begin PaneConfiguration = 8 NumPanes = 3 Configuration = "(H (1[56] 4[18] 2) )" End Begin PaneConfiguration = 9 NumPanes = 2 Configuration = "(H (1 [75] 4))" End Begin PaneConfiguration = 10 NumPanes = 2 Configuration = "(H (1[66] 2) )" End Begin PaneConfiguration = 11 NumPanes = 2 Configuration = "(H (4 [60] 2))" End Begin PaneConfiguration = 12 NumPanes = 1 Configuration = "(H (1) )" End Begin PaneConfiguration = 13 NumPanes = 1 Configuration = "(V (4))" End Begin PaneConfiguration = 14 NumPanes = 1 Configuration = "(V (2))" End ActivePaneConfig = 0 End Begin DiagramPane = Begin Origin = Top = 0 Left = 0 End Begin Tables = Begin Table = "ViewBHD_Report" Begin Extent = Top = 6 Left = 38 Bottom = 300 Right = 329 End DisplayFlags = 280 TopColumn = 4 End End End Begin SQLPane = End Begin DataPane = Begin ParameterDefaults = "" End Begin ColumnWidths = 28 Width = 284 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 End End Begin CriteriaPane = Begin ColumnWidths = 11 Column = 6084 Alias = 7728 Table = 1176 Output = 720 Append = 1400 NewValue = 1170 SortType = 1356 SortOrder = 1416 GroupBy = 1350 Filter = 2952 Or = 1350 Or = 1350 Or = 1350 End End End ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_Report' GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_Report' GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] Begin DesignProperties = Begin PaneConfigurations = Begin PaneConfiguration = 0 NumPanes = 4 Configuration = "(H (1[6] 4[37] 2[38] 3) )" End Begin PaneConfiguration = 1 NumPanes = 3 Configuration = "(H (1 [50] 4 [25] 3))" End Begin PaneConfiguration = 2 NumPanes = 3 Configuration = "(H (1 [50] 2 [25] 3))" End Begin PaneConfiguration = 3 NumPanes = 3 Configuration = "(H (4 [30] 2 [40] 3))" End Begin PaneConfiguration = 4 NumPanes = 2 Configuration = "(H (1 [56] 3))" End Begin PaneConfiguration = 5 NumPanes = 2 Configuration = "(H (2 [66] 3))" End Begin PaneConfiguration = 6 NumPanes = 2 Configuration = "(H (4 [50] 3))" End Begin PaneConfiguration = 7 NumPanes = 1 Configuration = "(V (3))" End Begin PaneConfiguration = 8 NumPanes = 3 Configuration = "(H (1[56] 4[18] 2) )" End Begin PaneConfiguration = 9 NumPanes = 2 Configuration = "(H (1 [75] 4))" End Begin PaneConfiguration = 10 NumPanes = 2 Configuration = "(H (1[66] 2) )" End Begin PaneConfiguration = 11 NumPanes = 2 Configuration = "(H (4 [60] 2))" End Begin PaneConfiguration = 12 NumPanes = 1 Configuration = "(H (1) )" End Begin PaneConfiguration = 13 NumPanes = 1 Configuration = "(V (4))" End Begin PaneConfiguration = 14 NumPanes = 1 Configuration = "(V (2))" End ActivePaneConfig = 0 End Begin DiagramPane = Begin Origin = Top = 0 Left = -2416 End Begin Tables = Begin Table = "BHD" Begin Extent = Top = 7 Left = 48 Bottom = 323 Right = 437 End DisplayFlags = 280 TopColumn = 2 End Begin Table = "ViewDLG_Aggregate" Begin Extent = Top = 7 Left = 485 Bottom = 367 Right = 830 End DisplayFlags = 280 TopColumn = 0 End End End Begin SQLPane = End Begin DataPane = Begin ParameterDefaults = "" End Begin ColumnWidths = 24 Width = 284 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 End End Begin CriteriaPane = Begin ColumnWidths = 11 Column = 22500 Alias = 2880 Table = 1176 Output = 720 Append = 1400 NewValue = 1170 SortType = 1356 SortOrder = 1416 GroupBy = 1356 Filter = 1356 Or = 1350 Or = 1350 Or = 1350 End End End ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'ViewBHD_Report' GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'ViewBHD_Report' GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] Begin DesignProperties = Begin PaneConfigurations = Begin PaneConfiguration = 0 NumPanes = 4 Configuration = "(H (1[17] 4[44] 2[20] 3) )" End Begin PaneConfiguration = 1 NumPanes = 3 Configuration = "(H (1 [50] 4 [25] 3))" End Begin PaneConfiguration = 2 NumPanes = 3 Configuration = "(H (1 [50] 2 [25] 3))" End Begin PaneConfiguration = 3 NumPanes = 3 Configuration = "(H (4 [30] 2 [40] 3))" End Begin PaneConfiguration = 4 NumPanes = 2 Configuration = "(H (1 [56] 3))" End Begin PaneConfiguration = 5 NumPanes = 2 Configuration = "(H (2 [66] 3))" End Begin PaneConfiguration = 6 NumPanes = 2 Configuration = "(H (4 [50] 3))" End Begin PaneConfiguration = 7 NumPanes = 1 Configuration = "(V (3))" End Begin PaneConfiguration = 8 NumPanes = 3 Configuration = "(H (1[56] 4[18] 2) )" End Begin PaneConfiguration = 9 NumPanes = 2 Configuration = "(H (1 [75] 4))" End Begin PaneConfiguration = 10 NumPanes = 2 Configuration = "(H (1[66] 2) )" End Begin PaneConfiguration = 11 NumPanes = 2 Configuration = "(H (4 [60] 2))" End Begin PaneConfiguration = 12 NumPanes = 1 Configuration = "(H (1) )" End Begin PaneConfiguration = 13 NumPanes = 1 Configuration = "(V (4))" End Begin PaneConfiguration = 14 NumPanes = 1 Configuration = "(V (2))" End ActivePaneConfig = 0 End Begin DiagramPane = Begin Origin = Top = 0 Left = 0 End Begin Tables = Begin Table = "DLG" Begin Extent = Top = 6 Left = 38 Bottom = 319 Right = 810 End DisplayFlags = 280 TopColumn = 0 End End End Begin SQLPane = End Begin DataPane = Begin ParameterDefaults = "" End Begin ColumnWidths = 9 Width = 284 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 Width = 1200 End End Begin CriteriaPane = Begin ColumnWidths = 12 Column = 4884 Alias = 4452 Table = 1176 Output = 720 Append = 1400 NewValue = 1170 SortType = 1356 SortOrder = 1416 GroupBy = 1350 Filter = 1356 Or = 1350 Or = 1350 Or = 1350 End End End ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'ViewDLG_Aggregate' GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'ViewDLG_Aggregate' GO USE [master] GO ALTER DATABASE [Dexline_10] SET READ_WRITE GO

---

Merci :)

Pour le probléme d'indexe qui jump de 10000 ou 1000 j'ai trouvé ( via google bien sur :lol:)

j'ai mis -t272 dans les réglages SQL , apparemment un bug SQL 2012