B4J Question [SOLVED] SQL question.

[Yafuhenk]

Hi,

I need a SQL query and I am not able to get it working (lack of knowledge I assume ;-)
Let me first tell what it should do and what I have done so far.

Via OData I import Opportunities from our business CRM system.
Some of fields are of the Opportunities mandatory. I want to the software to check if the mandatory fields are filled.
One opportunity can have several records. If a mandatory field in at least one of the records are filled things are ok for me.

In the SQL code below you see what I have done so far:

DECLARE @OppoCode AS VARCHAR(9)
SET @OppoCode = '12345789';
WITH Q1 AS (SELECT
    CASE WHEN NOT EXISTS (SELECT * FROM Opportunities WHERE OppoCode = @OppoCode AND PlantCode = '#') THEN 'Y' ELSE 'N' END AS 'Plant',
    CASE WHEN NOT EXISTS (SELECT * FROM Opportunities WHERE OppoCode = @OppoCode AND ApplicationCode = '#') THEN 'Y' ELSE 'N' END AS 'Application',
    CASE WHEN NOT EXISTS (SELECT * FROM Opportunities WHERE OppoCode = @OppoCode AND ArtGrpCode = '#') THEN 'Y' ELSE 'N' END AS 'Article Group',
    CASE WHEN NOT EXISTS (SELECT * FROM Opportunities WHERE OppoCode = @OppoCode AND StatusTrafficLightCode = '#') THEN 'Y' ELSE 'N' END AS 'Status Traffic Light',
    CASE WHEN NOT EXISTS (SELECT * FROM Opportunities WHERE OppoCode = @OppoCode AND DescriptionLong = 'Not assigned') THEN 'Y' ELSE 'N' END AS 'Description Long',
    CASE WHEN NOT EXISTS (SELECT * FROM Opportunities WHERE OppoCode = @OppoCode AND NextStep = 'Not assigned') THEN 'Y' ELSE 'N' END AS 'Next Step',
    CASE WHEN NOT EXISTS (SELECT * FROM Opportunities WHERE OppoCode = @OppoCode AND SalesUnit = '#') THEN 'Y' ELSE 'N' END AS 'Sales Unit',
    CASE WHEN NOT EXISTS (SELECT * FROM Opportunities WHERE OppoCode = @OppoCode AND ExpTotalSales = 0) THEN 'Y' ELSE 'N' END AS 'Expected Total Turnover'),
Q2 AS (SELECT Result FROM Q1 unpivot (Result FOR col in ([Plant], [Application], [Article Group], [Status Traffic Light], [Description Long], [Next Step], [Sales Unit], [Expected Total Turnover]))un)
SELECT CASE WHEN NOT EXISTS (SELECT * FROM Q2 WHERE Result = 'N') THEN 'Y' ELSE 'N' END AS 'Final Result'

The result of the code below is either 'Y' (all mandatory field are filled in) or 'N' (not all mandatory fields are filled in)

Here is my question:
How can I do something like this to for all Opportunities numbers instead of only one?

Thanks !!

 

[Yafuhenk]

This seems to do the job:

WITH Q1 AS
(SELECT
    Oppocode
FROM
    Opportunities
GROUP BY
    Oppocode
HAVING
    (min(PlantCode) = max(PlantCode) AND min(PlantCode) = '#') OR
    (min(ApplicationCode) = max(ApplicationCode) AND min(ApplicationCode) = '#') OR
    (min(ArtGrpCode) = max(ArtGrpCode) AND min(ArtGrpCode) = '#') OR
    (min(StatusTrafficLightCode) = max(StatusTrafficLightCode) AND min(StatusTrafficLightCode) = '#') OR
    (min(DescriptionLong) = max(DescriptionLong) AND min(DescriptionLong) = '#') OR  
    (min(NextStep) = max(NextStep) AND min(NextStep) = '#') OR  
    (min(SalesUnit) = max(SalesUnit) AND min(SalesUnit) = '#') OR
    (min(ExpTotalEuro) = max(ExpTotalEuro) AND min(ExpTotalEuro) = 0))
SELECT PlantCode, ApplicationCode, ArtGrpCode, StatusTrafficLightCode, DescriptionLong, NextSTep, SalesUnit, ExpTotalEuro FROM Opportunities, Q1 WHERE Opportunities.OppoCode = Q1.OppoCode