Page 1 of 1

implementing the following the SQL 05 query using sync sort

PostPosted: Thu Aug 27, 2009 7:36 pm
by swarnabhagath
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

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

PostPosted: Fri Aug 28, 2009 6:26 am
by 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. . .

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

PostPosted: Fri Aug 28, 2009 12:05 pm
by 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???

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

PostPosted: Fri Aug 28, 2009 11:23 pm
by 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?

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

PostPosted: Mon Aug 31, 2009 10:01 pm
by 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.

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

PostPosted: Tue Sep 01, 2009 3:12 am
by 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. . .