Click here to Skip to main content
14,333,933 members
   

Database

 
AnswerRe: Error reading data from SQL Server ? Pin
ZurdoDev4-Dec-18 2:22
professionalZurdoDev4-Dec-18 2:22 
AnswerRe: Error reading data from SQL Server ? Pin
Eddy Vluggen4-Dec-18 2:55
mveEddy Vluggen4-Dec-18 2:55 
GeneralRe: Error reading data from SQL Server ? Pin
Member 24584674-Dec-18 17:15
memberMember 24584674-Dec-18 17:15 
GeneralRe: Error reading data from SQL Server ? Pin
Eddy Vluggen5-Dec-18 1:44
mveEddy Vluggen5-Dec-18 1:44 
GeneralRe: Error reading data from SQL Server ? Pin
Member 24584679-Dec-18 21:28
memberMember 24584679-Dec-18 21:28 
GeneralRe: Error reading data from SQL Server ? Pin
Eddy Vluggen9-Dec-18 23:27
mveEddy Vluggen9-Dec-18 23:27 
SuggestionRe: Error reading data from SQL Server ? Pin
CHill6011-Dec-18 5:18
protectorCHill6011-Dec-18 5:18 
QuestionAn error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown Pin
simpledeveloper30-Nov-18 13:06
membersimpledeveloper30-Nov-18 13:06 
Hi,

I am getting the following error when I am trying to run a SQL Query with larger Data, any help how can I run the select statement.
SELECT     
	CASE WHEN PTT.POSTransactionTypeCode = 'A' THEN 'A' 
		WHEN PTT.POSTransactionTypeCode = 'C' THEN 'C'
		WHEN PTT.POSTransactionTypeCode = 'R' THEN 'R'
		WHEN PTT.POSTransactionTypeCode = 'I' THEN 'C' 
	END AS TransactionType, 
	CASE WHEN PTT.POSTransactionTypeCode = 'A' THEN 'A'
		WHEN PTT.POSTransactionTypeCode = 'C' THEN 'A'
		WHEN PTT.POSTransactionTypeCode = 'R' THEN 'A'
		WHEN PTT.POSTransactionTypeCode = 'I' THEN 'I' 
	END AS ProviderStatus, 
-- right format. uncomment when the PIN numbers are fixed.
'00000' + C.ProviderNbr + SPACE(1) AS ContractNbr,
-- right format. uncomment when sending the provider numbers that were added wrongly.
--'000000' + C.ContractNbr AS ContractNbr,
	'01' AS Other, 
	'000000' AS PIN, 
	UPPER(LEFT(C.ProviderName, 28)) AS ContractName,
	'PROGRAM DIRECTOR' + SPACE(8) AS ContractAddressAttention,
	UPPER(LEFT(A.StreetAddress1, 24)) AS Address1, 
	UPPER(LEFT(A.StreetAddress2, 24)) AS Address2,
	UPPER(LEFT(A.City, 17)) AS City,
	UPPER(S.PK_Geographic_Location_Code) AS State,
	LEFT(A.Zip1, 9) AS ZipCd,
	SPACE(12) AS ErrorCode2,
	SPACE(8) AS Filler
FROM         
	POSPINTransaction AS E INNER JOIN
	Provider AS C ON E.FKProviderId = C.PKProviderId INNER JOIN
	ProviderDate AS PD ON dbo.Udf_GetPKProviderDate(C.PKProviderId) = PD.PKContractDate INNER JOIN
	ProviderAddress AS PA ON PA.FKProviderId=C.PKProviderId INNER JOIN
	[Address] AS A ON A.PKAddressId = PA.FKAddressId INNER JOIN
	--ParentTypeLKP AS PT ON A.FKParentType = PT.PKParentType AND PT.ParentTypeCd = 'usrContract' INNER JOIN
	[State] AS S ON A.FK_State = S.PKStateId INNER JOIN
	POSTransactionTypeLKP PTT ON E.FKPOSTransactionTypeId=PTT.PKPOSTransactionTypeId
WHERE     
	(PA.IsCurrentAddress = 1) AND (E.TransactionSentDate IS NULL) AND (E.TransactionReceivedDate IS NULL) AND (E.FKPOSTransactionTypeId IN (SELECT PKPOSTransactionTypeId FROM POSTransactionTypeLKP WHERE POSTransactionTypeCode IN ('A', 'C', 'R'))) OR
	(PA.IsCurrentAddress = 1) AND (E.TransactionSentDate IS NULL) AND (E.TransactionReceivedDate IS NULL) AND (E.FKPOSTransactionTypeId = (SELECT Top 1 PKPOSTransactionTypeId FROM POSTransactionTypeLKP WHERE POSTransactionTypeCode = 'I')) AND (PD.EffectiveDateTo IS NULL) OR
	(PA.IsCurrentAddress = 1) AND (E.TransactionSentDate IS NULL) AND (E.TransactionReceivedDate IS NULL) AND (PD.EffectiveDateTo <= GETDATE())
