New Topic

Community Topic Question View Builder

Chris McCarthy

Yellowfin Support Team

Assigned

Category View Builder

Product Version 9.0

Incorrect syntax near the keyword 'WITH'.

Tiago shared this question ago

Completed

Hi.

I have an SQL query using keyword WITH.

If I use this query to create a view, Yellowfin gives the error used for this title. However, if I use the same query to create a report or a Transformation Flow, Yellowfin says the query is valid.

Can you help me understand the problem?

Thank you.

The query is something like this (name of the tables and names of the attributes were modified):

WITH D AS (
		select
		no,
		de,
		convert(datetime, date1, 103) as ate1,
		convert(datetime, date2, 103) as ate2,
		first,
		last,
		ti
		from table1),

PWS AS (
		select
		erId,
		onId,
		er,
		te,
		ode,
		tion,
		nd
		from table2),

L AS (
		select
		id,
		ice
		from table3),

SE AS (
		select
		eid,
		id,
		ate
		from table4),

GSE AS (
		select
		ueId,
		rade
		from table5)

select top (100)
D.no,
D.de,
D.ate1,
D.ate2,
D.first,
D.last,
D.ti,
PWS.te,
PWS.ode,
PWS.tion,
L.ice,
SE.ate,
GSE.rade
from D
left join PWS on PWS.er = D.no COLLATE Latin1_General_CI_AS
left join L on L.id = PWS.onId
left join SE on SE.id = PWS.erId
left join GSE on GSE.ueId = SE.eId
where	PWS.te not like '%something1%' and
		PWS.nd = 'something2' and
		D.ate2 is not null and
		CONVERT(datetime, D.ate2, 103) >= CONVERT(DATETIME, '01-01-1960 00:00:00', 103) and
		PWS.te not like '%aomething3%'
order by D.ate2 desc

The same question

Best Answer

Chris McCarthy ●

Hi Tiago,

No worries!

If there's nothing else I'll mark this ticket as complete and we can track progress of the enhancement from the other post.

Let me know.

Kind regards,

Chris

Replies (2)

Chris McCarthy ●

Hi Tiago,

Thanks for contacting Yellowfin. As you're posting from an @gmail address, unfortunately it hasn't filled in which organisation you work for. Can you let me know?

Bear in mind that CTEs like the WITH statement are not supported in freehand SQL views at this time, but it may be implemented at a later date.

Kind regards,

Chris

Reply URL

Tiago ●

Hi Chris.

I work for Blueworks. Should I create an account with my professional email?

Thank you for the quick reply.

Best regards,

Tiago

URL

Chris McCarthy ●

Hi Tiago,

Thanks very much!

I can't find Blueworks under our list here, are you a new customer or perhaps working with one of our partners?

Kind regards,

Chris

URL

Tiago ●

Hi Chris.

We are a new customer, and purchased our first license to Yellowfin less than 2 months ago.

Best regards,

Tiago marçal

URL

Chris McCarthy ●

Hi Tiago,

Thanks very much!

So we do have an open Idea request (what we also call an Enhancement request) to see this feature added to Yellowfin, but I can see it's been some time since it was last looked at. Please could you have a look at the post and check if it fits the goal of what you're trying to achieve here. If so, please could you give it a thumbs up.

I'll see if I can follow up on the request internally and get some feedback and hopefully push it along.

Welcome to Yellowfin! I hope it's all going okay. If you have any other questions, please don't hesitate to ask!

Kind regards,

Chris

URL

Tiago ●

Hi Chris.

I think you are right. I thumbs up the request.

Thank you.

Best Regards,

Tiago

URL

Tiago ●

Edited: Duplicated reply.

URL

Chris McCarthy ●

Hi Tiago,

No worries!

If there's nothing else I'll mark this ticket as complete and we can track progress of the enhancement from the other post.

Let me know.

Kind regards,

Chris

URL

Tiago ●

Hi Chris.

Yes, mark this ticket as complete.

Bets regards,

Tiago

URL

Chris McCarthy ●

Hi Tiago,

Great! Have an excellent weekend!

Kind regards,

Chris

URL

Ricki Garfield ●

Navigating the academic landscape can be challenging. Seeking help from an essay writing service like https://scamfighter.net/review/unemployedprofessors.com ensures expert assistance, maintaining high-quality standards to support students effectively in their academic journey.

Reply URL