1

I am writing an ETL and I am trying to make my dimension reference the time dimension three times. But I can't even get the first one right.

The error says:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

My code:

SELECT e.eventName, 
       e.eventType, 
       e.numberOfPersons,
      (SELECT  timeKey 
       FROM    starSchema.dbo.timeDim 
       JOIN    reservation r 
              ON r.reservationDate = timeDim.DATE) AS resDate, 
       e.eventStartDate, 
       e.eventEndDate, 
       contact.name, 
       customer.company
FROM   events e 
JOIN   reservation r 
       ON e.reservationId = r.reservationId
JOIN   customer 
       ON e.customerId = customer.customerId
JOIN   contact 
       ON customer.contactId = contact.contactId

I am trying to join the source datetime with my time dimension and return timeKey.

My goal is to have a timeKey(int) to reference the time dimension.

I want to do this with r.reservationdate, e.eventStartDate and e.eventEndDate.

Picture of my time Dimension:

Picture of my time Dimension

Picture of Source:

Picture of Source

halfer
  • 19,824
  • 17
  • 99
  • 186
J. Meijerink
  • 11
  • 6
  • 1
    @StelioK: thanks for wanting to improve posts here. However, if you can make your edit messages a little more diplomatic, it may be appreciated `:-)` - not everyone is a native English speaker here. For me, the question is whether they have made an effort prior to asking a question, and whether they have made an effort in assembling the question. – halfer Jan 15 '18 at 22:33
  • Understood and I apologize for being ignorant in my justifications. I didn't realize they were public ;) – StelioK Jan 15 '18 at 23:01
  • No worries @StelioK. Yep, they can be seen by the person you're editing (they get a notification) and your [history is visible too](https://stackoverflow.com/users/5750208/steliok?tab=activity). – halfer Jan 16 '18 at 13:31

1 Answers1

2

I think you just want a correlated subquery:

(SELECT timeKey FROM starSchema.dbo.timeDim WHERE r.reservationDate = timeDim.DATE) AS resDate,

You don't want a JOIN in the subquery. You want the connection to be to the reservation table in the outer query.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Or just use a normal `JOIN` in the outer query? – MatBailie Jan 15 '18 at 21:38
  • @MatBailie what do you mean with a normal JOIN ? a full join? Left/right join? – J. Meijerink Jan 15 '18 at 21:46
  • @J.Meijerink - Whatever is appropriate in your situation.... if you know there is always a row, then it's an `INNER JOIN`... I just mean that there is no apparent need for a correlated sub-query at all. – MatBailie Jan 15 '18 at 22:03