UNION
SELECT     
	CASE WHEN PTT.POSTransactionTypeCode = 'A' THEN 'A' 
		WHEN PTT.POSTransactionTypeCode = 'C' THEN 'C' 
		WHEN PTT.POSTransactionTypeCode = 'R' THEN 'R' 
		WHEN PTT.POSTransactionTypeCode= 'I' THEN 'C' 
	END AS TransactionType, 
	CASE WHEN PTT.POSTransactionTypeCode = 'A' THEN 'A' 
		WHEN PTT.POSTransactionTypeCode = 'C' THEN 'A' 
		WHEN PTT.POSTransactionTypeCode = 'R' THEN 'A' 
		WHEN PTT.POSTransactionTypeCode = 'I' THEN 'I' 
	END AS ProviderStatus, SP.NPINumber AS ContractNbr,
	'01' AS Other,
	'000000' AS PIN,
	UPPER(LEFT(SP.FirstName, 28)) AS ContractName,
	'PROGRAM DIRECTOR' + SPACE(8) AS ContractAddressAttention,
	UPPER(LEFT(ADDR.StreetAddress1, 24)) AS Address1, 
	UPPER(LEFT(ADDR.StreetAddress2, 24)) AS Address2,
	UPPER(LEFT(ADDR.City, 17)) AS City,
	UPPER(ST.PK_Geographic_Location_Code) AS State,
	LEFT(ADDR.Zip1, 9) AS ZipCd,
	SPACE(12) AS ErrorCode2,
	SPACE(8) AS Filler
FROM         
	POSPINTransaction AS EDS INNER JOIN
	NPIAssociation NA ON NA.FKParentId=EDS.FKProviderId AND NA.FKParentTypeLKPId = (SELECT Min(PKParentTypeLKPId) FROM ParentTypeLKP WHERE ParentCode='PRV') INNER JOIN 
	NPI AS SP ON SP.PKNPIId = NA.FKNPIId LEFT OUTER JOIN
	POSTransactionError AS EDSERR ON EDSERR.FKPOSPINTransactionId = EDS.PKPOSPINTransactionId LEFT OUTER JOIN
	POSTransactionErrorLKP AS EDSERRCD ON EDSERRCD.PKPOSTransactionErrorLKPId = EDSERR.FKPOSTransactionErrorLKPId LEFT OUTER JOIN
	NPIAddress AS NPIADDR ON NPIADDR.FKNPIId = SP.PKNPIId AND NPIADDR.IsCurrentAddress = 1 INNER JOIN
	[Address] AS ADDR ON ADDR.PKAddressId=NPIADDR.FKAddressId INNER JOIN
	[State] AS ST ON ADDR.FK_State = ST.PKStateId INNER JOIN
	County AS CO ON CO.PKCountyId = ADDR.FK_County INNER JOIN
	POSTransactionTypeLKP PTT ON EDS.FKPOSTransactionTypeId=PTT.PKPOSTransactionTypeId
WHERE     
	(EDS.TransactionSentDate IS NULL) AND (EDS.TransactionReceivedDate IS NULL) AND 
	(EDS.FKPOSTransactionTypeId IN (SELECT PKPOSTransactionTypeId FROM POSTransactionTypeLKP WHERE POSTransactionTypeCode IN ('A', 'C', 'R')) 
	OR	EDS.FKPOSTransactionTypeId = (SELECT Top 1 PKPOSTransactionTypeId FROM POSTransactionTypeLKP WHERE POSTransactionTypeCode = 'I'))

Let me know if I can refine the script or any other advice is also welcome please thanks in advance friends.
AnswerRe: An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown Pin
Richard MacCutchan30-Nov-18 22:29
protectorRichard MacCutchan30-Nov-18 22:29 
AnswerRe: An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown Pin
ZurdoDev4-Dec-18 2:23
professionalZurdoDev4-Dec-18 2:23 
QuestionI am not able to Generate Scripts for a larger table Pin
simpledeveloper30-Nov-18 13:02
membersimpledeveloper30-Nov-18 13:02 
AnswerRe: I am not able to Generate Scripts for a larger table Pin
ZurdoDev4-Dec-18 2:24
professionalZurdoDev4-Dec-18 2:24 
QuestionHow do I remove the error message when I add file attachments database ? Pin
Member 245846729-Nov-18 21:17
memberMember 245846729-Nov-18 21:17 
AnswerRe: How do I remove the error message when I add file attachments database ? Pin
Richard MacCutchan29-Nov-18 22:56
protectorRichard MacCutchan29-Nov-18 22:56 
AnswerRe: How do I remove the error message when I add file attachments database ? Pin
ZurdoDev4-Dec-18 2:25
professionalZurdoDev4-Dec-18 2:25 
QuestionERD Help Pin
Prytol29-Nov-18 4:28
memberPrytol29-Nov-18 4:28 
SuggestionRe: ERD Help Pin
CHill6029-Nov-18 6:10
protectorCHill6029-Nov-18 6:10 
GeneralRe: ERD Help Pin
Prytol29-Nov-18 9:10
memberPrytol29-Nov-18 9:10 
AnswerRe: ERD Help Pin
Eddy Vluggen29-Nov-18 9:03
mveEddy Vluggen29-Nov-18 9:03 
QuestionWhat is the right database technology for this simple outlined BI tool use case? Pin
Member 1407009628-Nov-18 1:55
memberMember 1407009628-Nov-18 1:55 
AnswerRe: What is the right database technology for this simple outlined BI tool use case? Pin
Eddy Vluggen28-Nov-18 2:22
mveEddy Vluggen28-Nov-18 2:22 
GeneralRe: What is the right database technology for this simple outlined BI tool use case? Pin
Mycroft Holmes28-Nov-18 13:11
memberMycroft Holmes28-Nov-18 13:11 
GeneralRe: What is the right database technology for this simple outlined BI tool use case? Pin
Eddy Vluggen28-Nov-18 23:30
mveEddy Vluggen28-Nov-18 23:30 
AnswerRe: What is the right database technology for this simple outlined BI tool use case? Pin
Mycroft Holmes28-Nov-18 13:13
memberMycroft Holmes28-Nov-18 13:13 
QuestionSQL SERVER 2012 - pull data from only, if value date is available Pin
joflo26-Nov-18 23:30
memberjoflo26-Nov-18 23:30 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.