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.
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
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
(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())
UNIONSELECTCASEWHENPTT.POSTransactionTypeCode= 'A'THEN'A'WHENPTT.POSTransactionTypeCode= 'C'THEN'C'WHENPTT.POSTransactionTypeCode= 'R'THEN'R'WHENPTT.POSTransactionTypeCode= 'I'THEN'C'ENDASTransactionType,CASEWHENPTT.POSTransactionTypeCode= 'A'THEN'A'WHENPTT.POSTransactionTypeCode= 'C'THEN'A'WHENPTT.POSTransactionTypeCode= 'R'THEN'A'WHENPTT.POSTransactionTypeCode= 'I'THEN'I'ENDASProviderStatus,SP.NPINumberASContractNbr,'01'ASOther,'000000'ASPIN,UPPER(LEFT(SP.FirstName,28))ASContractName,'PROGRAMDIRECTOR'+SPACE(8)ASContractAddressAttention,UPPER(LEFT(ADDR.StreetAddress1,24))ASAddress1,UPPER(LEFT(ADDR.StreetAddress2,24))ASAddress2,UPPER(LEFT(ADDR.City,17))ASCity,UPPER(ST.PK_Geographic_Location_Code)ASState,LEFT(ADDR.Zip1,9)ASZipCd,SPACE(12)ASErrorCode2,SPACE(8)ASFillerFROMPOSPINTransactionASEDSINNERJOINNPIAssociationNAONNA.FKParentId=EDS.FKProviderId ANDNA.FKParentTypeLKPId= (SELECT Min(PKParentTypeLKPId)FROMParentTypeLKPWHEREParentCode='PRV')INNERJOINNPIASSPONSP.PKNPIId= NA.FKNPIId LEFTOUTERJOINPOSTransactionErrorASEDSERRONEDSERR.FKPOSPINTransactionId= EDS.PKPOSPINTransactionId LEFTOUTERJOINPOSTransactionErrorLKPASEDSERRCDONEDSERRCD.PKPOSTransactionErrorLKPId= EDSERR.FKPOSTransactionErrorLKPId LEFTOUTERJOINNPIAddressASNPIADDRONNPIADDR.FKNPIId= SP.PKNPIId ANDNPIADDR.IsCurrentAddress= 1 INNERJOIN[Address]ASADDRONADDR.PKAddressId=NPIADDR.FKAddressId INNERJOIN[State]ASSTONADDR.FK_State= ST.PKStateId INNERJOINCountyASCOONCO.PKCountyId= ADDR.FK_County INNERJOINPOSTransactionTypeLKPPTTONEDS.FKPOSTransactionTypeId=PTT.PKPOSTransactionTypeId
WHERE(EDS.TransactionSentDateISNULL)AND(EDS.TransactionReceivedDateISNULL)AND(EDS.FKPOSTransactionTypeIdIN(SELECTPKPOSTransactionTypeIdFROMPOSTransactionTypeLKPWHEREPOSTransactionTypeCodeIN('A','C','R'))OREDS.FKPOSTransactionTypeId= (SELECT Top1PKPOSTransactionTypeIdFROMPOSTransactionTypeLKPWHEREPOSTransactionTypeCode= 'I'))
Let me know if I can refine the script or any other advice is also welcome please thanks in advance friends.
I have couple of larger tables in my Database, but when I am trying to generate script for data of those tables using SSMS Wizard, I getting red sign and its failing, any help how can I generate data for the larger tables using Generate Script or some other option? thanks in advance my friends.
I am looking for help to correct my ERD.
I have revised it many times, but it comes back incorrect. I am currently in community college, and stressing over this as its one of the big impactors on my grade. Honestly Ill pay for help, its gotten to that point.
Reaching out to the community to pressure test our internal thinking.
We are building a simplified business intelligence platform that will aggregate metrics (i.e. traffic, backlinks) and text list (i.e search keywords, used technologies) from several data providers.
The data will be somewhat loosely structured and may change over time with vendors potentially changing their response formats.
Data volume may be long term 100,000 rows x 25 input vectors.
Data would be updated and read continuously but not at massive concurrent volume.
We'd expect to need to do some ETL transformations on the gathered data from partners along the way to the UI (e.g show trending information over the past five captured data points).
We'd want to archive every single data snapshot (i.e. version it) vs just storing the most current data point.
The persistence technology should be readily available through AWS.
Our assumption is our requirements lend themselves best towards DynamoDB (vs Amazon Neptune or Redshift or Aurora).
Is that fair to assume? Are there any other questions / information I can provide to elicit input from this community?
You will HAVE to have an ETL layer between your various sources and your database (assuming it is a relational DB). You need to get all your sources into a single format and deal with changing source structures which will need recoding the ETL to suit.
Never underestimate the power of human stupidity -
I'm old. I know stuff - JSOP
I wish to only begin a select statement if the top value date is found - this the first of the month. It's my way of knowing data has been added into a system at the beginning of the month e.g max date is 01/11/2018. If not take the last day of the previous month 31/10/2018. Table is called sales. Column is called SaleDateFrom.
I want to pull e.g data from SaleDateFrom <=01/11/2018...if not pull data
statement to change
where (SaleDateFrom<= '20181101'
and (SaleDateTo '20010101' or
SaleDateTo is null))