IBM Mainframe Forum

by **swarnabhagath** » Thu Aug 27, 2009 7:36 pm

Hi everyone,

Can I implement the following SQL SERVER 2005 query using SYNCSORT. Please reply ASAP.

INSERT INTO #tmp1
SELECT BusinessCode
,1 As SectionType
,ISNULL(MAX(DetailSectionName),'') As DetailSectionName
,StatementDate
,AgentNumber
,ISNULL(GroupID,'')
,PolicyNumber
,NULL As NewBusConv
,MAX(NameOrDescription) As PolicyHolder
,IssueDate
,WritingAgentNumber
,MIN(EffectiveDate) As EffectiveDate
,TranCode
,NULL As PaidToDate
,0 As Duration
,SUM(PremiumAmount) As PremiumAmount
,CommissionRate
,SplitPercentage
,0 As CommissionAmount
,AdvanceRate
,ISNULL(SUM(OriginalAdvance),0) As OriginalAdvance
,0 As Recovery
,0 As Recoup
,0 As AdvanceBalance
,0 As MiscAmount
,0 As MiscYTD
,ISNULL(MAX(PlanDescription),'') AS PlanDescription
,NULL As WritingAgentName
,MAX(Mode) as Mode
FROM Comm_Details_Stag
WHERE BusinessCode = @BusinessCode
AND StatementDate = @StatementDate
AND (TranID = 'IA' or TranID = 'SA' or TranID = 'OA')
GROUP BY BusinessCode
,StatementDate
,GroupID
,AgentNumber
,PolicyNumber
,IssueDate
,WritingAgentNumber
,TranCode
,CommissionRate
,SplitPercentage
,AdvanceRate
,PlanDescription
,DetailSectionName

by **dick scherrer** » Fri Aug 28, 2009 6:26 am

Hello and welcome to the forum. . .

First - we don't do ASAP

Second - this is a mainframe forum. Last time time i checked, Microsoft has not provided a mainframe release of SQL Server 2005 (or any other version). If your organization has licensed Syncsort for Windows, i suggest you open an issue with Syncsort support. . .

by **swarnabhagath** » Fri Aug 28, 2009 12:05 pm

Hi,

My requirement is to implement this query done on a table with "SYNCSORT on a mainframe sequential (PS) file" i.e., doing GROUP BY ,SUMMING few fileds, getting MAX, MIN values on other fileds as done in the query. Can sysncsort be helpful for my requirement???

by **dick scherrer** » Fri Aug 28, 2009 11:23 pm

Hello,

SYNCSORT on a mainframe sequential (PS) file

What does this mean?

Earlier, you mentioned SQL Server and Syncsort. Which Syncsort product is being used? Syncsort for the mainframe and Syncsort for Windows are completely separate products.

Can sysncsort be helpful for my requirement???

Until the requirement is better understood, we may not be able to help much. How is/was the requirement described?

Is this process to run on a Win-based system or on a mainframe? Is there some mainframe process that generates a sequential file that is then to be downloaded?

by **arcvns** » Mon Aug 31, 2009 10:01 pm

Hi Dick,

I think the OP is trying to run the sql using SyncSort. SyncSort has DB2 query support since 1.2 version. But the one which the OP has posted according to him is an "SQL SERVER 2005 query " and not a DB2 sql.

by **dick scherrer** » Tue Sep 01, 2009 3:12 am

Hi Arun,

I think the OP is trying to run the sql using SyncSort.

Yup, i think so too

I'm just not sure if there is any cross-platform requirement or if the entire process is to run on the Win-based environment

Maybe, we'll get some clarification. . .

IBM Mainframe Forum

implementing the following the SQL 05 query using sync sort

implementing the following the SQL 05 query using sync sort

Re: implementing the following the SQL 05 query using sync sort

Re: implementing the following the SQL 05 query using sync sort

Re: implementing the following the SQL 05 query using sync sort

Re: implementing the following the SQL 05 query using sync sort

Re: implementing the following the SQL 05 query using sync sort