xml in sql server 2008 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

xml in sql server 2008

Author

Topic

TajKazi
Posting Yak Master

101 Posts

Posted - 2015-01-20 : 00:47:43

ddl script-----------

CREATE TABLE [dbo].tblrum(
[Id] [int] IDENTITY(1,1) NOT NULL,
[RoleId] [int] NOT NULL,
[PersonId] [int] NOT NULL,
[ProfitCenterCode] [varchar](25) NULL,
[CostCenterCode] [varchar](25) NULL,
[LocationCode] [int] NULL,
[OrgEntityCode] [varchar](10) NULL,
[Active] [int] NOT NULL,
[CreatedBy] [int] NOT NULL,
[CreatedOn] [datetime] NOT NULL,
CONSTRAINT [pk_rum_Id1] 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]
SET IDENTITY_INSERT [dbo].tblrum ON

INSERT [dbo].tblrum ([Id], [RoleId], [PersonId], [ProfitCenterCode], [CostCenterCode], [LocationCode], [OrgEntityCode], [Active], [CreatedBy], [CreatedOn]) VALUES (719, 89, 1129, N'F7100', N'71007122', 100, N'3736', 1, 34114, CAST(0x00009EA301357C20 AS DateTime))
INSERT [dbo].tblrum ([Id], [RoleId], [PersonId], [ProfitCenterCode], [CostCenterCode], [LocationCode], [OrgEntityCode], [Active], [CreatedBy], [CreatedOn]) VALUES (720, 89, 1593, N'100', N'100', 100, N'3736', 1, 32625, CAST(0x00009C2500A9860C AS DateTime))
INSERT [dbo].tblrum ([Id], [RoleId], [PersonId], [ProfitCenterCode], [CostCenterCode], [LocationCode], [OrgEntityCode], [Active], [CreatedBy], [CreatedOn]) VALUES (721, 89, 31067, N'100', N'100', 100, N'3736', 1, 30698, CAST(0x00009B4A01078C5C AS DateTime))
INSERT [dbo].tblrum ([Id], [RoleId], [PersonId], [ProfitCenterCode], [CostCenterCode], [LocationCode], [OrgEntityCode], [Active], [CreatedBy], [CreatedOn]) VALUES (722, 89, 32177, N'100', N'100', 100, N'3736', 1, 30698, CAST(0x00009B4A010736D0 AS DateTime))
INSERT [dbo].tblrum ([Id], [RoleId], [PersonId], [ProfitCenterCode], [CostCenterCode], [LocationCode], [OrgEntityCode], [Active], [CreatedBy], [CreatedOn]) VALUES (723, 89, 32880, N'100', N'100', 100, NULL, 1, 31064, CAST(0x00009FEA00B9EE0C AS DateTime))
INSERT [dbo].tblrum ([Id], [RoleId], [PersonId], [ProfitCenterCode], [CostCenterCode], [LocationCode], [OrgEntityCode], [Active], [CreatedBy], [CreatedOn]) VALUES (724, 89, 33341, N'100', N'100', 100, N'3736', 1, 32625, CAST(0x00009C5300F78924 AS DateTime))
INSERT [dbo].tblrum ([Id], [RoleId], [PersonId], [ProfitCenterCode], [CostCenterCode], [LocationCode], [OrgEntityCode], [Active], [CreatedBy], [CreatedOn]) VALUES (725, 89, 34607, N'100', N'100', 100, N'3736', 1, 34880, CAST(0x0000A37E0143B074 AS DateTime))
INSERT [dbo].tblrum ([Id], [RoleId], [PersonId], [ProfitCenterCode], [CostCenterCode], [LocationCode], [OrgEntityCode], [Active], [CreatedBy], [CreatedOn]) VALUES (726, 89, 34902, N'100', N'100', 100, N'3736', 1, 234, CAST(0x0000A32400E8FE18 AS DateTime))
INSERT [dbo].tblrum ([Id], [RoleId], [PersonId], [ProfitCenterCode], [CostCenterCode], [LocationCode], [OrgEntityCode], [Active], [CreatedBy], [CreatedOn]) VALUES (727, 89, 36254, N'100', N'100', 100, NULL, 1, 31064, CAST(0x00009F4700B31E4C AS DateTime))

CREATE TABLE [dbo].tblema(
[PersonId] [int] NOT NULL,
[EID] [varchar](12) NULL,
[NamePrefix] [varchar](5) NULL,
[NameFirst] [varchar](40) NULL,
[NameMiddle] [varchar](40) NULL,
[NameLast] [varchar](40) NULL,
[JobDescription] [varchar](50) NULL,
[BusinessTitle] [varchar](50) NULL,
[ProfitCenterCode] [varchar](10) NULL,
[CostCenterCode] [varchar](20) NULL,
[LegacyLocationId] [int] NULL,
[PeS_LocationCode] [varchar](10) NULL,
[Grade] [varchar](5) NULL,
[DateOfHire] [datetime] NULL,
[DateOfTermination] [datetime] NULL,
[DateOfRehire] [datetime] NULL,
[SupervisorEID] [varchar](12) NULL,
[SupervisorPersonId] [int] NULL,
[SalaryPlannerEID] [varchar](12) NULL,
[SalaryPlannerPersonId] [int] NULL,
[HrContactEID] [varchar](12) NULL,
[HrContactPersonId] [int] NULL,
[SBG] [varchar](20) NULL,
[SBU] [varchar](20) NULL,
[SBE] [varchar](20) NULL,
[SBX] [varchar](20) NULL,
[OrgEntity] [varchar](10) NULL,
[EmployerId] [int] NULL,
[ResidenceAddress1] [varchar](60) NULL,
[ResidenceAddress2] [varchar](60) NULL,
[ResidenceAddress3] [varchar](60) NULL,
[ResidenceAddress4] [varchar](60) NULL,
[ResidenceCity] [nvarchar](100) NULL,
[ResidenceStateId] [int] NULL,
[ResidencePostalCode] [varchar](20) NULL,
[PermanentAddress1] [varchar](60) NULL,
[PermanentAddress2] [varchar](60) NULL,
[PermanentAddress3] [varchar](60) NULL,
[PermanentAddress4] [varchar](60) NULL,
[PermanentCity] [nvarchar](100) NULL,
[PermanentStateId] [int] NULL,
[PermanentPostalCode] [varchar](20) NULL,
[PhoneMobile] [varchar](30) NULL,
[PhoneResidence] [varchar](30) NULL,
[PhoneOffice] [varchar](30) NULL,
[PhoneExtension] [varchar](20) NULL,
[EmailAddressOffice] [varchar](80) NULL,
[EmailAddressAlternate] [varchar](80) NULL,
[Sex] [char](5) NULL,
[BloodGroup] [varchar](5) NULL,
[DateOfBirth] [datetime] NULL,
[PAN] [varchar](20) NULL,
[PassportNumber] [varchar](50) NULL,
[BankCode] [varchar](10) NULL,
[BankAccountNo] [varchar](50) NULL,
[BankIFSC_Code] [varchar](20) NULL,
[ExEmployer] [varchar](50) NULL,
[PastExperience] [int] NULL,
[Qualification1] [varchar](60) NULL,
[YearOfPassing1] [varchar](10) NULL,
[Qualification2] [varchar](60) NULL,
[YearOfPassing2] [varchar](10) NULL,
[Qualification3] [varchar](60) NULL,
[YearOfPassing3] [varchar](10) NULL,
[LegacyEmpNo] [varchar](11) NULL,
[LegacyFunction] [varchar](50) NULL,
[HLoB] [varchar](10) NULL,
[HComments] [varchar](100) NULL,
[CostCenterFunction] [varchar](10) NULL,
[CurrentLocationId] [int] NULL,
[CurrentBuildingId] [int] NULL,
[CurrentFloorId] [int] NULL,
[CurrentSeatId] [nvarchar](50) NULL,
[CurrentAssetId] [varchar](100) NULL,
[IsValid] [int] NULL,
[SysComment] [varchar](200) NULL,
[Attribute0] [varchar](200) NULL,
[Attribute1] [varchar](200) NULL,
[Attribute2] [varchar](200) NULL,
[Attribute3] [varchar](200) NULL,
[Attribute4] [varchar](200) NULL,
[Attribute5] [varchar](200) NULL,
[Attribute6] [varchar](200) NULL,
[Attribute7] [varchar](200) NULL,
[Attribute8] [varchar](200) NULL,
[Attribute9] [varchar](200) NULL,
CONSTRAINT [pk_ema_PersonId1] PRIMARY KEY CLUSTERED
(
[PersonId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT [dbo].tblema ([PersonId], [EID], [NamePrefix], [NameFirst], [NameMiddle], [NameLast], [JobDescription], [BusinessTitle], [ProfitCenterCode], [CostCenterCode], [LegacyLocationId], [PeS_LocationCode], [Grade], [DateOfHire], [DateOfTermination], [DateOfRehire], [SupervisorEID], [SupervisorPersonId], [SalaryPlannerEID], [SalaryPlannerPersonId], [HrContactEID], [HrContactPersonId], [SBG], [SBU], [SBE], [SBX], [OrgEntity], [EmployerId], [ResidenceAddress1], [ResidenceAddress2], [ResidenceAddress3], [ResidenceAddress4], [ResidenceCity], [ResidenceStateId], [ResidencePostalCode], [PermanentAddress1], [PermanentAddress2], [PermanentAddress3], [PermanentAddress4], [PermanentCity], [PermanentStateId], [PermanentPostalCode], [PhoneMobile], [PhoneResidence], [PhoneOffice], [PhoneExtension], [EmailAddressOffice], [EmailAddressAlternate], [Sex], [BloodGroup], [DateOfBirth], [PAN], [PassportNumber], [BankCode], [BankAccountNo], [BankIFSC_Code], [ExEmployer], [PastExperience], [Qualification1], [YearOfPassing1], [Qualification2], [YearOfPassing2], [Qualification3], [YearOfPassing3], [LegacyEmpNo], [LegacyFunction], [HLoB], [HComments], [CostCenterFunction], [CurrentLocationId], [CurrentBuildingId], [CurrentFloorId], [CurrentSeatId], [CurrentAssetId], [IsValid], [SysComment], [Attribute0], [Attribute1], [Attribute2], [Attribute3], [Attribute4], [Attribute5], [Attribute6], [Attribute7], [Attribute8], [Attribute9]) VALUES (32177, N'E401243', N'Mr', N'Mahesh', N'Vinayak', N'ok', N'Sr. Sales Administration Super', N'Estimation Manager', N'H9036', N'51607060', 603, NULL, N'B3', CAST(0x0000999E00000000 AS DateTime), NULL, CAST(0x0000999E00000000 AS DateTime), N'E286749', 36254, N'E286749', 36254, N'E287494', 1022, N'ACS', N'HPS', N'TCOP', NULL, N'3736', 1, N'B 107, Om lala', N'Bibwewadi', N'-', N'-', NULL, NULL, N'411037', N'As above', N'-', N'-', N'-', NULL, NULL, N'-', N'9850978059', NULL, N'66072663', N'22663', N'Mahesh.Musale@moaney.com', N'Mahesh.V.kk@gmail.com', N'M ', N'B+', CAST(0x00006F1100000000 AS DateTime), N'AJWPM0707D', N'-', N'CITI', N'5046083708', NULL, N'Rosemount Tank Gauging I Pvt Ltd', 86, N'ME Marketing Mangt', N'2005', N'BE Electrical', N'2000', NULL, NULL, N'4422', N'Sales', N'0', NULL, N'CPS_0000', 603, 1, 30, N'ST-32177', N'IE03DTXP02173', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)

CREATE TABLE [dbo].tblcac(
[RoleId] [int] NOT NULL,
[Page] [varchar](150) NOT NULL,
[ColumnName] [varchar](150) NOT NULL,
[ProcedureName] [varchar](30) NULL,
[ParameterName] [varchar](30) NULL,
[IsEdit] [int] NULL,
[IsView] [int] NULL,
[IsRequired] [int] NULL,
[Valid] [int] NULL,
[CreatedBy] [int] NULL,
[CreatedOn] [date] NULL,
[ModifiedBy] [int] NULL,
[ModifiedOn] [date] NULL,
CONSTRAINT [pk_EAQ_cac_RoleId_Page_ColumnName1] PRIMARY KEY CLUSTERED
(
[RoleId] ASC,
[Page] ASC,
[ColumnName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (88, N'ESTIMATESUMMARY_ASPX', N'WTW_MARGIN_ABSOLUTE', N'USP_SHOW_SUMMARY', NULL, 0, 0, 0, 1, 31955, CAST(0x03300B00 AS Date), 31955, CAST(0x03300B00 AS Date))
INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'CONTRIBUTION', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date))
INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'CONTRIBUTION_PERCENT', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date))
INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'COST_AFTER_SUMMARY_DISC', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date))
INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'COST_BEFORE_SUMMARY_DISC', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date))
INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'COST_DISCOUNT', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date))
INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'EXCHANGE_RATE', N'USP_SHOW_SUMMARY', NULL, 1, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date))
INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'GROSS_MARGIN', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date))
INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'GROSS_MARGIN_PERCENT', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date))
INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'PRICE_AFTER_SUMMARY_DISC', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date))
INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'PRICE_BEFORE_SUMMARY_DISC', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date))
INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'PRICE_DISCOUNT', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date))
INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'TOTAL_COST_GRID', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date))
INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'TOTAL_COST_SUMMARY_DISC', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date))
INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'TOTAL_PRICE_GRID', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date))
INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'TOTAL_PRICE_SUMMARY_DISC', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date))
INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'WTW_MARGIN', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date))
INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'WTW_MARGIN_ABSOLUTE', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 31955, CAST(0x03300B00 AS Date), 31955, CAST(0x03300B00 AS Date))

actual script--------------------------

select page.Page as 'name',
page.ProcedureName as 'datasource',
(
select control.ColumnName "control/@name",
control.ParameterName "control/@parametername",
control.IsEdit "control/@isedit",
control.IsView "control/@isview",
control.IsRequired "control/@isrequired"
from tblcac control
join tblrum rum on control.RoleId = rum.RoleId
right outer join tblema vem on vem.PersonId = rum.PersonId
where vem.PersonId = 32177
and control.Valid = 1 or control.Valid is null
and control.Page = page.Page
and control.ProcedureName = page.ProcedureName
order by control.ColumnName
for xml path(''), type
) as controls

from tblcac page
where exists
(
select control.ColumnName "control/@name",
control.ParameterName "control/@parameter_name",
control.IsEdit "control/@isedit",
control.IsView "control/@isview",
control.IsRequired "control/@isrequired"
from tblcac control
join tblrum rum on control.RoleId = rum.RoleId
right outer join tblema vem on vem.PersonId = rum.PersonId
where vem.PersonId = 32177
and control.Valid = 1 or control.Valid is null
and control.Page = page.Page
and control.ProcedureName = page.ProcedureName
)
for xml auto, root('ControlAccess')

required output

- <ControlAccess>
- <Page name="ESTIMATESUMMARY_ASPX" dataSource="USP_SHOW_SUMMARY">
- <controls>
<control name="CONTRIBUTION" is_edit="0" is_view="1" is_required="0" />
<control name="CONTRIBUTION_PERCENT" is_edit="0" is_view="1" is_required="0" />
<control name="COST_AFTER_SUMMARY_DISC" is_edit="0" is_view="1" is_required="0" />
<control name="COST_BEFORE_SUMMARY_DISC" is_edit="0" is_view="1" is_required="0" />
<control name="COST_DISCOUNT" is_edit="0" is_view="1" is_required="0" />
<control name="EXCHANGE_RATE" is_edit="1" is_view="1" is_required="0" />
<control name="GROSS_MARGIN" is_edit="0" is_view="1" is_required="0" />
<control name="GROSS_MARGIN_PERCENT" is_edit="0" is_view="1" is_required="0" />
<control name="PRICE_AFTER_SUMMARY_DISC" is_edit="0" is_view="1" is_required="0" />
<control name="PRICE_BEFORE_SUMMARY_DISC" is_edit="0" is_view="1" is_required="0" />
<control name="PRICE_DISCOUNT" is_edit="0" is_view="1" is_required="0" />
<control name="TOTAL_COST_GRID" is_edit="0" is_view="1" is_required="0" />
<control name="TOTAL_COST_SUMMARY_DISC" is_edit="0" is_view="1" is_required="0" />
<control name="TOTAL_PRICE_GRID" is_edit="0" is_view="1" is_required="0" />
<control name="TOTAL_PRICE_SUMMARY_DISC" is_edit="0" is_view="1" is_required="0" />
<control name="WTW_MARGIN" is_edit="0" is_view="1" is_required="0" />
<control name="WTW_MARGIN_ABSOLUTE" is_edit="0" is_view="1" is_required="0" />
</controls>
</Page>
</ControlAccess>

