SQL

April 26th, 2010 in Coding - 2 Comments

My ugliest SQL query — for posterity.

SELECT `wp_category`.`id` AS `category:id`, `wp_category`.`name` AS `category:name`, `wp_category`.`created` AS `category:created`, `wp_category`.`modified` AS `category:modified`, `wp_lib_items`.* FROM `wp_lib_items` LEFT OUTER JOIN `wp_lib_borrowers` ON (`wp_lib_items`.`id` = (SELECT `wp_b`.`id` FROM `wp_lib_borrowers` AS `wp_b` WHERE `wp_b`.`item_id` = wp_lib_items.id ORDER BY `wp_b`.`status` ASC LIMIT 1)) LEFT OUTER JOIN `wp_congregate_members` ON (`wp_lib_borrowers`.`member_id` = `wp_congregate_members`.`id`) LEFT JOIN `wp_lib_categories` AS `wp_category` ON (`wp_category`.`id` = `wp_lib_items`.`category_id`) ORDER BY IFNULL(NULLIF(`wp_lib_borrowers`.`due_date`, '0000-00-00'), '3333-33-33') DESC, `wp_lib_items`.`title` ASC LIMIT 10 OFFSET 0

Now I’m off to tango with PHP’s SplPriorityQueue.

2 Comment so far

  • Simeon
    September 23, 2010 at 9:21 am

    Try this for ugly:

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go
    
    
    
    
    
    
    ALTER PROCEDURE [dbo].[CustomReports_ManagerLicenseReport]
    	@SessionID INT
    AS
    
    -- Don't wait for locks.
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
    UPDATE [kronos].[dbo].[CUST_RPT_USAGE_Count]
       SET [UsageCount] = (SELECT [UsageCount]
    					   FROM [kronos].[dbo].[CUST_RPT_USAGE_Count] 
    					   WHERE [EXTERNAL_REPORT_NAME] = 'ManagerLicenseReport') + 1
    	WHERE [EXTERNAL_REPORT_NAME] = 'ManagerLicenseReport'
    
    DECLARE @Now DATETIME
    SET @Now = GETDATE()
    	DECLARE @sqlCommand VARCHAR(8000),
    	@Headcount INT,
    	@params nvarchar(4000),
    	@PERID INT
    
    	DELETE FROM TEMPCOUNT
    	DECLARE AG CURSOR FORWARD_ONLY 
    	FOR
    	SELECT DISTINCT JA.PERSONID
    	FROM dbo.JAIDS AS JA INNER JOIN
                        dbo.MYWTKEMPLOYEE ON JA.EMPLOYEEID = dbo.MYWTKEMPLOYEE.EMPLOYEEID
    					  WHERE (JA.PERSONID > 0) AND (JA.DELETEDSW = 0) AND EXISTS
                              (SELECT     PERSONID, LICENSETYPEID
                                FROM          dbo.PRSNLICTYPEMM
                                WHERE      (LICENSETYPEID = 3) AND (PRSNLICTYPEMM.PERSONID = JA.PERSONID)) AND (dbo.MYWTKEMPLOYEE.SESSIONID = @SessionID)
    	OPEN AG
    	FETCH NEXT FROM AG INTO @PERID
    		WHILE @@FETCH_STATUS=0
    		BEGIN
    			SELECT @sqlCommand =[dbo].[CustomReports_GetAccessGroupBy_TestErr](@PERID)
    			CREATE TABLE #temp(mycount int)
    			SET @sqlCommand = 'INSERT INTO #temp ' + @sqlCommand
    			EXEC (@sqlCommand)
    			--INSERT INTO ResultSt select @sqlCommand 
    			--Select @Headcount = mycount from #temp
    			--INSERT INTO TEMPCOUNT select @PERID, @Headcount
    			INSERT INTO TEMPCOUNT select @PERID, mycount from #temp
    			DROP TABLE #temp
    		FETCH NEXT FROM AG INTO @PERID
    		END
    	CLOSE AG
    	DEALLOCATE AG
    
    
    SELECT DISTINCT 
                        JA.PERSONID AS PersonID, 
    					PE.PERSONNUM AS PersonNum, 
    					PE.FULLNM AS PersonFullName, 
    					EA.EMAILADDRESSTXT AS EmailAddress, 
                        LA.LABORLEV1NM AS OrgUnitNum, 
    					LA.LABORLEV1DSC AS OrgUnitName, 
    					LA.LABORLEV4NM AS PerAdmin, 
    					LA.LABORLEV4DSC AS PerAdminDesc, 
                        AP.PROFILENM, 
                        CASE AP.PROFILENM WHEN '&SUPER_USER_ACCESS' THEN 'ALL HOMES' ELSE dbo.CustomReports_GetAccessGroup(PE.PERSONID) 
                        END AS AccessGroup, CASE AP.PROFILENM WHEN '&SUPER_USER_ACCESS' THEN dbo.HeadCountSU() 
                        ELSE dbo.TEMPCOUNT.HeadCOUNT END AS HeadCount, 
    					PN.PHONENUM AS PhoneNumber, 
    					dbo.CustomReports_GetAccessGroupBy(PE.PERSONID) AS AccessGroupBy, 
    					MAX(dbo.AUDITITEM.ENTEREDONDTM) AS LoginDate
    FROM         dbo.PHONENUMBER AS PN RIGHT OUTER JOIN
                      dbo.EMAILADDRESS AS EA RIGHT OUTER JOIN
    				  dbo.JAIDS AS JA INNER JOIN
    				  dbo.PERSON AS PE ON JA.PERSONID = PE.PERSONID INNER JOIN
    				  dbo.HOMEACCTHIST AS HH ON JA.EMPLOYEEID = HH.EMPLOYEEID INNER JOIN
    				  dbo.LABORACCT AS LA ON HH.LABORACCTID = LA.LABORACCTID AND GETDATE() BETWEEN HH.EFFECTIVEDTM AND ISNULL(HH.EXPIRATIONDTM, 
    				  GETDATE() + 1) - 1 INNER JOIN
    				  dbo.PRSNACCSASSIGN AS PA ON PE.PERSONID = PA.PERSONID INNER JOIN
    				  dbo.ACCESSPROFILE AS AP ON PA.ACCESSPROFID = AP.ACCESSPROFID ON EA.PERSONID = PE.PERSONID AND EA.EMAILADDRESSTXT =
    					  (SELECT     TOP (1) EMAILADDRESSTXT
                                FROM          dbo.EMAILADDRESS
                                WHERE      (PERSONID = PE.PERSONID)
                                ORDER BY UPDATEDTM DESC) ON PN.PERSONID = JA.PERSONID AND PN.PHONENUM =
                              (SELECT     TOP (1) PHONENUM
                                FROM          dbo.PHONENUMBER
                                WHERE      (PERSONID = JA.PERSONID) AND (CONTACTTYPEID = 4)
                                ORDER BY UPDATEDTM DESC) FULL OUTER JOIN
                      dbo.USERACCOUNT AS UA ON JA.PERSONID = UA.PERSONID INNER JOIN
                      dbo.MYWTKEMPLOYEE AS WE ON WE.PERSONID = UA.PERSONID INNER JOIN
                      dbo.DATASOURCE INNER JOIN
                      dbo.CLIENTCONTEXT ON dbo.DATASOURCE.CLIENTCONTEXTID = dbo.CLIENTCONTEXT.CLIENTCONTEXTID ON 
                      UA.USERACCOUNTNM = dbo.CLIENTCONTEXT.USERNAME INNER JOIN
                      dbo.AUDITITEM ON dbo.DATASOURCE.DATASOURCEID = dbo.AUDITITEM.DATASOURCEID LEFT OUTER JOIN
                      dbo.TEMPCOUNT ON LTRIM(RTRIM(EA.PERSONID)) = LTRIM(RTRIM(dbo.TEMPCOUNT.PersonID))
    WHERE     (JA.PERSONID > 0) AND (JA.DELETEDSW = 0) AND (WE.SESSIONID = @SessionID) AND EXISTS
                              (SELECT     PERSONID, LICENSETYPEID
                                FROM          dbo.PRSNLICTYPEMM
                                WHERE      (LICENSETYPEID = 3) AND (PERSONID = JA.PERSONID)) AND (dbo.AUDITITEM.AUDITTYPEID = 10)
    GROUP BY JA.PERSONID, PE.PERSONNUM, PE.FULLNM, EA.EMAILADDRESSTXT, LA.LABORLEV1NM, LA.LABORLEV1DSC, LA.LABORLEV4NM, 
                          LA.LABORLEV4DSC, AP.PROFILENM, PE.PERSONID, PN.PHONENUM, dbo.TEMPCOUNT.HeadCOUNT
  • September 23, 2010 at 9:23 am

    That’s more scary than ugly…

Add Your Comment

* Your Name

* Your Email

Your Website