Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

pl help i need ms access transform statement in mssql urgent

access mssql

Best Answer lespauled, 04 June 2013 - 10:39 AM

You might want to format it so it's easier for you to understand:

 

TRANSFORM IIf(IsNull(Sum(gtab10.qty)),0,(Sum(gtab10.qty))) AS Expr1 

SELECT gtab10.BatchId, 
       BatchNo 
FROM   gtab05 
INNER JOIN (
   gtab09 
   INNER JOIN gtab10 
   ON gtab09.TranId = gtab10.TranId
) 
ON gtab05.BatchId = gtab10.BatchId 
WHERE gtab09.acyrid = 9 
  AND gtab09.VrId In (6, 10, 11, 12, 18, 17, 13, 25, 15, 23, 24) 
GROUP BY gtab10.BatchId, BatchNo 
ORDER BY gtab10.BatchId 
PIVOT gtab09.VrId In (6, 10, 11, 12, 18, 17, 13, 25, 15, 23, 24)

The inner join will cause a problem, so change it to:

FROM gtab05 
INNER JOIN gtab10 
ON gtab05.BatchId = gtab10.BatchId 
INNER JOIN gtab09 
ON gtab09.TranId = gtab10.TranId
Go to the full post


This topic has been archived. This means that you cannot reply to this topic.
4 replies to this topic

#1 viveq

viveq

    CC Regular

  • Member
  • PipPipPip
  • 46 posts

Posted 04 June 2013 - 04:19 AM

this is the Access query

 

TRANSFORM IIf(IsNull(Sum(gtab10.qty)),0,(Sum(gtab10.qty))) AS Expr1 SELECT gtab10.BatchId, BatchNo FROM gtab05 INNER JOIN (gtab09 INNER JOIN gtab10 ON gtab09.TranId = gtab10.TranId) ON gtab05.BatchId = gtab10.BatchId WHERE gtab09.acyrid = 9 AND gtab09.VrId In (6, 10, 11, 12, 18, 17, 13, 25, 15, 23, 24) GROUP BY gtab10.BatchId, BatchNo ORDER BY gtab10.BatchId PIVOT gtab09.VrId In (6, 10, 11, 12, 18, 17, 13, 25, 15, 23, 24)

 


:thumbup1:  ..::To Finish First You Have To Start First::.. :thumbup1:


#2 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts

Posted 04 June 2013 - 08:02 AM

Your IIF statement can be implemented using case. Instead of using the TRANSFORM/PIVOT, you should probably just drop them and let whatever language is receiving this query do additional processing on it.


Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#3 lespauled

lespauled

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1360 posts

Posted 04 June 2013 - 10:39 AM   Best Answer

You might want to format it so it's easier for you to understand:

 

TRANSFORM IIf(IsNull(Sum(gtab10.qty)),0,(Sum(gtab10.qty))) AS Expr1 

SELECT gtab10.BatchId, 
       BatchNo 
FROM   gtab05 
INNER JOIN (
   gtab09 
   INNER JOIN gtab10 
   ON gtab09.TranId = gtab10.TranId
) 
ON gtab05.BatchId = gtab10.BatchId 
WHERE gtab09.acyrid = 9 
  AND gtab09.VrId In (6, 10, 11, 12, 18, 17, 13, 25, 15, 23, 24) 
GROUP BY gtab10.BatchId, BatchNo 
ORDER BY gtab10.BatchId 
PIVOT gtab09.VrId In (6, 10, 11, 12, 18, 17, 13, 25, 15, 23, 24)

The inner join will cause a problem, so change it to:

FROM gtab05 
INNER JOIN gtab10 
ON gtab05.BatchId = gtab10.BatchId 
INNER JOIN gtab09 
ON gtab09.TranId = gtab10.TranId

My Blog: http://forum.codecal...699-blog-77241/
"Women and Music: I'm always amazed by other people's choices." - David Lee Roth

#4 viveq

viveq

    CC Regular

  • Member
  • PipPipPip
  • 46 posts

Posted 04 June 2013 - 08:35 PM

Thanks a lot


:thumbup1:  ..::To Finish First You Have To Start First::.. :thumbup1:


#5 viveq

viveq

    CC Regular

  • Member
  • PipPipPip
  • 46 posts

Posted 04 June 2013 - 09:00 PM

but it doesnt work :D


:thumbup1:  ..::To Finish First You Have To Start First::.. :thumbup1:





Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download