problem--------------------
getting above output repeatedly ....

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-20 : 11:02:12

I can't run your script since I need the definition for the vies [viwSSAppsEmpMasterExtended]

TajKazi
Posting Yak Master

101 Posts

Posted - 2015-01-21 : 04:55:56

sir now i edited all code now test ...

quote:
Originally posted by gbritton

I can't run your script since I need the definition for the vies [viwSSAppsEmpMasterExtended]

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-21 : 08:53:56

Thanks for the update. The first thing I did is strip your query down to the bare bones to see how many rows are retrieved:


SELECT page.Page AS 'name'
     , page.ProcedureName AS 'datasource'
	 , (
		SELECT COUNT(*)
		FROM tblcac control
           JOIN tblrum rum
               ON control.RoleId = rum.RoleId
           RIGHT OUTER JOIN tblema vem
               ON vem.PersonId = rum.PersonId
      WHERE vem.PersonId = 32177 
        AND control.Valid = 1 
         OR control.Valid IS NULL 
        AND control.Page = page.Page 
        AND control.ProcedureName = page.ProcedureName
	  )
FROM tblcac page

This query returned 18 rows of:


name	                datasource	        (No column name)
ESTIMATESUMMARY_ASPX	USP_SHOW_SUMMARY	17

So the main query produces 18 rows, which matches your data. Why do you think you should get only 1 for Page name="ESTIMATESUMMARY_ASPX" dataSource="USP_SHOW_SUMMARY" when there are 17 more rows?

TajKazi
Posting Yak Master

101 Posts

Posted - 2015-01-21 : 23:37:50

actually there is repetition of output, that i do not need

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-22 : 16:15:31

Well, there is repetition of input! If you don't want it, use SELECT TOP(1) ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources