December 7, 2011 at 5:17 pm
I'm working on a new view and could use some guidance. The task is to create a view which produces output to xml. I'll be stuck on "that" part when i get to it. For now, i'm uncertain about how to get the select statement to perform a lookup on values in four bit fields. If any of the following bit fields are true, then the view will need to look up the corresponding WarningCode in the corresponding table: WarningBoatCodesExist, WarningFishCodesExist, WarningHuntCodesExist, WarningQualityCodesExist.
For example, if a record indicates that a WarningQualityCodeExists flag is set to true, then you would use the uniquekey value of the BoatingWarning record to look into the BoatingWarningQaulityCodes table, comparing the BoatingWarning uniquekey value to the BoatingWarningUniqueFKey and find the WarningCode.
So how would the syntax look to get this lookup done? My guess is the use of a case expression? WarningBoatCodesExist case when 1 then...????
more info on what i'm trying to do: Below is the select portion of the create script that i have so far (still working on it). With regard to the bit fields, i need to check the value for the four bit fields ( dbo.BoatingWarning.WarningBoatCodesExist,
dbo.BoatingWarning.WarningFishCodesExist, dbo.BoatingWarning.WarningHuntCodesExist,
dbo.BoatingWarning.WarningQualityCodesExist) and if it is "1" (true) then i need to do a left outer join like the following:
WHEN 1 THEN BWQC = BoatingWarning.UniqueKey
FROM MobileFormsServer.dbo.BoatingWarning BoatingWarning
LEFT OUTER JOIN
MobileFormsServer.dbo.BoatingWarningQualityCodes
BoatingWarningQualityCodes
ON (BoatingWarning.UniqueKey =
BoatingWarningQualityCodes.BoatingWarningUniqueFKey)
dbo.BoatingWarningBoatCodes.BoatingWarningUniqueFKey,
dbo.BoatingWarning.UniqueKey
FROM dbo.BoatingWarning
and have it get the value of dbo.BoatingWarningBoatCodes.WarningCode based on that join. here is the full script:
/************************************************************
* Code formatted by James Hamel, DBA
* Time: 12/7/2011 3:52:53 PM
************************************************************/
USE [MobileFormsServer]
GO
/****** Object: View [dbo].[BOATWARNINGASXML] Script Date: 12/07/2011 15:52:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BOATWARNINGASXML]
AS
SELECT dbo.BoatingWarningBoatCodes.WarningCode AS BWBC,
dbo.BoatingWarningFishCodes.WarningCode AS BWFC,
dbo.BoatingWarningHuntCodes.WarningCode AS BWHC,
dbo.BoatingWarningQualityCodes.WarningCode AS BWQC,
dbo.BoatingWarning.CitationNo,
dbo.BoatingWarning.CitationDateTime,
dbo.BoatingWarning.LoginName,
dbo.BoatingWarning.UniqueUserID,
dbo.BoatingWarning.Void,
dbo.BoatingWarning.Companion,
dbo.BoatingWarning.CompanionNumberType,
dbo.BoatingWarning.CompanionNumber,
dbo.BoatingWarning.CompanionUniqueKey,
dbo.BoatingWarning.CountyOf,
dbo.BoatingWarning.CountyOfNo,
dbo.BoatingWarning.CityOf,
dbo.BoatingWarning.CityOfNo,
dbo.BoatingWarning.OfficerAgency,
dbo.BoatingWarning.Location,
dbo.BoatingWarning.Latitude,
dbo.BoatingWarning.Longitude,
dbo.BoatingWarning.MNINo,
dbo.BoatingWarning.NameFirst,
dbo.BoatingWarning.NameMiddle,
dbo.BoatingWarning.NameLast,
dbo.BoatingWarning.NameSuffix,
dbo.BoatingWarning.Street,
dbo.BoatingWarning.AddressOther,
dbo.BoatingWarning.AddDiffThanReg,
dbo.BoatingWarning.City,
dbo.BoatingWarning.State,
dbo.BoatingWarning.ZipCode,
dbo.BoatingWarning.Phone,
dbo.BoatingWarning.Race,
dbo.BoatingWarning.Ethnicity,
dbo.BoatingWarning.Sex,
dbo.BoatingWarning.Height,
dbo.BoatingWarning.DateOfBirth,
dbo.BoatingWarning.Weight,
dbo.BoatingWarning.Hair,
dbo.BoatingWarning.Eyes,
dbo.BoatingWarning.BusinessName,
dbo.BoatingWarning.BusinessPhone,
dbo.BoatingWarning.IDNo,
dbo.BoatingWarning.IDState,
dbo.BoatingWarning.IDType,
dbo.BoatingWarning.IDExpires,
dbo.BoatingWarning.VesselRegNo,
dbo.BoatingWarning.VesselRegState,
dbo.BoatingWarning.VesselRegExpires,
dbo.BoatingWarning.VesselDocNo,
dbo.BoatingWarning.VesselFuel,
dbo.BoatingWarning.VesselPropulsion,
dbo.BoatingWarning.VesselHP,
dbo.BoatingWarning.VesselYear,
dbo.BoatingWarning.VesselMake,
dbo.BoatingWarning.VesselType,
dbo.BoatingWarning.VesselLength,
dbo.BoatingWarning.VesselLengthType,
dbo.BoatingWarning.VesselColor,
dbo.BoatingWarning.VesselHIN,
dbo.BoatingWarning.VehicleYear,
dbo.BoatingWarning.VehicleMake,
dbo.BoatingWarning.VehicleModel,
dbo.BoatingWarning.VehicleTagNo,
dbo.BoatingWarning.VehicleTagNoState,
dbo.BoatingWarning.VehicleTagExpires,
dbo.BoatingWarning.VehicleVIN,
dbo.BoatingWarning.VehicleColor,
dbo.BoatingWarning.OfficerNotes,
dbo.BoatingWarning.OfficerOrgUnit,
dbo.BoatingWarning.OfficerName,
dbo.BoatingWarning.OfficerIDNo,
dbo.BoatingWarning.OfficerSignature,
dbo.BoatingWarning.UserCreatedDateTime,
dbo.BoatingWarning.Printed,
dbo.BoatingWarning.PrintedDateTime,
dbo.BoatingWarning.UserCompleted,
dbo.BoatingWarning.OfficerRank,
dbo.BoatingWarning.UserCompletedDateTime,
dbo.BoatingWarning.UserTransmitted,
dbo.BoatingWarning.UserTransmittedDateTime,
dbo.BoatingWarning.SystemTransmitAck,
dbo.BoatingWarning.SystemTransmitAckDateTime,
dbo.BoatingWarning.RuleNumber,
dbo.BoatingWarning.FishSpecies,
dbo.BoatingWarning.FishComments,
dbo.BoatingWarning.HuntSpecies,
dbo.BoatingWarning.HuntComments,
dbo.BoatingWarning.QualSpecies,
dbo.BoatingWarning.QualComments,
dbo.BoatingWarning.OtherViolation1,
dbo.BoatingWarning.OtherViolation2,
dbo.BoatingWarning.CompanionNTNumberType,
dbo.BoatingWarning.ReportStatus,
dbo.BoatingWarning.ViolationUniqueKey,
dbo.BoatingWarning.Violation,
dbo.BoatingWarning.ViolationTypeCode,
dbo.BoatingWarning.ViolationType,
dbo.BoatingWarning.ViolationLevelCode,
dbo.BoatingWarning.ViolationLevel,
dbo.BoatingWarning.ViolationLevelCourtAppearanceMandatory,
dbo.BoatingWarning.ViolationDescription,
dbo.BoatingWarning.ViolationInstructions,
dbo.BoatingWarning.ViolationCode,
dbo.BoatingWarning.CodeViolationUniqueKey,
dbo.BoatingWarning.CodeViolation,
dbo.BoatingWarning.CodeViolationDescription,
dbo.BoatingWarning.CodeViolationInstructions,
dbo.BoatingWarning.CodeViolationCounty,
dbo.BoatingWarning.DescriptionOfViolations,
dbo.BoatingWarning.OfficerAgencyGroup,
dbo.BoatingWarning.WarningBoatCodesExist,
dbo.BoatingWarning.WarningFishCodesExist,
dbo.BoatingWarning.WarningHuntCodesExist,
dbo.BoatingWarning.WarningQualityCodesExist
GO
Beyond all of this, i then need to get the view to provide it's output as xml using FOR XML
thanks for any help.
December 7, 2011 at 5:42 pm
Do a left join to each of those tables with the appropriate fields specified in the on clause. IN addition add 'and suchandsuchfield = 1'
e.g.
Left Outer Join BoatingWarningQaulityCodes
On BoatingWarning = BoatingWarningUniqueFKey
And WarningBoatCodesExist = 1
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 7, 2011 at 5:51 pm
Thank you. I've kind of had the sense that this is where i should be heading with this. Please pardon my confusion, but wouldn't i be using a case expression for each of the "dbo.BoatingWarning.WarningBoatCodesExist,
dbo.BoatingWarning.WarningFishCodesExist,
dbo.BoatingWarning.WarningHuntCodesExist,
dbo.BoatingWarning.WarningQualityCodesExist". that is to say dbo.BoatingWarning.WarningBoatCodesExist CASE when "1" then Left Outer Join BoatingWarningQaulityCodes
On BoatingWarning = BoatingWarningUniqueFKey
And WarningBoatCodesExist = 1
and repeat that for the other three, using the appropriate LOJ?
December 7, 2011 at 6:00 pm
For something like this you are probably better off doing 2 mutually exclusive queries and a UNION between them.
In the first one do something like:
-- OR the bits. This query would do the JOIN to the other table.
SELECT....
WHERE
WarningBoatCodesExist | WarningFishCodesExist | WarningHuntCodesExist | WarningQualityCodes = 1
UNION ALL
SELECT...
-- AND the bits. This query wouldn't have the JOIN.
WHERE
WarningBoatCodesExist & WarningFishCodesExist & WarningHuntCodesExist & WarningQualityCodes = 0
Since they are mutually exclusive conditions use UNION ALL rather than UNION.
Todd Fifield
December 7, 2011 at 6:13 pm
i will admit, now i'm feeling completely lost in the weeds and twisted around nine ways to sunday on this. My brain has been frazzled by this all day and i haven't even gotten to the xml part of this yet. any more specificity on these solutions would be appreciated, immensely. i can conceptualize, in real world terms, what i'm trying to do, but translating that to T-sql with all it's intricasies is killing me.
December 7, 2011 at 6:25 pm
breakwater,
Is there something you didn't get about using a UNION ALL query? I'd be happy to explain.
Your original post doesn't really have the full query and it's very difficult to read - looks like you were using a query designer tool and they mostly put out hard to read queries.
Conceptually, you want 2 queries with UNION ALL stuck between them. They would be identical except that one would want a 1 in any of the 4 bit columns. This is the one that does the JOIN to the other table.
In the second query you want the 4 bit columns to all be zero. I actually made a mistake in my previous post when I said to do an AND of the 4 bit columns. I think you actually want the same OR condition on the 4 bit columns where all bits = 0.
If you could post the full query I would be happy to show you how.
Todd Fifield
December 7, 2011 at 6:38 pm
Thank you. It's just been a long day and i'm frazzled. The full query (as much of it as i have now) is posted below. The bulk of it was put together by Mgmt studio (the select portion and the syntax for the create).
I am confused about the syntax needed for those bit fields and also the way in which i need to compose the code that outputs as xml. I do know that such particular code includes 'FOR XML AUTO, ELEMENTS, TYPE', but i'm not finding much else on what other code i need to include in that.
thank you again.
James
USE [MobileFormsServer]
GO
/***** Object: View [dbo].[BoatingWarningasXML] Script Date: 12/07/2011 19:38:18 *****/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BoatingWarningasXML]
AS
SELECT dbo.BoatingWarning.UniqueKey, dbo.BoatingWarning.CitationNo, dbo.BoatingWarning.CitationDateTime, dbo.BoatingWarning.LoginName,
dbo.BoatingWarning.UniqueUserID, dbo.BoatingWarning.Void, dbo.BoatingWarning.Companion, dbo.BoatingWarning.CompanionNumberType,
dbo.BoatingWarning.CompanionNumber, dbo.BoatingWarning.CompanionUniqueKey, dbo.BoatingWarning.CountyOf, dbo.BoatingWarning.CountyOfNo,
dbo.BoatingWarning.CityOf, dbo.BoatingWarning.CityOfNo, dbo.BoatingWarning.OfficerAgency, dbo.BoatingWarning.Location, dbo.BoatingWarning.Latitude,
dbo.BoatingWarning.Longitude, dbo.BoatingWarning.MNINo, dbo.BoatingWarning.NameFirst, dbo.BoatingWarning.NameMiddle, dbo.BoatingWarning.NameLast,
dbo.BoatingWarning.NameSuffix, dbo.BoatingWarning.Street, dbo.BoatingWarning.AddressOther, dbo.BoatingWarning.AddDiffThanReg, dbo.BoatingWarning.City,
dbo.BoatingWarning.State, dbo.BoatingWarning.ZipCode, dbo.BoatingWarning.Phone, dbo.BoatingWarning.DateOfBirth, dbo.BoatingWarning.Race,
dbo.BoatingWarning.Ethnicity, dbo.BoatingWarning.Sex, dbo.BoatingWarning.Height, dbo.BoatingWarning.Weight, dbo.BoatingWarning.Hair,
dbo.BoatingWarning.Eyes, dbo.BoatingWarning.BusinessName, dbo.BoatingWarning.BusinessPhone, dbo.BoatingWarning.IDNo, dbo.BoatingWarning.IDState,
dbo.BoatingWarning.IDType, dbo.BoatingWarning.IDExpires, dbo.BoatingWarning.VesselRegNo, dbo.BoatingWarning.VesselRegState,
dbo.BoatingWarning.VesselRegExpires, dbo.BoatingWarning.VesselDocNo, dbo.BoatingWarning.VesselFuel, dbo.BoatingWarning.VesselPropulsion,
dbo.BoatingWarning.VesselHP, dbo.BoatingWarning.VesselYear, dbo.BoatingWarning.VesselMake, dbo.BoatingWarning.VesselType,
dbo.BoatingWarning.VesselLength, dbo.BoatingWarning.VesselLengthType, dbo.BoatingWarning.VesselColor, dbo.BoatingWarning.VesselHIN,
dbo.BoatingWarning.VehicleYear, dbo.BoatingWarning.VehicleMake, dbo.BoatingWarning.VehicleModel, dbo.BoatingWarning.VehicleTagNo,
dbo.BoatingWarning.VehicleTagNoState, dbo.BoatingWarning.VehicleTagExpires, dbo.BoatingWarning.VehicleVIN, dbo.BoatingWarning.VehicleColor,
dbo.BoatingWarning.OfficerNotes, dbo.BoatingWarning.OfficerOrgUnit, dbo.BoatingWarning.OfficerRank, dbo.BoatingWarning.OfficerName,
dbo.BoatingWarning.OfficerIDNo, dbo.BoatingWarning.OfficerSignature, dbo.BoatingWarning.UserCreatedDateTime, dbo.BoatingWarning.Printed,
dbo.BoatingWarning.PrintedDateTime, dbo.BoatingWarning.UserCompleted, dbo.BoatingWarning.UserCompletedDateTime, dbo.BoatingWarning.UserTransmitted,
dbo.BoatingWarning.UserTransmittedDateTime, dbo.BoatingWarning.SystemTransmitAck, dbo.BoatingWarning.SystemTransmitAckDateTime,
dbo.BoatingWarning.RuleNumber, dbo.BoatingWarning.FishSpecies, dbo.BoatingWarning.FishComments, dbo.BoatingWarning.HuntSpecies,
dbo.BoatingWarning.HuntComments, dbo.BoatingWarning.QualSpecies, dbo.BoatingWarning.QualComments, dbo.BoatingWarning.OtherViolation1,
dbo.BoatingWarning.OtherViolation2, dbo.BoatingWarning.CompanionNTNumberType, dbo.BoatingWarning.ReportStatus, dbo.BoatingWarning.ViolationUniqueKey,
dbo.BoatingWarning.Violation, dbo.BoatingWarning.ViolationTypeCode, dbo.BoatingWarning.ViolationType, dbo.BoatingWarning.ViolationLevelCode,
dbo.BoatingWarning.ViolationLevel, dbo.BoatingWarning.ViolationLevelCourtAppearanceMandatory, dbo.BoatingWarning.ViolationDescription,
dbo.BoatingWarning.ViolationInstructions, dbo.BoatingWarning.ViolationCode, dbo.BoatingWarning.CodeViolationUniqueKey, dbo.BoatingWarning.CodeViolation,
dbo.BoatingWarning.CodeViolationDescription, dbo.BoatingWarning.CodeViolationInstructions, dbo.BoatingWarning.CodeViolationCounty,
dbo.BoatingWarning.DescriptionOfViolations, dbo.BoatingWarning.OfficerAgencyGroup, dbo.BoatingWarning.WarningBoatCodesExist,
dbo.BoatingWarning.WarningFishCodesExist, dbo.BoatingWarning.WarningHuntCodesExist, dbo.BoatingWarning.WarningQualityCodesExist,
dbo.BoatingWarningBoatCodes.BoatingWarningUniqueFKey, dbo.BoatingWarningFishCodes.BoatingWarningUniqueFKey AS Expr1,
dbo.BoatingWarningQualityCodes.BoatingWarningUniqueFKey AS Expr2, dbo.BoatingWarningHuntCodes.BoatingWarningUniqueFKey AS Expr3,
dbo.BoatingWarningHuntCodes.WarningCode, dbo.BoatingWarningQualityCodes.WarningCode AS Expr4, dbo.BoatingWarningBoatCodes.WarningCode AS Expr5,
dbo.BoatingWarningFishCodes.WarningCode AS Expr6
FROM dbo.BoatingWarning LEFT OUTER JOIN
dbo.BoatingWarningBoatCodes ON dbo.BoatingWarning.UniqueKey = dbo.BoatingWarningBoatCodes.BoatingWarningUniqueFKey LEFT OUTER JOIN
dbo.BoatingWarningHuntCodes ON dbo.BoatingWarning.UniqueKey = dbo.BoatingWarningHuntCodes.BoatingWarningUniqueFKey LEFT OUTER JOIN
dbo.BoatingWarningQualityCodes ON dbo.BoatingWarning.UniqueKey = dbo.BoatingWarningQualityCodes.BoatingWarningUniqueFKey LEFT OUTER JOIN
dbo.BoatingWarningFishCodes ON dbo.BoatingWarning.UniqueKey = dbo.BoatingWarningFishCodes.BoatingWarningUniqueFKey
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[40] 4[20] 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 = 12
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "BoatingWarning"
Begin Extent =
Top = 7
Left = 0
Bottom = 126
Right = 291
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "BoatingWarningBoatCodes"
Begin Extent =
Top = 432
Left = 32
Bottom = 551
Right = 254
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "BoatingWarningFishCodes"
Begin Extent =
Top = 54
Left = 648
Bottom = 173
Right = 870
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "BoatingWarningHuntCodes"
Begin Extent =
Top = 494
Left = 325
Bottom = 613
Right = 547
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "BoatingWarningQualityCodes"
Begin Extent =
Top = 432
Left = 631
Bottom = 551
Right = 853
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
PaneHidden =
End
Begin DataPane =
PaneHidden =
Begin ParameterDefaults = ""
End
End
Begin CriteriaPane =
PaneHidden =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 13' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'BoatingWarningasXML'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'50
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'BoatingWarningasXML'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'BoatingWarningasXML'
GO
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply