SQL Question , Convert Formula

[soldat452002]

Hello

Im trying to create this Excel formula in SQL. How can I do this in SQL.




Data Sources

Contract.StatusCode

License.LicenseExpiration

Education.ProductTraining

Contract.StatusEffective


=IF([@[STATUS CODE]]<>"COMPLETE", "NOT ELIGIBLE TO SELL",IF([@[LICENSE EXPIRATION]]<=TODAY(),"NOT ELIGIBLE TO SELL",IF([STATUS EFFECTIVE]>=[PRODUCT TRAINING],[STATUS EFFECTIVE],[PRODUCT TRAINING])))

[pbaldy]

Look at the IIf() function. The Date() function returns the current date, the Now() function returns the current date and time.

[CJ_London]

As paul says - a fairly easy translation from excel to access. Although not applicable in the example provided be aware with regards the access iif function that you have to provide a resolution for both true and false. In the case of excel, the IF function will work with only a true resolution e.g.

IF(a=b,true,false) works
IF(a=b,true) works

iif(a=b,true,false) works
iif(a=b,true) fails

[soldat452002]

As paul says - a fairly easy translation from excel to access. Although not applicable in the example provided be aware with regards the access iif function that you have to provide a resolution for both true and false. In the case of excel, the IF function will work with only a true resolution e.g.

IF(a=b,true,false) works
IF(a=b,true) works

iif(a=b,true,false) works
iif(a=b,true) fails

It does not work in SQL.


SELECT DISTINCT

BROKERID,
CONTRACTID,
JURISDICTIONCD AS "LICENSE STATE",
TERMINATIONREASONCODE AS "STATUS CODE",

IF(TERMINATIONREASONCODE = "COMPLETE", TRUE, FALSE) AS "WORKS"


FROM Producer P

INNER JOIN ProducerVer PV on PV.ProducerNo = P.ProducerNo
INNER JOIN Contract C on C.ProducerNo = P.ProducerNo
INNER JOIN ContractVer CV on CV.ContractNo = C.ContractNo



WHERE CV.PROSTA = 1 AND CONTRACTTYPE IN ('Selling Agent', 'Producer')

[CJ_London]

you use iif, not if

[soldat452002]

you use iif, not if

It is a SQL oracle database. not access. sorry

Only thing Im missing is Returning the highest date in the bold section. I was able to do that in Access.

SELECT P.PRODUCERID, C.CONTRACTID, TERMINATIONREASONCODE, to_char(to_date(DateTERM, 'yyyy/mm/dd'), 'mm/dd/yyyy') AS "LIC Expiration", to_char(to_date(E.COMPLETIONDATE, 'yyyy/mm/dd'), 'mm/dd/yyyy') AS "trAINING DATE",
to_char(to_date(CV.DATEEFF, 'yyyy/mm/dd'), 'mm/dd/yyyy') AS "STATUS EFF",
CASE
WHEN CV.TERMINATIONREASONCODE <> 'COMPLETE' THEN 'NOT ELIGIBLE TO SELL'
WHEN LV.DATETERM < '20180726' THEN 'NOT ELIGIBLE TO SELL'
WHEN CV.DATEEFF > E.COMPLETIONDATE THEN 'RETURN HIGHEST DATE OF TWO'
ELSE 'ELIGIBLE TO SELL'
END AS "ETS"

[June7]

@Ajax, for IIf() in Access (ControlSource and VBA expressions) calcs must have valid result but IIf() in query does not.

IIf(1=1, 10/2, 10/0)

Will fail in Access but works fine in query.

[CJ_London]

IIf() in query does not.

that wasn't my point - IIf(1=1, 10/2) in a query will fail as an incomplete expression

@Soldat. My Oracle sql is rusty but wouldn't

WHEN CV.DATEEFF > E.COMPLETIONDATE THEN 'RETURN HIGHEST DATE OF TWO'

be simply

WHEN CV.DATEEFF > E.COMPLETIONDATE THEN CV.DATEEFF

[pbaldy]

Well, you'd need an Else to return the other value, which means a nested Case statement for the dates.

[Minty]

Hmmm bit like the answer here ?
https://www.accessforums.net/showthread.php?t=73072

Duplicate question I think??

[pbaldy]

Yup. You need the Case in yours I think.

[Minty]

Agreed - edited suitably !

[June7]

IIf(1=1, 10/2) tested in query and no error.
Same for IIf(1=2, 10/2)

Both will fail in Access/VBA.

[CJ_London]

OK, OK I stand corrected