0

May I ask whats the problem with my SQL Server syntax, I'm using SQL Server 2008.

This my code :

SELECT 
    CASE 
        WHEN tbDiagnosaPasienRekamMedik.Kode_Diagnosa_Awal IS NULL 
            THEN tbDiagnosaPasienRekamMedik.Kode_Diagnosa_Akhir
        WHEN tbDiagnosaPasienRekamMedik.Kode_Diagnosa_Akhir IS NULL 
            THEN tbDiagnosaPasienRekamMedik.Kode_Diagnosa_Awal
        WHEN tbDiagnosaPasienRekamMedik.Kode_Diagnosa_Awal IS NOT NULL 
             AND tbDiagnosaPasienRekamMedik.Kode_Diagnosa_Akhir IS NOT NULL 
            THEN tbDiagnosaPasienRekamMedik.Kode_Diagnosa_Akhir
        ELSE '-'
    END AS KodeDiag, 
    dbo.tbDiagnosaICD10.Diagnosa AS NAMA,
    COUNT(KodeDiag) AS JUMLAH
FROM
    dbo.tbDiagnosaICD10 
RIGHT OUTER JOIN
    dbo.tbDiagnosaPasienRekamMedik ON dbo.tbDiagnosaICD10.Kode_Diagnosa = KodeDiag 
WHERE 
    dbo.tbDiagnosaPasienRekamMedik.Tgl_Masuk BETWEEN '2022-06-01' AND '2022-08-31' 
GROUP BY 
    dbo.tbDiagnosaICD10.Diagnosa 
ORDER BY 
    JUMLAH DESC

I get this error from my dbclient:

Msg 207, Level 16, State 1, Server SIMRS-BARU, Procedure, Line 0
Invalid column name 'KodeDiag'.

Msg 207, Level 16, State 1, Server SIMRS-BARU, Procedure, Line 0
Invalid column name 'KodeDiag'

[42S22] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'KodeDiag'. (207)
[42S22] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'KodeDiag'. (207)

enter image description here

I hope someone can explain what my mistake and how to solve it.

Thank you

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Imronsam
  • 1
  • 1
  • 3
    You can't use a calculated column value in another column without using a sub-query. – Dale K Sep 07 '22 at 04:16
  • I'm newbie on it, did you mean I can use my select case when as on count function? – Imronsam Sep 07 '22 at 04:38
  • I highly recommend a tutorial which covers sub-queries that way you will gain a better understanding than if someone just writes the answer here. – Dale K Sep 07 '22 at 04:40
  • maybe can give me a clue or keywords for search on internet? Thankyou – Imronsam Sep 07 '22 at 04:45
  • I just did... sql sub query – Dale K Sep 07 '22 at 04:47
  • `ON dbo.tbDiagnosaICD10.Kode_Diagnosa = KodeDiag` You cannot use the alias here, either you write the entire case here again, or you put your query inside a sub query as Dale already suggested – GuidoG Sep 07 '22 at 06:18
  • And a subquery in the FROM clause is called a _derived table_. – jarlh Sep 07 '22 at 06:25

0 Answers0