aszsql = ""
aszsql = aszsql & "Select UD.SSN, UD.last_name, UD.first_name, UD.full_name,UD.Username,"
aszsql = aszsql & "UD.Age, UD.DOB, UD.Email,"
aszsql = aszsql & " Gender_lookup.Gender, group_lookup.group_name as group, "
aszsql = aszsql & " Departments.dept_name as Department ,"
aszsql = aszsql & "language_lookup.language as primary_language,groupcategory_lookup.Category_Name, "
aszsql = aszsql & "UD.currently_employed,Classification_lookup.Classification_name,"
aszsql = aszsql & "workGroup_Lookup.workGrouptype,"
aszsql = aszsql & "UD.groupLevel,UD.Last_HRappt,UD.hireYear,UD.HoursWorked,UD.rating, "
aszsql = aszsql & "tbl2.logintime, UD1.full_name as supervisorName,tbl1.CreatedOn, "
aszsql = aszsql & "II.item_title as itemText, IC.item_committment as committment,IC.itemID,IC.committmentID"
aszsql = aszsql & " FROM item_committment IC INNER JOIN"
aszsql = aszsql & " invoice_items II ON IC.itemID = II.itemID INNER JOIN"
aszsql = aszsql & " (SELECT COM.ID,COM.CreatedOn, COM.Username,workFlow_item.createdby"
aszsql = aszsql & " FROM committment COM INNER JOIN workFlow_item ON "
aszsql = aszsql & "workFlow_item.wfID = COM.source_wfID"
aszsql = aszsql & " WHERE wfCode = '" & wfType & "'"
aszsql = aszsql & " AND (wfstatus = 'done' OR wfstatus = 'arch' OR wfstatus = 'inar')"
aszsql = aszsql & " AND createdby IN (SELECT uid"
aszsql = aszsql & " " & extraQuery & " )"
aszsql = aszsql & " AND COM.createdon <= cast('" & todate & " 11:59 PM' as smalldatetime) AND COM.createdon >= '"
aszsql = aszsql & fromdate & "' AND COM.invoiceID = " & invoiceID
aszsql = aszsql & " ) tbl1 ON IC.committmentID = tbl1.ID"
aszsql = aszsql & " Inner Join UserData UD ON UD.UID = tbl1.createdby Left Outer Join (Select max(logintime) as "
aszsql = aszsql & "logintime,username from LoginLog group by username) tbl2 on tbl2.username = UD.uid "
aszsql = aszsql & "LEFT OUTER JOIN Gender_lookup ON UD.Gender = Gender_Lookup.ID LEFT OUTER JOIN "
aszsql = aszsql & "group_lookup ON UD.group = group_Lookup.ID LEFT OUTER JOIN "
aszsql = aszsql & "Departments ON UD.Department = Departments.ID LEFT OUTER JOIN "
aszsql = aszsql & "Language_lookup ON UD.Primary_language = Language_lookup.ID Left outer "
aszsql = aszsql & "Join Classification_Lookup on UD.classID = Classification_lookup.ID Left Outer"
aszsql = aszsql & " Join workGroup_Lookup on UD.workGroupID = workGroup_Lookup.ID Left "
aszsql = aszsql & " Outer Join groupcategory_lookup on UD.groupcategory = groupcategory_Lookup.ID "
aszsql = aszsql & "INNER JOIN UserData UD1 "
aszsql = aszsql & " ON UD1.uid = UD1.uid order by tbl1.ID,II.sort_order"
You see, the fact that it's all broken up into a bunch of lines makes it readable... so very very readable. And just in case you forgot to make it complex enough, you can add big chunks of SQL at the "extraQuery" variable.
Wow.
(submitted by "sk")
Wednesday, October 7, 2009
Subscribe to:
Post Comments (Atom)
Posts
3 comments:
That looks familiar...
sSQL = "SET NOCOUNT ON "
sSQL = sSQL & "IF OBJECT_ID('tempdb..#TmpPatterns') IS NOT NULL "
sSQL = sSQL & "DROP TABLE #TmpPatterns "
sSQL = sSQL & "SELECT PATTERN_ID INTO #TmpPatterns FROM "
sSQL = sSQL & "BLACKOUT_PATTERNS "
sSQL = sSQL & "WHERE PATTERN_ZIP = '" & strZIP & "' "
sSQL = sSQL & "CREATE UNIQUE CLUSTERED INDEX IX_TMP_PATTERN ON #TmpPatterns (PATTERN_ID) "
sSQL = sSQL & "IF OBJECT_ID('tempdb..#TmpEvents') IS NOT NULL "
sSQL = sSQL & "DROP TABLE #TmpEvents "
sSQL = sSQL & "SELECT GAME_DATE, GAME_DESCRIPTION, GAME_TIME, RTRIM(LTRIM(LEFT(GAME_CHANNEL, CHARINDEX(':', GAME_CHANNEL)-1))) AS CHAN_NUM, "
sSQL = sSQL & "GAME_CHANNEL, GAME_TYPE, GAME_UNAFFECTED, GAME_BLACKOUT INTO #TmpEvents FROM "
sSQL = sSQL & "SPORTING_EVENTS "
sSQL = sSQL & "WHERE GAME_DATE >= GETDATE()-1 "
sSQL = sSQL & "AND GAME_DATE < GETDATE()+2 "
IF NOT strEvent = "" THEN sSQL = sSQL & "AND GAME_DESCRIPTION LIKE '%" & strEvent & "%' "
sSQL = sSQL & "CREATE INDEX IX_TMP_GAME_TIME ON #TmpEvents(GAME_TIME) "
sSQL = sSQL & "CREATE INDEX IX_TMP_GAME_DATE ON #TmpEvents(GAME_DATE) "
sSQL = sSQL & "SELECT DISTINCT GAME_DATE, GAME_DESCRIPTION, GAME_TIME, EVENTS.CHAN_NUM, MAPDOWN_CHAN, STATION_NAME, GAME_CHANNEL, GAME_TYPE FROM( "
sSQL = sSQL & "SELECT GAME_DATE, GAME_DESCRIPTION, GAME_TIME, CHAN_NUM, GAME_CHANNEL, GAME_TYPE FROM #TmpEvents WHERE "
sSQL = sSQL & "GAME_UNAFFECTED = '' AND GAME_BLACKOUT NOT IN( "
sSQL = sSQL & "SELECT PATTERN_ID FROM #TmpPatterns) "
sSQL = sSQL & "UNION SELECT GAME_DATE, GAME_DESCRIPTION, GAME_TIME, CHAN_NUM, GAME_CHANNEL, GAME_TYPE FROM #TmpEvents WHERE "
sSQL = sSQL & "GAME_BLACKOUT = '' AND GAME_UNAFFECTED IN( "
sSQL = sSQL & "SELECT PATTERN_ID FROM #TmpPatterns) "
sSQL = sSQL & "UNION SELECT GAME_DATE, GAME_DESCRIPTION, GAME_TIME, CHAN_NUM, GAME_CHANNEL, GAME_TYPE FROM #TmpEvents WHERE "
sSQL = sSQL & "GAME_BLACKOUT = '' AND GAME_UNAFFECTED = '') EVENTS, "
sSQL = sSQL & "(SELECT CHAN_NUM, MAPDOWN_CHAN, STATION_NAME FROM PROG_DIRECTORY "
sSQL = sSQL & "WHERE DMA_NUM IS NULL "
sSQL = sSQL & "UNION SELECT CHAN_NUM, MAPDOWN_CHAN, STATION_NAME FROM PROG_DIRECTORY "
sSQL = sSQL & "WHERE DMA_NUM IS NOT NULL "
sSQL = sSQL & "AND DMA_NUM IN "
sSQL = sSQL & "(SELECT DMA_NUM FROM ZIP_TO_DMA WHERE ZIP = '" & strZIP & "')) CHANS "
sSQL = sSQL & "WHERE CHANS.CHAN_NUM = EVENTS.CHAN_NUM "
if request.QueryString("orderby") & "" <> "" then sSQL = sSQL & "ORDER BY " & request.QueryString("orderby") & " "
sSQL = sSQL & "DROP TABLE #TmpPatterns "
sSQL = sSQL & "DROP TABLE #TmpEvents "
sSQL = sSQL & "SET NOCOUNT OFF "
Set RS = dbConn.Execute(sSQL)
Sweet... apparently repeatedly concatenating strings is par for the course in VBscript. Is this a standard?
I also noticed that both the post code (and this comment) use some kind of Hungarian Notation... weakly (variant) typed languages FTW!
To be more precise, you MUST add more SQL "at" the extraQuery variable.
extraQuery aside, this query concatenation pattern is extremely common.
Post a Comment