Click here to Skip to main content
14,691,603 members
Home » Discussions » Database
   

Database

 
AnswerRe: Error reading data from SQL Server ? Pin
ZurdoDev4-Dec-18 3:22
mveZurdoDev4-Dec-18 3:22 
AnswerRe: Error reading data from SQL Server ? Pin
Eddy Vluggen4-Dec-18 3:55
professionalEddy Vluggen4-Dec-18 3:55 
GeneralRe: Error reading data from SQL Server ? Pin
Member 24584674-Dec-18 18:15
MemberMember 24584674-Dec-18 18:15 
GeneralRe: Error reading data from SQL Server ? Pin
Eddy Vluggen5-Dec-18 2:44
professionalEddy Vluggen5-Dec-18 2:44 
GeneralRe: Error reading data from SQL Server ? Pin
Member 24584679-Dec-18 22:28
MemberMember 24584679-Dec-18 22:28 
GeneralRe: Error reading data from SQL Server ? Pin
Eddy Vluggen10-Dec-18 0:27
professionalEddy Vluggen10-Dec-18 0:27 
SuggestionRe: Error reading data from SQL Server ? Pin
CHill6011-Dec-18 6:18
mveCHill6011-Dec-18 6:18 
QuestionAn error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown Pin
simpledeveloper30-Nov-18 14:06
Membersimpledeveloper30-Nov-18 14: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 23:29
mveRichard MacCutchan30-Nov-18 23:29 
AnswerRe: An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown Pin
ZurdoDev4-Dec-18 3:23
mveZurdoDev4-Dec-18 3:23 
QuestionI am not able to Generate Scripts for a larger table Pin
simpledeveloper30-Nov-18 14:02
Membersimpledeveloper30-Nov-18 14:02 
AnswerRe: I am not able to Generate Scripts for a larger table Pin
ZurdoDev4-Dec-18 3:24
mveZurdoDev4-Dec-18 3:24 
QuestionHow do I remove the error message when I add file attachments database ? Pin
Member 245846729-Nov-18 22:17
MemberMember 245846729-Nov-18 22:17 
AnswerRe: How do I remove the error message when I add file attachments database ? Pin
Richard MacCutchan29-Nov-18 23:56
mveRichard MacCutchan29-Nov-18 23:56 
AnswerRe: How do I remove the error message when I add file attachments database ? Pin
ZurdoDev4-Dec-18 3:25
mveZurdoDev4-Dec-18 3:25 
QuestionERD Help Pin
Prytol29-Nov-18 5:28
MemberPrytol29-Nov-18 5:28 
SuggestionRe: ERD Help Pin
CHill6029-Nov-18 7:10
mveCHill6029-Nov-18 7:10 
GeneralRe: ERD Help Pin
Prytol29-Nov-18 10:10
MemberPrytol29-Nov-18 10:10 
AnswerRe: ERD Help Pin
Eddy Vluggen29-Nov-18 10:03
professionalEddy Vluggen29-Nov-18 10:03 
QuestionWhat is the right database technology for this simple outlined BI tool use case? Pin
Member 1407009628-Nov-18 2:55
MemberMember 1407009628-Nov-18 2:55 
AnswerRe: What is the right database technology for this simple outlined BI tool use case? Pin
Eddy Vluggen28-Nov-18 3:22
professionalEddy Vluggen28-Nov-18 3:22 
GeneralRe: What is the right database technology for this simple outlined BI tool use case? Pin
Mycroft Holmes28-Nov-18 14:11
professionalMycroft Holmes28-Nov-18 14:11 
GeneralRe: What is the right database technology for this simple outlined BI tool use case? Pin
Eddy Vluggen29-Nov-18 0:30
professionalEddy Vluggen29-Nov-18 0:30 
AnswerRe: What is the right database technology for this simple outlined BI tool use case? Pin
Mycroft Holmes28-Nov-18 14:13
professionalMycroft Holmes28-Nov-18 14:13 
QuestionSQL SERVER 2012 - pull data from only, if value date is available Pin
joflo27-Nov-18 0:30
Memberjoflo27-Nov-18 0: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.