Hi, I have the following query :
SELECT PAC.PAC_PRONT,PAC.PAC_NOME, PC.CID_NOME CID_NOME_PC, PAC.pac_dreg, OSM.OSM_CNV, CNV.CNV_NOME, MAX ( FIXED ( DATEDIFF ( PAC.PAC_NASC, timestamp ) / 365, 5 ) ) FROM PAC, SMM, OSM, CNV, STR B, CID PC WHERE ( SMM.SMM_SFAT <> 'C' ) AND ( OSM.OSM_SERIE = SMM.SMM_OSM_SERIE ) AND ( OSM.OSM_NUM = SMM.SMM_OSM ) AND ( OSM.OSM_CNV = CNV.CNV_COD ) AND ( B.STR_COD = OSM.OSM_STR ) AND ( OSM.OSM_PAC = PAC.PAC_REG ) AND ( PAC.PAC_DIAG_CID = PC.CID_COD (+) ) AND ( ( PAC_DREG >= '1979-01-01 00:00:00' AND PAC_DREG < '2007-01-01 00:00:00' ) AND ( OSM.OSM_DTHR >= '2007-01-01 00:00:00' AND OSM.OSM_DTHR < '2007-02-01 00:00:00' ) ) AND ( B.STR_STR_COD = '1' ) GROUP BY PAC.PAC_PRONT, PAC.PAC_NOME, PC.CID_NOME, PAC.pac_dreg, OSM.OSM_CNV, CNV.CNV_NOME with the following explain: DBA PAC TABLE SCAN 8664 DBA SMM TABLE SCAN 5585 DBA OSM JOIN VIA MULTIPLE KEY COLUMNS 1355 OSM_SERIE (USED KEY COLUMN) OSM_NUM (USED KEY COLUMN) DBA CNV CNV_COD JOIN VIA KEY COLUMN 35 B STR_COD JOIN VIA KEY COLUMN 1 PC CID_COD JOIN VIA KEY RANGE 439 INTERNAL TEMPORARY RESULT TABLE SCAN 500 DBA RESULT IS COPIED , COSTVALUE IS 754813588 I tried to create a index with PAC_NASC, desc, to avoid the SCAN but it did not worked. Can you give me any help? This query is crashing SAP DB. Best regards, -- Daniel Castro - דניאל MSN: [EMAIL PROTECTED] Skype: danielti2005 ICQ : 316727989 Jabber: danielti Linux User: 387864 AOL User: Danielti2005 Sola Scriptura, Sola Gratia, Sola Fide, Solus Christus, Soli Deo Gloria
