|
Hi,
I have a Contolflow Task in my SSIS Package, which sends Emails, and because we don't need it and msdb.dbo.sp_send_dbmail is disabled on our SQL Server, I have first disabled the Controlflow task that sends Email using msdb.dbo.sp_send_dbmail (basically Execute SQL Task).
It was working fine on my local machine, when I deployed the Package on to a Server as SQL Agent Job, Job didn't execute threw error. Then I disabled the Email task on Package deployed it again, but surprisingly, Job is giving the same error as " msdb.dbo.sp_send_dbmail is disabled"
Then I completely deleted the Task and deployed again, still it is giving me the same error, to check when I opened the Package in notepad the task still showing.
Is there any way we can completely get rid of the task from SSIS Package, any help would be very helpful - thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Hi,
I am executing the following query, its taking too long to execute, can anybody give me any advice what can I do to improve its performance, because its taking hours of time to execute. Thanks in advance.
SELECT
vw_OwnerXML5010.Owner,
vw_OwnerXML5010.Billing_EIN AS TP_EIN,
vw_OwnerXML5010.TP_Name AS TP_NAME,
vw_OwnerXML5010.County_Code AS County_Code,
(
SELECT
(
SELECT
vw_ServiceFacilityXML5010.Service_Facility_NPI AS "Service_Facility_NPI",
vw_ServiceFacilityXML5010.County_Owned_Indicator AS "County_Owned_Indicator",
(
Select
vw_ServicesXML5010.Procedure_Code AS "Procedure_Code",
vw_ServicesXML5010.Modifier1 As "Modifier1",
vw_ServicesXML5010.Modifier2 AS "Modifier2",
vw_ServicesXML5010.Modifier3 AS "Modifier3",
vw_ServicesXML5010.Modifier4 AS "Modifier4",
vw_ServicesXML5010.From_Date AS "From_Date",
vw_ServicesXML5010.To_Date AS "To_Date",
vw_ServicesXML5010.Revenue_Code AS "Revenue_Code",
vw_ServicesXML5010.Place_Of_Service AS "Place_Of_Service",
vw_ServicesXML5010.Taxonomy_Code AS "Taxonomy_Code"
FROM
vw_ServicesXML5010
WHERE
vw_ServiceFacilityXML5010.Service_Facility_NPI = vw_ServicesXML5010.Service_Facility_NPI and
vw_ServiceFacilityXML5010.County_Code = vw_ServicesXML5010.County_Code
and vw_ServiceFacilityXML5010.Billing_EIN = vw_ServicesXML5010.Billing_EIN
FOR XML PATH('Service'), TYPE , ELEMENTS XSINIL
)
FROM
vw_ServiceFacilityXML5010
WHERE
vw_ServiceFacilityXML5010.County_Code = vw_OwnerXML5010.County_Code
and vw_ServiceFacilityXML5010.Billing_EIN = vw_OwnerXML5010.Billing_EIN
FOR XML PATH ('TB_SERVICE_FACILITY'), TYPE
) FOR XML PATH ('TB_BILLING_PROVIDER_FILE'), TYPE
)
FROM
dbo.vw_SDMCPhaseTwoParentLevelXML vw_OwnerXML5010
ORDER BY vw_OwnerXML5010.County_Code
FOR XML PATH ('TB_TRADING_PARTNER'), root('TB_BILLING_PROVIDER_FILES')
Here are the following Views:
CREATE VIEW [dbo].[vw_SDMCPhaseTwoParentLevelXML]
AS
SELECT
DISTINCT 'DMH' AS [Owner]
,'' AS [Billing_NPI]
,SUBSTRING(RTRIM(LE.TaxId), 1, 9) AS [Billing_EIN]
,'' AS DirectIndicator
,CO.PK_Geographic_Location_Code AS [County_Code]
,CO.County_Name AS [TP_Name]
FROM
dbo.LegalEntity LE<br />
INNER JOIN dbo.county CO
ON LE.FKCountyLKPId = CO.PKCountyId
Where LEFT(LegalEntityNbr,3) NOT IN ('HFP', '00F')
AND LEFT(LegalEntityNbr,3) IN ('000')
AND CO.PK_Geographic_Location_Code NOT IN ('99', '00', '65', '66')
GO
CREATE VIEW [dbo].[vw_ServiceFacilityXML5010]
AS
SELECT
distinct VW.Billing_EIN AS [Billing_EIN]
,CO.PK_Geographic_Location_Code AS [County_Code]<br />
,US.NPINumber AS [Service_Facility_NPI]
, 'true' AS [County_Owned_Indicator]
FROM
dbo.Provider C
INNER JOIN dbo.ProviderDate CD
ON CD.FKProviderId = C.PKProviderId
INNER JOIN dbo.ProviderService CS
ON CS.FKProviderDateId = CD.PKProviderDateId and cs.FKProviderId=cd.FKProviderId
INNER JOIN dbo.ProviderService CSD
ON CSD.FKProviderId = CS.FKProviderId
LEFT OUTER JOIN dbo.ProviderService MCS
ON MCS.FKProviderId = CS.FKProviderId
LEFT OUTER JOIN dbo.ProviderService MCSD
ON MCSD.FKProviderId = MCS.FKProviderId
INNER JOIN dbo.ServiceFunctionCategoryLKP SFC
ON SFC.PKServiceFunctionCategoryLKPId = CS.FKServFuncCatMCModeOfServiceId
INNER JOIN dbo.ModeOfServiceLKP MS
ON MS.Code = SFC.ModeOfServiceCode
LEFT OUTER JOIN dbo.ServFuncCatMCModeOfService SFCMMS
ON MS.Code= SFCMMS.MCModeOfServiceCode
LEFT OUTER JOIN dbo.MCModeOfServiceLKP MMS
ON MMS.PKMCModeOfServiceLKPId = SFCMMS.MCModeOfServiceCode
INNER JOIN dbo.NPIAssociation USP
ON USP.FKParentId = C.PKProviderId AND USP.FKParentTypeLKPId=(SELECT Top 1 PKParentTypeLKPId from ParentTypeLKP WHERE ParentCode='PRV')
INNER JOIN dbo.NPI US
ON US.PKNPIId = USP.FKNPIId
INNER JOIN dbo.LegalEntity LE
ON LE.PKLegalEntityId = C.FKLegalEntityId
--INNER JOIN dbo.usrTaxid TX
-- ON LE.FKTaxId = TX.PKTaxId
INNER JOIN dbo.County CO
ON C.FKCountyLKPId = CO.PKCountyId
INNER JOIN VW_SDMCPhaseTwoParentLevelXML VW
ON CO.PK_Geographic_Location_Code = VW.county_code
INNER JOIN dbo.ProviderType FT
ON C.ProviderTypeId = FT.ProviderTypeId
INNER JOIN dbo.MCMSSFCrosswalkLKP SP
ON MMS.Code = SP.MCModeOfServiceCode
Where LEFT(LegalEntityNbr,3) IN ('000', 'AFC')
AND LEFT(LegalEntityNbr,3) NOT IN ('HFP', '00F')<br />
AND MMS.Code IS NOT NULL
AND CO.PK_Geographic_Location_Code NOT IN ('99', '00', '65', '66')
AND SP.ServiceFunctionCategoryCode = SFC.ServiceFunctionCategoryCode
UNION ALL<br />
SELECT
distinct VW.Billing_EIN AS [Billing_EIN]
,CO.PK_Geographic_Location_Code AS [County_Code]<br />
,US.NPINumber AS [Service_Facility_NPI]
FROM
dbo.Provider C
INNER JOIN dbo.ProviderDate CD
ON CD.FKProviderId = C.PKProviderId
INNER JOIN dbo.ProviderService CS
ON CS.FKProviderDateId = CD.PKProviderDateId and cs.FKProviderId=cd.FKProviderId
INNER JOIN dbo.ProviderService CSD
ON CSD.FKProviderId = CS.FKProviderId
LEFT OUTER JOIN dbo.ProviderService MCS
ON MCS.FKProviderId = CS.FKProviderId
LEFT OUTER JOIN dbo.ProviderService MCSD
ON MCSD.FKProviderId = MCS.FKProviderId
INNER JOIN dbo.ServiceFunctionCategoryLKP SFC
ON SFC.PKServiceFunctionCategoryLKPId = CS.FKServFuncCatMCModeOfServiceId
INNER JOIN dbo.ModeOfServiceLKP MS
ON MS.Code = SFC.ModeOfServiceCode
LEFT OUTER JOIN dbo.ServFuncCatMCModeOfService SFCMMS
ON MS.Code= SFCMMS.MCModeOfServiceCode
LEFT OUTER JOIN dbo.MCModeOfServiceLKP MMS
ON MMS.PKMCModeOfServiceLKPId = SFCMMS.MCModeOfServiceCode
INNER JOIN dbo.NPIAssociation USP
ON USP.FKParentId = C.PKProviderId AND USP.FKParentTypeLKPId=(SELECT Top 1 PKParentTypeLKPId from ParentTypeLKP WHERE ParentCode='PRV')
INNER JOIN dbo.NPI US
ON US.PKNPIId = USP.FKNPIId
INNER JOIN dbo.LegalEntity LE
ON LE.PKLegalEntityId = C.FKLegalEntityId
--INNER JOIN dbo.usrTaxid TX
-- ON LE.FKTaxId = TX.PKTaxId
INNER JOIN dbo.County CO
ON C.FKCountyLKPId = CO.PKCountyId
INNER JOIN VW_SDMCPhaseTwoParentLevelXML VW
ON CO.PK_Geographic_Location_Code = VW.county_code
INNER JOIN dbo.ProviderType FT
ON C.ProviderTypeId = FT.ProviderTypeId
INNER JOIN dbo.MCMSSFCrosswalkLKP SP
ON MMS.Code = SP.MCModeOfServiceCode
Where LEFT(LegalEntityNbr,3) NOT IN ('000', 'AFC','HFP', '00F')<br />
AND MMS.Code IS NOT NULL
AND CO.PK_Geographic_Location_Code NOT IN ('99', '00', '65', '66')
AND SP.ServiceFunctionCategoryCode = SFC.ServiceFunctionCategoryCode
GO
CREATE VIEW [dbo].[vw_ServicesXML5010]
AS
SELECT
VW.Billing_EIN AS [Billing_EIN]
,CO.PK_Geographic_Location_Code AS [County_Code]
,US.NPINumber AS [Service_Facility_NPI]
,ISNULL(SP.RevenueCode, '') AS [Revenue_Code]
,SP.ProcedureCode AS [Procedure_Code]
,SP.Modifier1 as Modifier1
,ISNULL(SP.Modifier2, '') AS [Modifier2]
,ISNULL(SP.Modifier3, '') AS [Modifier3]
,ISNULL(SP.Modifier4, '') AS [Modifier4]
,CASE
WHEN SP.MCModeOfServiceCode = '12' AND SP.ServiceFunctionCategoryCode = '20' THEN '23'
WHEN SP.MCModeOfServiceCode = '12' AND SP.ServiceFunctionCategoryCode = '25' THEN '20'
WHEN SP.MCModeOfServiceCode = '18' AND SP.ServiceFunctionCategoryCode = '20' THEN '23'
WHEN SP.MCModeOfServiceCode = '18' AND SP.ServiceFunctionCategoryCode = '25' THEN '20'
ELSE ''
END Place_Of_Service
, '' AS Taxonomy_Code
,ISNULL(CONVERT(VARCHAR(8), CS.MCBeginDate, 112), '') AS [From_Date]
,CONVERT(VARCHAR(8), CS.MCEndDate, 112) AS [To_Date]
FROM
dbo.Provider C
INNER JOIN dbo.ProviderDate CD
ON CD.FKProviderId = C.PKProviderId
INNER JOIN dbo.ProviderService CS
ON CS.FKProviderDateId = CD.PKProviderDateId
INNER JOIN dbo.ServFuncCatMCModeOfService SFC
ON SFC.PKServFuncCatMCModeOfServiceId = CS.FKServFuncCatMCModeOfServiceId
INNER JOIN dbo.ModeOfServiceLKP MS
ON MS.Code = SFC.ModeOfServiceCode
LEFT OUTER JOIN dbo.MCModeOfServiceLKP MMS
ON MMS.Code = SFC.MCModeOfServiceCode
INNER JOIN dbo.NPIAssociation USP
ON USP.FKParentId = C.PKProviderId AND USP.FKParentTypeLKPId IN
(
SELECT PKParentTypeLKPId FROM dbo.ParentTypeLKP a WHERE ParentCode IN ('PRV') --, 'NPI'
)
INNER JOIN dbo.NPI US
ON US.PKNPIId = USP.FKNPIId
INNER JOIN dbo.LegalEntity LE
ON LE.PKLegalEntityId = C.FKLegalEntityId
--INNER JOIN dbo.usrTaxid TX
-- ON LE.FKTaxId = TX.PKTaxId
INNER JOIN dbo.County CO
ON C.FKCountyLKPId = CO.PKCountyId
INNER JOIN VW_SDMCPhaseTwoParentLevelXML VW
ON CO.PK_Geographic_Location_Code = VW.county_code
INNER JOIN dbo.FacilityDesignationLKP FT
ON US.FKFacilityDesignationLKPId = FT.PKFacilityDesignationLKPId
INNER JOIN dbo.MCMSSFCrosswalkLKP SP
ON MMS.Code = SP.MCModeOfServiceCode
Where LEFT(LegalEntityNbr,3) NOT IN ('HFP', '00F')
AND MMS.Code IS NOT NULL
AND CO.PK_Geographic_Location_Code NOT IN ('99', '00', '65', '66')
AND SP.FKServFuncCatMCModeOfServiceId = SFC.PKServFuncCatMCModeOfServiceId
AND SP.Modifier1 <> 'HK'
UNION ALL
SELECT
VW.Billing_EIN AS [Billing_EIN]
,CO.PK_Geographic_Location_Code AS [County_Code]
,US.NPINumber AS [Service_Facility_NPI]
,ISNULL(SP.RevenueCode, '') AS [Revenue_Code]
,SP.ProcedureCode AS [Procedure_Code]
,SP.Modifier1 as Modifier1
,ISNULL(SP.Modifier2, '') AS [Modifier2]
,ISNULL(SP.Modifier3, '') AS [Modifier3]
,ISNULL(SP.Modifier4, '') AS [Modifier4]
,CASE
WHEN SP.MCModeOfServiceCode = '12' AND SP.ServiceFunctionCategoryCode = '20' THEN '23'
WHEN SP.MCModeOfServiceCode = '12' AND SP.ServiceFunctionCategoryCode = '25' THEN '20'
WHEN SP.MCModeOfServiceCode = '18' AND SP.ServiceFunctionCategoryCode = '20' THEN '23'
WHEN SP.MCModeOfServiceCode = '18' AND SP.ServiceFunctionCategoryCode = '25' THEN '20'
ELSE ''
END Place_Of_Service
, '' AS Taxonomy_Code
,CASE
WHEN (SP.ProcedureCode = 'H2015' or SP.ProcedureCode = 'T1017')AND SP.Modifier1 = 'HK' --all Katie A. Services start on 20130101
THEN '20130101'
ELSE
ISNULL(CONVERT(VARCHAR(8), CS.MCBeginDate, 112), '')
END [From_Date]
,CONVERT(VARCHAR(8), CS.MCEndDate, 112) AS [To_Date] -- end date is not changing for Katie A. services. Just eliminate the rows that have an end date
FROM
dbo.Provider C
INNER JOIN dbo.ProviderDate CD
ON CD.FKProviderId = C.PKProviderId
INNER JOIN dbo.ProviderService CS
ON CS.FKProviderDateId = CD.PKProviderDateId
INNER JOIN dbo.ServFuncCatMCModeOfService SFC
ON SFC.PKServFuncCatMCModeOfServiceId = CS.FKServFuncCatMCModeOfServiceId
INNER JOIN dbo.ModeOfServiceLKP MS
ON MS.Code = SFC.ModeOfServiceCode
LEFT OUTER JOIN dbo.MCModeOfServiceLKP MMS
ON MMS.Code = SFC.MCModeOfServiceCode
INNER JOIN dbo.NPIAssociation USP
ON USP.FKParentId = C.PKProviderId AND USP.FKParentTypeLKPId IN
(
SELECT PKParentTypeLKPId FROM dbo.ParentTypeLKP a WHERE ParentCode IN ('PRV') --, 'NPI'
)
INNER JOIN dbo.NPI US
ON US.PKNPIId = USP.FKNPIId
INNER JOIN dbo.LegalEntity LE
ON LE.PKLegalEntityId = C.FKLegalEntityId
--INNER JOIN dbo.usrTaxid TX
-- ON LE.FKTaxId = TX.PKTaxId
INNER JOIN dbo.County CO
ON C.FKCountyLKPId = CO.PKCountyId
INNER JOIN VW_SDMCPhaseTwoParentLevelXML VW
ON CO.PK_Geographic_Location_Code = VW.county_code
INNER JOIN dbo.FacilityDesignationLKP FT
ON US.FKFacilityDesignationLKPId = FT.PKFacilityDesignationLKPId
INNER JOIN dbo.MCMSSFCrosswalkLKP SP
ON MMS.Code = SP.MCModeOfServiceCode
Where LEFT(LegalEntityNbr,3) NOT IN ('HFP', '00F')
AND MMS.Code IS NOT NULL
AND CO.PK_Geographic_Location_Code NOT IN ('99', '00', '65', '66')
AND SP.ServiceFunctionCategoryCode = SFC.ServiceFunctionCategoryCode
AND (SP.Modifier1 = 'HK' and (CS.MCEndDate is null or (CS.MCBeginDate >= '20130101' or CS.MCEndDate > '20130101')))
GO
Any help would be greatly helpful, thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
CHill60 wrote: You might get a better response with some sample data and expected results
This is getting old now. When posting SQL queries and asking "what is wrong" or "how does this not work" or ... well, anything, you should include some sample data for all of the tables used in your query.
Nobody wants to use up their spare time (which is how this forum works) traipsing through 200+ lines of code in the off-chance they might spot something that could be improved.
Help us to help you.
|
|
|
|
|
Some of the tables in that Query have 15 Columns too, I don't know how can I put all that Data here on the forum itself, it will be more huge message than currently, I am assuming this current message itself is too big.
What I am asking is, are there any places that I can be suspicious of taking more time than normal. Any help would be very very helpful buddy.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
indian143 wrote: What I am asking is, are there any places that I can be suspicious of taking more time than normal.
The usual things.
Converting varchars to dates is a big timewaster, having magical numbers shows a design-flaw, and every function that is called on each value will slow the thing down fast.
8 Ways to Fine-tune your SQL Queries (for production databases) | Sisense[^]
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Hi all. I was in a meeting recently where the chair was extolling the virtues of Graph databases. So much so that he asserted, if they’d been invented, Ralph Kimball would never have become involved with dimensional modelling and would have used graph databases instead. I have been involved with dimensional modelling (and cubes) for many years, but can’t claim to have built a Graph database. However, I understand their strength is in identifying relationships. If you’re working with data that is hierarchical in nature and, the relationships are evident, then Graph is not appropriate. Would be interested in you views. Cheers
|
|
|
|
|
DADom wrote: So much so that he asserted, if they’d been invented, Ralph Kimball would never have become involved with dimensional modelling and would have used graph databases instead.
Keep any evangelists away from these discussions. You don't need a salesman on your team.
DADom wrote: Would be interested in you views.
My strength is identifying relationships too, so I prefer to store relational data in a RDBMS in a structure that is at least in BCNF. It is a proven way which guarantees consistency and correctness.
According to the wiki, a graph db excells where data is "difficult to model" (and I agree with the comment "according to whom"). So, to evaluate it, you come looking for anecdotes.
I suggest you make a list (and check it twice) with advantages and disadvantages of both, and decide which are more important to you. THAT is the correct way out of these discussions - you will need to justify your choice, which is not done by pointing to an anecdote on codeproject, but by showing which of the two choices is the better fit in your specific case.
Personally, I'd say that the choice is obvious; Most RDBMSs are stable and well-optimized for data storage and retrieval, and there are a lot of resources on SQL92 (talk about backward compatibility eh?) Graph-databases would require something else than SQL probably, adding to the costs (and riscs).
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Eddy Vluggen wrote: According to the wiki,
Where is the wiki? Regards.
|
|
|
|
|
John 13564154 wrote:
Where is the wiki? Regards.
We don't have one; I was referring to this[^] wikipedia article.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
I have a table product, columns:id,date,product,productname
I want to create a insert trigger, when I insert id,date,product, the productname will auto update in to the table(the product=productname... I don't know how to make the equation, please help me.
sorry for my terrible English.
modified 22-May-18 5:39am.
|
|
|
|
|
Why have two columns with the same data?
Have you tried anything at all yet?
|
|
|
|
|
CREATE TRIGGER (Transact-SQL) | Microsoft Docs[^]
CREATE TRIGGER YourTriggerName
ON YourTableName
AFTER insert, update
As
BEGIN
SET NOCOUNT ON;
If UPDATE(FK)
BEGIN
UPDATE
T
SET
RedundantDuplicateColumn = T2.Name
FROM
YourTable As T
INNER JOIN inserted As I
ON I.PK = T.PK
LEFT JOIN YourOtherTable As T2
ON T2.PK = T.FK
;
END;
END
Replace the dummy table and column names with the real table and column names.
Remember that triggers will fire once per batch, not once per row. There could be multiple rows in the inserted table.
But as CHill60 said, duplicating the product name in both tables seems like a bad design. If you have the ID of the product, you can simply join to the product table to get the name on demand.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
This is a REALLY BAD design, instead of having a separate table for productname create a view
create view vwProductName
as
select distinct product as productname from product
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Am I missing something ... where's the table for the ProductName mentioned?
|
|
|
|
|
OP wrote:
the productname will auto update in to the table(the product=productname.
So yes I think you missed this.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
It's an ERP bill, customer want to input the batch number, but they don't want to use any rules, just want to fetch the bill number into that field.
|
|
|
|
|
The comments still stand - there should be no reason to store the productname in a different table especially as it is user input and free form.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I have a query that is using FOR XML PATH, when the child elements has values then its giving the Child elements, but when there are no values coming out of query for the Child elements, its finishing the X-path by putting and end tag (\), but I want the all the elements to be created if there are values are not, it doesn't have to repeat the elements as if there are values but at least one occurrence has to be there even if there are values or don't have values.
Below are the example for it.
Case 1 is the file that it is creating with all the values and elements, there are elements that are repeated I am not putting all that xml here to reduce the size, but I want to create all the elements of the xml even if there are values or no values at least one occurrence.
<TB_BILLING_PROVIDER_FILES>
<TB_TRADING_PARTNER>
<Owner>DMH</Owner>
<TP_EIN>9xxxxxxx1</TP_EIN>
<TP_NAME>Alameda</TP_NAME>
<County_Code>01</County_Code>
<TB_BILLING_PROVIDER_FILE>
<TB_SERVICE_FACILITY>
<Service_Facility_NPI>xxxxxxxx7</Service_Facility_NPI>
<County_Owned_Indicator>true</County_Owned_Indicator>
<Service xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Procedure_Code>Hxxxxx</Procedure_Code>
<Modifier1>HE</Modifier1>
<Modifier2>TG</Modifier2>
<Modifier3/>
<Modifier4/>
<From_Date>19811201</From_Date>
<To_Date>20030623</To_Date>
<Revenue_Code/>
<Place_Of_Service/>
<Taxonomy_Code/>
</Service>
</TB_SERVICE_FACILITY>
</TB_BILLING_PROVIDER_FILE>
</TB_TRADING_PARTNER>
</TB_BILLING_PROVIDER_FILES>
The Case 2 xml is as below:
<TB_BILLING_PROVIDER_FILES>
<TB_TRADING_PARTNER>
<Owner>DMH</Owner>
<TP_EIN>xxxxxxxxx</TP_EIN>
<TP_NAME>Alameda</TP_NAME>
<County_Code>01</County_Code>
<TB_BILLING_PROVIDER_FILE/>
</TB_TRADING_PARTNER>
</TB_BILLING_PROVIDER_FILES>
The sql script for the case 1 is:
SELECT
vw_OwnerXML5010.Owner,
vw_OwnerXML5010.Billing_EIN AS TP_EIN,
vw_OwnerXML5010.TP_Name AS TP_NAME,
vw_OwnerXML5010.County_Code AS County_Code,
(
SELECT
(
SELECT
vw_ServiceFacilityXML5010.Service_Facility_NPI AS "Service_Facility_NPI",
vw_ServiceFacilityXML5010.County_Owned_Indicator AS "County_Owned_Indicator",
(
Select
vw_ServicesXML5010.Procedure_Code AS "Procedure_Code",
vw_ServicesXML5010.Modifier1 As "Modifier1",
vw_ServicesXML5010.Modifier2 AS "Modifier2",
vw_ServicesXML5010.Modifier3 AS "Modifier3",
vw_ServicesXML5010.Modifier4 AS "Modifier4",
vw_ServicesXML5010.From_Date AS "From_Date",
vw_ServicesXML5010.To_Date AS "To_Date",
vw_ServicesXML5010.Revenue_Code AS "Revenue_Code",
vw_ServicesXML5010.Place_Of_Service AS "Place_Of_Service",
vw_ServicesXML5010.Taxonomy_Code AS "Taxonomy_Code"
FROM
vw_ServicesXML5010
WHERE
vw_ServiceFacilityXML5010.Service_Facility_NPI = vw_ServicesXML5010.Service_Facility_NPI
and vw_ServiceFacilityXML5010.County_Code = vw_ServicesXML5010.County_Code
and vw_ServiceFacilityXML5010.Billing_EIN = vw_ServicesXML5010.Billing_EIN
-- and vw_ServiceFacilityXML5010.County_Code in ('01', '02')
-- and vw_ServiceFacilityXML5010.Service_Facility_NPI = '1932328580'
FOR XML PATH('Service'), TYPE , ELEMENTS XSINIL
)
FROM
vw_ServiceFacilityXML5010
WHERE
vw_ServiceFacilityXML5010.County_Code = vw_OwnerXML5010.County_Code
and vw_ServiceFacilityXML5010.Billing_EIN = vw_OwnerXML5010.Billing_EIN
-- and vw_ServiceFacilityXML5010.County_Code in ( '01', '02')
-- and vw_ServicesXML5010.Service_Facility_NPI = '1932328580'
FOR XML PATH ('TB_SERVICE_FACILITY'), TYPE
-- ) AS "BILLING_PROVIDERS/TB_BILLING_PROVIDER_FILE/TB_SERVICE_FACILITY"
--) AS "TB_BILLING_PROVIDER_FILE/TB_SERVICE_FACILITY"
--) AS "TB_BILLING_PROVIDER_FILE"
) FOR XML PATH ('TB_BILLING_PROVIDER_FILE'), TYPE
)
FROM
vw_OwnerXML5010
-- where vw_OwnerXML5010.County_Code in ('01', '02')
ORDER BY vw_OwnerXML5010.County_Code
FOR XML PATH ('TB_TRADING_PARTNER'), root('TB_BILLING_PROVIDER_FILES')
The sql script for the case 2 is:
SELECT
vw_OwnerXML5010.Owner,
vw_OwnerXML5010.Billing_EIN AS TP_EIN,
vw_OwnerXML5010.TP_Name AS TP_NAME,
vw_OwnerXML5010.County_Code AS County_Code,
(
SELECT
(
SELECT
vw_ServiceFacilityXML5010.Service_Facility_NPI AS "Service_Facility_NPI",
vw_ServiceFacilityXML5010.County_Owned_Indicator AS "County_Owned_Indicator",
(
Select
vw_ServicesXML5010.Procedure_Code AS "Procedure_Code",
vw_ServicesXML5010.Modifier1 As "Modifier1",
vw_ServicesXML5010.Modifier2 AS "Modifier2",
vw_ServicesXML5010.Modifier3 AS "Modifier3",
vw_ServicesXML5010.Modifier4 AS "Modifier4",
vw_ServicesXML5010.From_Date AS "From_Date",
vw_ServicesXML5010.To_Date AS "To_Date",
vw_ServicesXML5010.Revenue_Code AS "Revenue_Code",
vw_ServicesXML5010.Place_Of_Service AS "Place_Of_Service",
vw_ServicesXML5010.Taxonomy_Code AS "Taxonomy_Code"
FROM
vw_ServicesXML5010
WHERE
vw_ServiceFacilityXML5010.Service_Facility_NPI = vw_ServicesXML5010.Service_Facility_NPI
and vw_ServiceFacilityXML5010.County_Code = vw_ServicesXML5010.County_Code
and vw_ServiceFacilityXML5010.Billing_EIN = vw_ServicesXML5010.Billing_EIN
-- and vw_ServiceFacilityXML5010.County_Code in ('01', '02')
-- and vw_ServiceFacilityXML5010.Service_Facility_NPI = '1932328580'
FOR XML PATH('Service'), TYPE , ELEMENTS XSINIL
)
FROM
vw_ServiceFacilityXML5010
WHERE
vw_ServiceFacilityXML5010.County_Code = vw_OwnerXML5010.County_Code
and vw_ServiceFacilityXML5010.Billing_EIN = vw_OwnerXML5010.Billing_EIN
-- and vw_ServiceFacilityXML5010.County_Code in ( '01', '02')
-- and vw_ServicesXML5010.Service_Facility_NPI = '1932328580'
FOR XML PATH ('TB_SERVICE_FACILITY'), TYPE
-- ) AS "BILLING_PROVIDERS/TB_BILLING_PROVIDER_FILE/TB_SERVICE_FACILITY"
--) AS "TB_BILLING_PROVIDER_FILE/TB_SERVICE_FACILITY"
--) AS "TB_BILLING_PROVIDER_FILE"
) FOR XML PATH ('TB_BILLING_PROVIDER_FILE'), TYPE
)
FROM
dbo.vw_SDMCPhaseTwoParentLevelXML vw_OwnerXML5010
-- where vw_OwnerXML5010.County_Code in ('01', '02')
ORDER BY vw_OwnerXML5010.County_Code
FOR XML PATH ('TB_TRADING_PARTNER'), root('TB_BILLING_PROVIDER_FILES')
I am not understanding how are these two different, I just want to create the complete xml at least with one occurrence of all the elements, can anybody please help me, any help a suggestion a link or code snippet, anything helps. Thanks in advance buddies.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Last tuesday I made a suggestion to you that would probably help you get assistance.
CHill60 wrote: You might get a better response with some sample data and expected results
You've given us the outputs but still haven't provided any sample data ... on the tables (or views) vw_ServicesXML5010, vq_ServiceFacilityXML5010, vw_OwnerXML5010 or dbo.vw_SDMCPhaseTwoParentLevelXML
You have said that you don't understand how the two scripts are different, yet one of them is reading from vw_OwnerXML5010 but the other is reading from dbo.vw_SDMCPhaseTwoParentLevelXML having been given an alias of vw_OwnerXML5010. I suggest you start by comparing the output of the following sql commands
SELECT * FROM vb_OwnerXML5010 and
SELECT * FROM dbo.vw_SDMCPhaseTwoParentLevelXML introducing an appropriate WHERE clause to both.
We can't help you any further without sample data.
|
|
|
|
|
Hi,
What is the equivalent of the following query without the FOR XML PATH, when removed the FOR XML PATH from the query it is giving error, can anybody please help with this regards, thanks in advance buddies.
SELECT
vw_OwnerXML5010.Owner,
vw_OwnerXML5010.Billing_EIN AS TP_EIN,
vw_OwnerXML5010.TP_Name AS TP_NAME,
vw_OwnerXML5010.County_Code AS County_Code,
(
SELECT
(
SELECT
vw_ServiceFacilityXML5010.Service_Facility_NPI AS "Service_Facility_NPI",
vw_ServiceFacilityXML5010.County_Owned_Indicator AS "County_Owned_Indicator",
(
Select
vw_ServicesXML5010.Procedure_Code AS "Procedure_Code",
vw_ServicesXML5010.Modifier1 As "Modifier1",
vw_ServicesXML5010.Modifier2 AS "Modifier2",
vw_ServicesXML5010.Modifier3 AS "Modifier3",
vw_ServicesXML5010.Modifier4 AS "Modifier4",
vw_ServicesXML5010.From_Date AS "From_Date",
vw_ServicesXML5010.To_Date AS "To_Date",
vw_ServicesXML5010.Revenue_Code AS "Revenue_Code",
vw_ServicesXML5010.Place_Of_Service AS "Place_Of_Service",
vw_ServicesXML5010.Taxonomy_Code AS "Taxonomy_Code"
FROM
vw_ServicesXML5010
WHERE
vw_ServiceFacilityXML5010.Service_Facility_NPI = vw_ServicesXML5010.Service_Facility_NPI
and vw_ServiceFacilityXML5010.County_Code = vw_ServicesXML5010.County_Code
and vw_ServiceFacilityXML5010.Billing_EIN = vw_ServicesXML5010.Billing_EIN
-- and vw_ServiceFacilityXML5010.County_Code in ('01', '02')
-- and vw_ServiceFacilityXML5010.Service_Facility_NPI = '1932328580'
FOR XML PATH('Service'), TYPE , ELEMENTS XSINIL
)
FROM
vw_ServiceFacilityXML5010
WHERE
vw_ServiceFacilityXML5010.County_Code = vw_OwnerXML5010.County_Code
and vw_ServiceFacilityXML5010.Billing_EIN = vw_OwnerXML5010.Billing_EIN
-- and vw_ServiceFacilityXML5010.County_Code in ( '01', '02')
-- and vw_ServicesXML5010.Service_Facility_NPI = '1932328580'
FOR XML PATH ('TB_SERVICE_FACILITY'), TYPE
-- ) AS "BILLING_PROVIDERS/TB_BILLING_PROVIDER_FILE/TB_SERVICE_FACILITY"
--) AS "TB_BILLING_PROVIDER_FILE/TB_SERVICE_FACILITY"
--) AS "TB_BILLING_PROVIDER_FILE"
) FOR XML PATH ('TB_BILLING_PROVIDER_FILE'), TYPE
)
FROM
vw_OwnerXML5010
-- where vw_OwnerXML5010.County_Code in ('01', '02')
ORDER BY vw_OwnerXML5010.County_Code
FOR XML PATH ('TB_TRADING_PARTNER'), root('TB_BILLING_PROVIDER_FILES')
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
You might get a better response with some sample data and expected results
|
|
|
|
|
I am having a query on the Production Server, I am getting an xml out of it. For some reason there is a problem in the data that I am getting, some data is missing, but I can't easily compare and see it on the xml file what am I missing and why.
It would be easier for me if I put the same Data into Table then I can easily compare what am I missing. Or is there anyway that I can read the Data from xml file and put it into a table? I am trying but I have to create table before it seems, but I want to just import into Table like we do from excel files.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
-- modified 15-May-18 13:02pm.
|
|
|
|
|
CHill60 wrote: You might get a better response with some sample data and expected results
My comment still stands.
Indian143 wrote: when removed the FOR XML PATH from the query it is giving error
Are you are expecting us to guess what that error might be?
There are plenty of people who would like to help you solve your problem, but you're not giving us much to go on. We do this in our spare time so expecting us to wade through the SQL to try and create some tables to match it so we can see what is being produced so we can work out how to replace the FOR XML and get appropriate results ... would take more time than most of us have available.
See Some guidelines for posting questions in the forums[^]
|
|
|
|
|
I can understand by can you please help me how can I create dummy values for the views vw_ServicesXML5010, vw_ServiceFacilityXML5010, as those views are not fetching any records, the xml is not generating those elements, is it possible to help me in that regards.
For example if I don't have any records for the View vw_ServicesXML5010 for that where condition I want to have empty value for the elements Procedure_Code, Modifier1, Modifier2, Modifier3, Modifier4, From_Date, To_Date, Revenue_Code, Place_Of_Service, Taxonomy_Code
Same thing applies to the View: vw_ServiceFacilityXML5010, if it doesn't have any records then at least one record set with empty values for the Service_Facility_NPI, County_Owned_Indicator.
Can you please help me buddy, thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
I just got a requirement for a web API to an Oracle database. So I fire up an Oracle DAL from 2010 only to find the reference to System.Data.OracleClient are out of date and it is not recommended. Searching for Oracle Connection does not uncover the references required, connection strings.com was no real help.
Can someone advise me on the current references for VS 2015 to connect to Oracle please. I can't add object to the server as one article suggested just a plain read only connection is required.
[edit0]
Looks like this is the latest tool from Oracle Oracle Data Provider for .NET (ODP.NET)[^]. What a PITA,
First I need an account to download, create an account, nope email already has an account, do the forgot password thing. Ok have account not to refind the link to download.
It has to be run as admin if you want to install for all users, you are not given a choice to install for just yourself. Have to close all VS Instances. I'm betting the 2017 version does not work with 2015 and it seems like you can only have 1 version installed. 
As I sit here waiting for help desk to allow me to install a .net driver I really do not like Oracle.
[/edit0]
[edit1] They have a nuget package - I hate nuget [/edit1]
Never underestimate the power of human stupidity
RAH
modified 7-May-18 3:07am.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
