Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Aug 2009
    Posts
    262

    Unanswered: this query can be converted into CTE or not?

    Hi ,

    i am exploring other ways to write this down . I can see a part of the query repeating in each case statement so can this be converted into CTE ? or using cte for an example :

    Code:
    SELECT
    CASE WHEN CCT.NewStatus = 'A' AND CCT.IsVoid IS NULL THEN TransDate
    	 WHEN CCT.PreviousStatus = 'A' AND CCT.NewStatus = 'C' THEN 
    			(SELECT TOP 1 C.TransDate FROM   CreditCardtransaCtion C
    				WHERE  CCT.refid = C.id AND C.NewStatus = 'A' AND isvoid IS NULL)
    
    WHEN CCT.PreviousStatus = 'C' AND CCT.NewStatus = 'R' THEN 
    			(select transdate from CreditCardtransaCtion  where id=
    				(Select c.Refid from CreditCardtransaCtion c where c.id=
    					(select c.id from CreditCardtransaCtion c where cct.refid=c.id)))
    END AS AuthDate,
    
    CASE When  CCT.PreviousStatus IS NULL AND CCT.NewStatus='A' AND CCT.IsVoid IS NULL then
    			(SELECT TOP 1 C.TransDate FROM   CreditCardtransaCtion C 
    				WHERE  CCT.id =C.refid AND C.NewStatus = 'C' AND isvoid IS NULL) 
    
    
    
    WHEN CCT.NewStatus = 'C' AND CCT.IsVoid IS NULL THEN TransDate
    
    
    WHEN CCT.PreviousStatus = 'C' AND CCT.NewStatus = 'R' THEN 
    			(SELECT TOP 1 C.TransDate FROM   CreditCardtransaCtion C 
    				WHERE  CCT.refid = C.id AND C.NewStatus = 'C' AND isvoid IS NULL)
    				ELSE null
    END AS CaptureDate,
    
    CASE WHEN CCT.NewStatus ='A' AND CCT.PreviousStatus IS NULL AND CCT.IsVoid IS NULL then
    			(select transdate from CreditCardtransaCtion where id=( 
    				select c.id from CreditCardtransaCtion c where c.refid=(
    					select refid from CreditCardtransaCtion c where refid=
    					 (select c.id from CreditCardtransaCtion c
    						where c.refid = cct.id))))
    
    WHEN CCT.NewStatus ='C' AND CCT.PreviousStatus ='A' AND CCT.IsVoid IS NULL then
    			(SELECT top 1 c.TransDate FROM   CreditCardtransaCtion C 
    				WHERE  C.NewStatus = 'R' AND c.refid=cct.id AND isvoid IS NULL)
    
    WHEN CCT.NewStatus = 'R' AND CCT.PreviousStatus='C' AND CCT.IsVoid IS NULL THEN  TransDate
             ELSE null
    END AS RefundDate
    
    FROM CreditCardtransaCtion  cct

  2. #2
    Join Date
    Aug 2009
    Posts
    262
    why is it every thread i start end up unanswered and in the end i end up doing it myself .. well mostly ...

    does it have to do with not being a student and a on job ? or my questions are not sqlserver questions .. o.O .. which they are ..

    or are they above any ones brain power or again is it about every arse for himself ?

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I have looked at your code.

    As a first reaction, I wouldn't grab at a CTE to solve your problem.

    Without knowing anything about your database structure, can't you replace
    Code:
    (select transdate 
    from CreditCardtransaCtion  
    where id = (Select c.Refid 
    	from CreditCardtransaCtion c 
    	where c.id = (select c.id 
    			from CreditCardtransaCtion c 
    			where cct.refid=c.id
    			)
    	)
    )
    with
    Code:
    (select transdate 
    from CreditCardtransaCtion
    	INNER JOIN CreditCardtransaCtion c ON
    		CreditCardtransaCtion.id = c.Refid AND
    		cct.refid = c.id
    )
    ?

    You also don't give any reason why you would want to change the given SQL. Is it to make it more human-readable? because of performance reasons? ...?

    why is it every thread i start end up unanswered
    We sometimes all have had that feeling, I think.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Well, very few (I think) members are students, and if they are, - they mostly didn't join the forum because they are students. I'd say there are quite a few professors here (judging by some answers ). And your questions are SQL-related, though I might say they are mostly development questions, while I am personally mostly interested and admin and architecture of SQL, so are many heavy hitters here.

    Are your questions above any one's brain power? Well, asking a question like that is not gonna get anything but flame. Most people that could have answered your question will simply blow you off and let you suffer on your own from now on, just to prove the point

    On the other hand, you seem to be handling most of your questions on your own, so continue doing so. It just doesn't look like you're stuck, and need a miracle to save your job.

    In short, - cool off, look around, and see may be there is something in the question that averts people from jumping into writing code for you
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mishaalsy View Post
    why is it every thread i start end up unanswered
    because you didn't ask it correctly

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Aug 2009
    Posts
    262

    hahahaha.

    I love you guys . lol .. okey i start posting the correct question.
    The reason why i want to change it to CTE is because i am writing almost every procedure ( for given scenario where i have to compare a row to previous one or need to compare rows to related records) the same way with subqueries and correlated subqueries i.e ( (select id or * (select from cct a where a.id = b.id ) from cct b )

    also i see many joins occcuring in my query making me to think what effect would i have if i succeed eliminating many joins to simpler form .

    I was reading accelerated sql server 2008 book by robert walters ( apress ) and found CTE handing the problem of same manner with CTE .
    But here my problem is i cannot identify effectively ( or entirely) how to place the joins in cte .

    i am giving my table structure/db stucture and rdja you are right i am working in development. and as for ppl might not answer me any more well ... all is love around us :P .. lets see .

    Its call center transaction logged in a table ( as a result set of many tables) and i have to drive out a transaction with its authenticate/capture( sold) / refund dates for every order .

    ordersid are coming from order table as orderid
    each order can have max 3 rows as A/C/R status ..
    after authentication it gets captured and after captured it may get refund .

    ( follow the other question i asked (Urgent help needed) for further reference . i have given complete table structure there as well as logic behind it)
    Refid is the ID itself . when an order get captured after authentication .. the id of A status become ref id of C status.


    table creditcardtransactions:

    Code:
    GO
    CREATE TABLE [dbo].[CreditCardTransaction](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[RefID] [int] NULL,
    	[OrderId] [int] NULL,
    	[CCID] [int] NULL,
    	[CreditCard] [varchar](24) NULL,
    	[TransId] [varchar](24) NULL,
    	[TransResult] [varchar](256) NULL,
    	[HolderName] [varchar](128) NULL,
    	[PaymentMethod] [varchar](512) NULL,
    	[Amount] [numeric](12, 2) NULL,
    	[UserID] [int] NULL,
    	[CurrencyFactor] [numeric](12, 4) NULL,
    	[MerchantIdFK] [tinyint] NULL,
    	[PreviousStatus] [char](1) NULL,
    	[NewStatus] [char](1) NULL,
    	[TransCode] [varchar](24) NULL,
    	[TransDate] [smalldatetime] NULL,
    	[API_Status] [tinyint] NULL,
    	[API_Response] [varchar](2048) NULL,
    	[isVoid] [bit] NULL,
    	[VoidDate] [smalldatetime] NULL,
    	[VoidBy] [int] NULL,
    	[VoidAPI_Response] [varchar](2048) NULL,
    	[isProcessable] [bit] NULL
    ) ON [PRIMARY]
    
    GO
    Inserts . These 3 inserts are enough to test the code thoroughly as it contains A/C/R of an order

    Code:
    GO
    INSERT INTO [Test].[dbo].[CreditCardTransaction]
               ([RefID]
               ,[OrderId]
               ,[CCID]
               ,[CreditCard]
               ,[TransId]
               ,[TransResult]
               ,[HolderName]
               ,[PaymentMethod]
               ,[Amount]
               ,[UserID]
               ,[CurrencyFactor]
               ,[MerchantIdFK]
               ,[PreviousStatus]
               ,[NewStatus]
               ,[TransCode]
               ,[TransDate]
               ,[API_Status]
               ,[API_Response]
               ,[isVoid]
               ,[VoidDate]
               ,[VoidBy]
               ,[VoidAPI_Response]
               ,[isProcessable])
         VALUES(114,
    NULL,
    97717,
    92615,
    '123456789123456',
    '3355060321',
    'This transaction has been approved.',
    'Mrs. K l Warrington',
    'auth_only',
    2.00,
    102,
    1.0000,
    20,
    NULL,
    'A',
    '478018',
    '12/17/2010 9:25:00 PM',
    1,
    '1|1|1|This transaction has been approved.|478018|P|3355060321|524826||199.00|CC|auth_only||Kerrie|Warrington|||||||||||||||||||||||AE2E36B8FDBE5C26DDFD887EA958267C|||||||||||||XXXX2365|Visa||||||||||||||||',
    NULL,
    NULL,
    NULL,
    NULL,
    False)
    GO
    GO
    INSERT INTO [Test].[dbo].[CreditCardTransaction]
               ([RefID]
               ,[OrderId]
               ,[CCID]
               ,[CreditCard]
               ,[TransId]
               ,[TransResult]
               ,[HolderName]
               ,[PaymentMethod]
               ,[Amount]
               ,[UserID]
               ,[CurrencyFactor]
               ,[MerchantIdFK]
               ,[PreviousStatus]
               ,[NewStatus]
               ,[TransCode]
               ,[TransDate]
               ,[API_Status]
               ,[API_Response]
               ,[isVoid]
               ,[VoidDate]
               ,[VoidBy]
               ,[VoidAPI_Response]
               ,[isProcessable])
         VALUES (
    151,
    114,
    97717,
    92615,
    '123456789123456',
    '3355060321',
    'This transaction has been approved.',
    'Mrs. K l Warrington',
    'auth_only',
    2.00,
    102,
    1.0000,
    20,
    'A',
    'C',
    '478018',
    '12/18/2010 4:49:00 AM',
    1,
    '1|1|1|This transaction has been approved.|478018|P|3355060321|524826||199.00|CC|auth_only||Kerrie|Warrington|||||||||||||||||||||||AE2E36B8FDBE5C26DDFD887EA958267C|||||||||||||XXXX2365|Visa||||||||||||||||',
    NULL,
    NULL,
    NULL,
    NULL,
    True)
    GO
    GO
    INSERT INTO [Test].[dbo].[CreditCardTransaction]
               ([RefID]
               ,[OrderId]
               ,[CCID]
               ,[CreditCard]
               ,[TransId]
               ,[TransResult]
               ,[HolderName]
               ,[PaymentMethod]
               ,[Amount]
               ,[UserID]
               ,[CurrencyFactor]
               ,[MerchantIdFK]
               ,[PreviousStatus]
               ,[NewStatus]
               ,[TransCode]
               ,[TransDate]
               ,[API_Status]
               ,[API_Response]
               ,[isVoid]
               ,[VoidDate]
               ,[VoidBy]
               ,[VoidAPI_Response]
               ,[isProcessable])
         VALUES
    (
    221,
    151,
    97717,
    92615,
    '123456789123456',
    '3368613227',
    'This transaction has been approved.',
    'Mrs. K l Warrington',
    'credit',
    2.00,
    1,
    1.0000,
    20,
    'C',
    'R',
    null,
    '12/27/2010 4:40:00 AM',
    1,
    '1|1|1|This transaction has been approved.||P|3368613227|||199.00|CC|credit||||||||||||||||||||||||||51C8041BF0E9A4F9E2A7E9E806E1D9E5|||||||||||||XXXX2365|Visa||||||||||||||||',
    NULL,
    NULL,
    NULL,
    NULL,
    True)
    GO

  7. #7
    Join Date
    Aug 2009
    Posts
    262
    WiM .. i replace it and tested .
    it is working for status A and C but throwing an error for R .

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by mishaalsy View Post
    or are they above any ones brain power or again is it about every arse for himself ?
    You'll find in life that the ones with brain power generally are employed and answer questions such as yours in their spare time. If you'd like a response faster, try polling the residents of your nearest homeless shelter.

    Quote Originally Posted by rdjabarov View Post
    Well, asking a question like that is not gonna get anything but flame.
    Damn. Robert was right again. That guy is like a psychic or something.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Aug 2009
    Posts
    262
    hahaha .. u said that right blindman . .. they are generally employed.

    I have been oracle dba for nearly 9 years and sql server is a new field for me. Since the guy who was suppose to do these work got fired couple of days back i have to wait for the company to hire a new person. Until then i have to do these development work.

    But pardon my ignorance , but dont u guys showing a bit more ego than necessary?

  10. #10
    Join Date
    Aug 2009
    Posts
    262

    Any ways itis done

    I have made it with cte ,

    Code:
    with testcct as
    (select 
    id,refid,orderid,CreditCard,transdate,previousstatus,newstatus,isvoid,IsProcessable,MerchantIDFK,UserID,API_Status,HolderName,TransID,Amount
    FROM   CreditCardtransaCtion
    where TransDate between @sDate AND @eDate 
    --AND NewStatus=@TransType
    and newstatus in ('A','C','R')
    and isvoid is null
    
    )
    
    select ID,ISNULL((SELECT DealNo FROM Orders Ord where Ord.ID = CCT.OrderId),'')DealNo,orderid,
    ISNULL((SELECT case ISNULL(SignatureText,'0') when '0' then '0' else '1' end FROM AuthenSignNet.dbo.SignAuthen S Where S.OrderID = CCT.OrderId),'0')Signature,
    	CCT.HolderName as CustomerName,	ISNULL(TransID,'')TransID,Amount As AuthAmount,TransDate,		
    
    
    case WHEN CCT.NewStatus = 'A' AND CCT.IsVoid IS NULL THEN cct.TransDate
        when CCT.PreviousStatus = 'A' AND CCT.NewStatus = 'C' THEN 
    (select transdate from testcct where cct.refid= testcct.id and testcct.previousstatus='A' and testcct.newstatus='C') 
    WHEN CCT.PreviousStatus = 'C' AND CCT.NewStatus = 'R' THEN 
    			(select transdate from testcct  where testcct.id=
    				(Select c.Refid from testcct c where c.id=
    					(select c.id from testcct c where cct.refid=c.id)))
    end as AuthDate,
    
    CASE 
    WHEN CCT.NewStatus = 'C' AND CCT.IsVoid IS NULL THEN TransDate
    When  CCT.PreviousStatus IS NULL AND CCT.NewStatus='A' AND CCT.IsVoid IS NULL then
    			(SELECT TOP 1 C.TransDate FROM   testcct C 
    				WHERE  CCT.id =C.refid AND C.NewStatus = 'C' AND isvoid IS NULL) 
    
    
    WHEN CCT.PreviousStatus = 'C' AND CCT.NewStatus = 'R' THEN 
    			(SELECT TOP 1 C.TransDate FROM   testcct C
    But it is costing more then the simple select proc.
    I am now going to try it with functions , placing some code i.e authdate, capturedate,refunddate in functions and put them in query

    some thing like "select field,field,func1, func2,func3, (rest of the select) from table ".

    i have come accross many references that functions are faster then query . though i donot agree but what the hell , i know as much about sql server as a dog know about climbing trees.

  11. #11
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by mishaalsy View Post
    WiM .. i replace it and tested .
    it is working for status A and C but throwing an error for R .
    You faced the venerable "an error" and still live to tell the tale!

    You choose not to repeat the error message on this forum. Was it too personal, did it contain racist or anti-religious statements, ... or did you just want to narrow the number of people who can help you further to those with mind-reading capacities?

    It also shines a bright new light on this:
    why is it every thread i start end up unanswered
    I tend to dig deeper to solve a problem posted on this forum, when the question also contains a few CREATE TABLE and INSERT statements with some sample data. It makes it a lot easier for us to help the one with the problem.
    Its call center transaction logged in a table ( as a result set of many tables) and i have to drive out a transaction with its authenticate/capture( sold) / refund dates for every order .
    I suspect there is a data warehouse ETL step generating this table. Can't you add a few extra columns in that table and the extra code for these calculated values
    Code:
    (select transdate 
    from testcct  
    where testcct.id=(Select c.Refid
     	from testcct c 
    	where c.id=
    		(select c.id 
    		from testcct c 
    		where cct.refid=c.id)
    		)
    	)
    It would simplify and speed up your final SELECT statement.

    And I still see no reason to write the SQL scripts in that way (... WHERE x = (SELECT ...)).
    Last edited by Wim; 01-05-11 at 08:40.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  12. #12
    Join Date
    Aug 2009
    Posts
    262
    the error was subquery passing multiple value ..

    if i could add columns in table i would have but that table is running live for applications. I took a small portion of records to test the code . adding column is not an option .

    i am sorry for not posting table structure caz in my opinion i thought it will not be needed

    any way now i am stuck in a greater problem ... Update trigger that contain procedure and updated two tables and 2nd table trigger firing again to update 3rd ....... grrrrrrrr

    that guy who got fired have make it a wormhole. posting new question in a new thread

    ps: the procedure qualify the need of development team ... dont know why i still feel it is unfinished .. but they want to move on creating front end for it . who am i to complain

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Wherever I see a trigger, I smell a rat...And the trigger is not the rat, it's the guy who wrote it
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by mishaalsy View Post
    But pardon my ignorance , but dont u guys showing a bit more ego than necessary?
    No. I have checked, and my ego is precisely in sync with my actual self-importance.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You might want to show a bit less leg. At least around the Office.

    I tried using the sample data you supplied, but it seems you have 24 columns in the table, and only 23 values to stuff in them.
    Code:
    Server: Msg 110, Level 15, State 1, Line 1
    There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
    Server: Msg 110, Level 15, State 1, Line 2
    There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
    Server: Msg 110, Level 15, State 1, Line 2
    There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
    Is the first value the ID field?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •