My query not supported to the system

Hi, I need some help for my query. Below is my query.

CREATE VIEW STUD1 AS
SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY R.ContractID
ORDER BY CONVERT(DATE, LEFT(EVAL_PERIOD , charindex('-', EVAL_PERIOD) - 1), 103) DESC)
FROM SCMD3.DBO.CPE R;

GO

CREATE VIEW STUD2 AS
SELECT C.ContractID,
GRED1 = MAX(CASE WHEN RN = 2 THEN ALL_GRADE END),
MARK1 = MAX(CASE WHEN RN = 2 THEN ALL_SCORE END),
GRED2 = MAX(CASE WHEN RN = 1 THEN ALL_GRADE END),
MARK2 = MAX(CASE WHEN RN = 1 THEN ALL_SCORE END)
FROM STUD1 C
GROUP BY C.ContractID;

GO
SELECT s.ContractTitle, x.GRED1, x.MARK1, x.GRED2, x.MARK2 FROM STUD2 x INNER JOIN SCMD3.DBO.CONTRACTMASTER s ON x.ContractID = s.ID;

Above is my current query and need to run on my db but the application I used cannot use view function. Is there any other way that I can get the same result? Help would be greatly appreciated

CTE?

I've just cut & pasted your code, NOT tested it :slight_smile:, but it would be something like this:

;WITH STUD1 AS
(
SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY R.ContractID
ORDER BY CONVERT(DATE, LEFT(EVAL_PERIOD , charindex('-', EVAL_PERIOD) - 1), 103) DESC)
FROM SCMD3.DBO.CPE R
),
STUD2 AS
(
SELECT C.ContractID,
GRED1 = MAX(CASE WHEN RN = 2 THEN ALL_GRADE END),
MARK1 = MAX(CASE WHEN RN = 2 THEN ALL_SCORE END),
GRED2 = MAX(CASE WHEN RN = 1 THEN ALL_GRADE END),
MARK2 = MAX(CASE WHEN RN = 1 THEN ALL_SCORE END)
FROM STUD1 C
GROUP BY C.ContractID
)
SELECT s.ContractTitle, x.GRED1, x.MARK1, x.GRED2, x.MARK2 
FROM STUD2 x 
INNER JOIN SCMD3.DBO.CONTRACTMASTER s ON x.ContractID = s.ID

Thank you for the solution..everything like OK except for Conversion failed when converting date and/or time from character string error I found since this query created.

You have an invalid date in this expression:

CONVERT(DATE, LEFT(EVAL_PERIOD , charindex('-', EVAL_PERIOD) - 1), 103)

it is always very dangerous storing a date in a text field ... bound to be illegal dates entered if they are not validated and stored in a datatype column that enforces validity.

You may also find that your CHARINDEX() won't find a "-" and that will give you an error to (in effect your will then do a LEFT "-1")

You could try:

SET DATEFORMAT dmy
SELECT R.ContractID, LEFT(EVAL_PERIOD , charindex('-', EVAL_PERIOD) - 1)
FROM SCMD3.DBO.CPE R
WHERE ISDate(LEFT(EVAL_PERIOD , charindex('-', EVAL_PERIOD) - 1) = 0

but it would be better to create a new DATE column in the table and Parse the EVAL_PERIOD data into it. You could use a TRIGGER, to populate the new date column (in future) and that could RAISERROR if an invalid date was encountered.

Where should I apply query that you ask to try above?

Any SQL query tool will do e.g. SSMS (SQL Server Management Studio)

I mean this code

SET DATEFORMAT dmy
SELECT R.ContractID, LEFT(EVAL_PERIOD , charindex('-', EVAL_PERIOD) - 1)
FROM SCMD3.DBO.CPE R
WHERE ISDate(LEFT(EVAL_PERIOD , charindex('-', EVAL_PERIOD) - 1) = 0

merge together with the previous code or run concurrently?

its a standalone query to help you find invalid dates

You need to find & fix all invalid dates BEFORE you can then use your original query to "process" them into DATE datatype.