error in SQL query 

error in SQL query 

  
Hi all,

I get an error due to the fact that [DatumVerwijzing] is of data tape Date; all the other are Text.
How can I retrieve this information in this query?

Regards,
Arnold 

SELECT {Verwijsbrief}.[Id], {Kind}.[Voornaam] + ' ' + {Kind}.[TussenvoegselKind] + ' ' + {Kind}.[Achternaam] + '- '+ {Verwijsbrief}.[Disicpline] + ' ' + {Verwijsbrief}.[DatumVerwijzing]
FROM {Kind},{Verwijsbrief}
If you absolutely must use an Advanced Query here (and I do not see any reason to use an Advanced Query here), then you should use CAST or CONVERT in your query to transform the date field to a varchar or nvarchar field.

J.Ja
Hi Justin,

For a couple of days I'm puzzling where to fit the convert query into. Trying google I find to set it up like this: 
select convert(varchar, getdate(), 105) -> that should give me a date like: 30-12-2013.

In the query I use in the outsystems platform I have to convert the {Verwijsbrief}.[DatumVerwijzing], but I don't know where in the query beneath I have to put it (=> 
select convert(varchar, getdate(), 105)) ......?

Can you help me out?


SELECT {Verwijsbrief}.[Id], {Kind}.[Voornaam] + ' ' + {Kind}.[TussenvoegselKind] + ' ' +  {Kind}.[Achternaam] + ' ' + {Verwijsbrief}.[Disicpline] + ' ' + {Verwijsbrief}.[DatumVerwijzing]
FROM {Verwijsbrief} INNER JOIN {Kind} on {Kind}.[Id] = {Verwijsbrief}.[Id]
ORDER BY {Kind}.[Voornaam]



Arnold
Hi Arnold,

Something like this should work:
convert (varchar, {Verwijsbrief}.[DatumVerwijzing], 105)

Regards,
João Rosado
Thx Joao, 

But where do I put it?
Whatever I try placing it into the query I always get the warning:
Unexpected SQL Unexpected 'convert' in SQL statement of GetVerwijsbriefNaam.

Regards,
Arnold

Hi Arnold,

That's only a warning right? Displayed because convert isn't recognized has a Keyword. But if you can publish, you can test, and if the query is right it will work.
Hi Nuno,

It's indeed a warning.
However I've tested the query and then I receive an error.
"Error in advanced query GetVerwijsbriefNaam: Incorrect syntax near the keyword 'convert'.
I assume that when I publish it, I'll get the same error.......

SELECT {Verwijsbrief}.[Id], {Kind}.[Voornaam] + ' ' + {Kind}.[TussenvoegselKind] + ' ' +  {Kind}.[Achternaam] + ' ' + {Verwijsbrief}.[Disicpline] + ' '+ {Verwijsbrief}.[DatumVerwijzing]
convert (varchar, {Verwijsbrief}.[DatumVerwijzing], 105)
FROM {Verwijsbrief} INNER JOIN {Kind} on {Kind}.[Id] = {Verwijsbrief}.[Id]
ORDER BY {Kind}.[Voornaam]
Is it due to the fact that I have the 'convert statement' placed in the wrong place?  or .....

Regards,
Arnold 





 
Yes, either you need to add a comma right before your convert, so that it is an extra column in your output stucture, or you need to remove the {Verwijsbrief}.[DatumVerwijzing] right in front of it, so that the convert is added to your string. Most likely that is the behavior you are looking for:
SELECT {Verwijsbrief}.[Id], {Kind}.[Voornaam] + ' ' + {Kind}.[TussenvoegselKind] + ' ' +  {Kind}.[Achternaam] + ' ' + {Verwijsbrief}.[Disicpline] + ' '+ convert (varchar, {Verwijsbrief}.[DatumVerwijzing], 105)
FROM {Verwijsbrief} INNER JOIN {Kind} on {Kind}.[Id] = {Verwijsbrief}.[Id]
ORDER BY {Kind}.[Voornaam]
Hi there,

I'm facing a difficulty with the inner join code. Probably due to the fact that it is somewhat more complex than the above. I like to have come information in a combo box and therefore retrieving information from several tables. buit I don't get it right. In the enclosure I've explained it into more detail.

Anyone there to give me a hand?

Regards,
Arnold
Table KIND isn't in the FROM so no columns from it can be read. And that second INNER JOIN is very strange.
Thx for the reply,

In the attachment I've mentioned several attempts to get the proper data in the combobox. For which attempt is the one that comes near the right solutioin?
I get the same error in all the attempts, but I think it has got nothing to do with the code itself (?). Can anyone shed a light on this error?

I've also mentioned an example of MS Access. I've created my current application in there and in Access I get the chance to pick the attributes I want and Access generates the code. Is in Outsystems a sortlike tool available?   

Regards,
Arnold
Hi,

What kind of ooutputstructire are you using?
I cannot see that in the document.

I expect you have a structure named for example ddlTest
with the following attributes:

Id - InvoiceId
Description - Text (150)



Yes indeed.


Sorry, I was a bit to fast. 
I've attached the oml.

It's te structure FactuurNaam.

Regards,
Arnold

Edited by André Vieira on request of user to remove attachment.