Selection condition of a Query

Startbeitrag von Vassilis Boutsikas am 15.03.2013 07:34

In a query I maintain with query editor, I need to add selection contition between two items of the query, but with editor seems to be imposible.
Any advice is wellcome.

Vassilis

Antworten:

Vassilis

Define a 'Calculated Item' in the query editor with a case statement

CASE WHEN MyTable.MyField1 = MyTable.MyField2 THEN True ELSE False

Then declare a condition on the result of this - true or false - in the normal way

von DerekT - am 15.03.2013 09:51
Dear DerekT,

among other conditions, the condition between items of query is:
(Date_end = '' OR Date_end >= {DateCheck})
AND
(Date_end_final = '' OR Date_end_final >= Date_end)
OR
Date_end_metabolis >= {DateCheck}

Because I'm not SQL specialist, would you like to be more specific ?

Regards

Vassilis

von Vassilis Boutsikas - am 15.03.2013 10:28
Vassilis. Read the help for the select sql command. You may use "Case" in the select clause.
You also (maybe) can use a subquery (not in select clause because it isn't supported), but it depends on your query.

If you don't find the info in the WD help, google "select sql".

Regards.

von RAUL2 - am 15.03.2013 12:46
Hi Vassilis,

Here's an example of a dynamic join based on passed query parameters which you can store as such in the WD query editor:

SELECT
Attachment.AttachmentID AS AttachmentID,
Attachment.AttachmentTypeID AS AttachmentTypeID,
Attachment.LinkToType AS LinkToType,
Attachment.LinkToID AS LinkToID,
Attachment.Url AS Url,
Attachment.Description AS Description,
Attachment.CreatedBy AS CreatedBy,
Attachment.CreatedOn AS CreatedOn,
Attachment.LastUpdatedBy AS LastUpdatedBy,
Attachment.LastUpdatedOn AS LastUpdatedOn,
UserDefCodeAT.Description AS AttachmentType,
CASE
WHEN ( Attachment.LinkToType = 1) THEN 'Company '
WHEN ( Attachment.LinkToType = 2) THEN 'Person '
WHEN ( Attachment.LinkToType = 3) THEN 'Dossier '
WHEN ( Attachment.LinkToType = 4) THEN 'Subdossier '
WHEN ( Attachment.LinkToType = 5) THEN 'Document '
WHEN ( Attachment.LinkToType = 6) THEN 'ProductGroup '
ELSE 'UNKNOWN'
END
AS LinkToDescription,
CASE
WHEN ( Attachment.LinkToType = 1) THEN 'BT_Company_16_1.png'
WHEN ( Attachment.LinkToType = 2) THEN 'BT_Person_16_1.png'
WHEN ( Attachment.LinkToType = 3) THEN 'BT_DOSSIER_16_1.png'
WHEN ( Attachment.LinkToType = 4) THEN 'BT_SUBDOSSIER_16_1.png'
WHEN ( Attachment.LinkToType = 5) THEN 'BT_DOCUMENT_16_1.png'
WHEN ( Attachment.LinkToType = 6) THEN 'BT_ProductGroup_16_1.png'
ELSE 'UNKNOWN'
END
AS LinkToIcoon,
CASE
WHEN ( Attachment.LinkToType = 1) THEN Company.CommercialName
WHEN ( Attachment.LinkToType = 2) THEN Person.Name + ' ' + Person.FirstName
WHEN ( Attachment.LinkToType = 3) THEN Dossier.Description
WHEN ( Attachment.LinkToType = 4) THEN SubDossier.Reference1 + ' ' + SubDossier.Reference2
WHEN ( Attachment.LinkToType = 5) THEN Document.DocumentNr
WHEN ( Attachment.LinkToType = 6) THEN ProductGroup.Description
ELSE 'UNKNOWN'
END
AS LinkDescription,
CASE WHEN ( Attachment.LinkToType = 1) THEN Attachment.LinkToID ELSE 0 END AS JoinCompanyID,
CASE WHEN ( Attachment.LinkToType = 2) THEN Attachment.LinkToID ELSE 0 END AS JoinPersonID,
CASE WHEN ( Attachment.LinkToType = 3) THEN Attachment.LinkToID ELSE 0 END AS JoinDossierID,
CASE WHEN ( Attachment.LinkToType = 4) THEN Attachment.LinkToID ELSE 0 END AS JoinSubDossierID,
CASE WHEN ( Attachment.LinkToType = 5) THEN Attachment.LinkToID ELSE 0 END AS JoinDocumentID,
CASE WHEN ( Attachment.LinkToType = 6) THEN Attachment.LinkToID ELSE 0 END AS JoinProductGroupID
FROM
Attachment
LEFT OUTER JOIN UserDefCode UserDefCodeAT ON UserDefCodeAT.UserDefCodeID = Attachment.AttachmentTypeID
LEFT OUTER JOIN Company ON Company.CompanyID = joinCompanyID
LEFT OUTER JOIN Person ON Person.PersonID = joinPersonID
LEFT OUTER JOIN Dossier ON Dossier.DossierID = joinDossierID
LEFT OUTER JOIN SubDossier ON SubDossier.SubDossierID = joinSubDossierID
LEFT OUTER JOIN Document ON Document.DocumentID = joinDocumentID
LEFT OUTER JOIN ProductGroup ON ProductGroup.ProductGroupID = joinProductGroupID
WHERE
(
Attachment.AttachmentID IN ({pAttachmentID_List})
AND Attachment.AttachmentTypeID IN ({pAttachmentTypeID_List})
AND Attachment.Description LIKE %{pDescription_Contains}%
AND
( JoinCompanyID IN ({pCompanyID_List})
OR JoinPersonID IN ({pPersonID_List})
OR JoinDossierID IN ({pDossierID_List})
OR JoinSubDossierID IN ({pSubDossierID_List})
OR JoinDocumentID IN ({pDocumentID_List})
OR JoinProductGroupID IN ({pProductGroupID_List})
)
)
ORDER BY Attachment.LinkToType ASC, UserDefCodeAT.Description ASC



von Peter Holemans - am 15.03.2013 14:35
All the above solutions are valid but in some situation it's easy to edit the sql code of the query in the query editor, add the conditions you need and reverse engineering the query.

http://doc.windev.com/en-US/?2032060

von Paulo Oliveira - am 15.03.2013 18:49
I'm tryin to enter the following piece of SQL code I mention before:
CASE
WHEN (symbaseis.Date_end = '' OR symbaseis.Date_end >= WL.DateSys())
AND (symbaseis.Date_end_metabolis = '' OR symbaseis.Date_end_metabolis >= symbaseis.Date_end)
OR symbaseis.Date_end_metabolis >= WL.DateSys()
THEN True
ELSE False
END

When validate the code an error message appears:
"Error found in expression
Unexpected word: THEN
Check the syntax."

I suspect the use of multiple AND / OR in the conditions. Is that the point?

Regards

Vassilis

von Vassilis Boutsikas - am 16.03.2013 10:41
I found the solution. The cause of problem is the use of True / False keywords.
Seems that SQL does not recognise them, so I used 1 / 0.

Thanks to all for the help

Regards

Vassilis

von Vassilis Boutsikas - am 17.03.2013 10:39
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.