implementing the following the SQL 05 query using sync sort



Support for NetApp SyncSort for z/OS, Visual SyncSort, SYNCINIT, SYNCLIST and SYNCTOOL

implementing the following the SQL 05 query using sync sort

Postby 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
swarnabhagath
 
Posts: 2
Joined: Fri May 29, 2009 1:32 pm
Has thanked: 0 time
Been thanked: 0 time

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

 

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

Postby 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. . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

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

Postby 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???
swarnabhagath
 
Posts: 2
Joined: Fri May 29, 2009 1:32 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby 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?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

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

Postby 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.
Arun
User avatar
arcvns
 
Posts: 55
Joined: Sat Feb 28, 2009 12:36 am
Location: India
Has thanked: 0 time
Been thanked: 0 time

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

Postby 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. . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times


Return to Syncsort/Synctool

 


  • Related topics
    Replies
    Views
    Last post