implementing the following the SQL 05 query using sync sort

[swarnabhagath]

Hi everyone,

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

Code: Select all

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

[dick scherrer]

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. . .

[swarnabhagath]

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???

[dick scherrer]

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?

[arcvns]

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.

[dick scherrer]

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. . .