MS SQL 2008 R2 Queryanalyzer successful run in the code. Failed SQL code field within the LOGO application is running

Question

MS SQL 2008 R2 Queryanalyzer successful run in the code. Failed SQL code field within the LOGO application is running.

Error :

" The order by clause is invalid in views, inline functions,derived tables,subqueries and common table expressions,unless TOP or FOR XML is also specified"

My Code :

printf("goodbye world!");  /* his suicide note
                              was in C */

SELECT PLN.[DATE_] AS 'TARİH',
          CC.DEFINITION_ AS 'Cari Kodu',
          TANA.DONEM AS 'MNL_Dönem',
          CASE 
        WHEN TANA.Oda_Tipleri='1' THEN 'Tek Kişilik'
        WHEN TANA.Oda_Tipleri='2' THEN '2 kişilik'
        WHEN TANA.Oda_Tipleri='3' THEN '3 kişilik'
        WHEN TANA.Oda_Tipleri='4' THEN '4 kişilik'
        WHEN TANA.Oda_Tipleri='5' THEN '2 kişilik odada TEK kişi'
        WHEN TANA.Oda_Tipleri='6' THEN '3 kişilik odada 2 kişi'
    ELSE 'SEÇİLMEMİŞ'
 END 'Oda_Tipi',
CASE        WHEN TANA.Oda_Ozellik= '1'      THEN 'Dışa Bakan Oda ' 
        WHEN TANA.Oda_Ozellik= '2'  THEN 'Kurangleze Bakan Oda ' 
        WHEN TANA.Oda_Ozellik= '3'  THEN 'İçe Bakan Oda ' 
        WHEN TANA.Oda_Ozellik= '4'  THEN 'Arkaya Bakan Oda '
        WHEN TANA.Oda_Ozellik= '5'  THEN 'Öne Bakan Oda '
    ELSE 'SEÇİLMEMİŞ'
END Oda_Ozellik,

CASE
        WHEN TANA.Sene_Bilgisi = '1' THEN '2011/2012 ' 
        WHEN TANA.Sene_Bilgisi = '2' THEN '2012/2013 ' 
        WHEN TANA.Sene_Bilgisi = '3' THEN '2013/2014 ' 
        WHEN TANA.Sene_Bilgisi = '4' THEN '2014/2015 ' 
        WHEN TANA.Sene_Bilgisi = '5' THEN '2015/2016 ' 
        WHEN TANA.Sene_Bilgisi = '6' THEN '2016/2017 ' 
        WHEN TANA.Sene_Bilgisi = '7' THEN '2017/2018 ' 
        WHEN TANA.Sene_Bilgisi = '8' THEN '2018/2019 ' 
        WHEN TANA.Sene_Bilgisi = '9' THEN '2019/2020 ' 
    ELSE 'SEÇİLMEMİŞ'
END Sene_Bilgisi,
CASE 
        WHEN TANA.Donem_Bilgisi = '1' THEN 'Güz ' 
        WHEN TANA.Donem_Bilgisi = '2' THEN 'Bahar '
        WHEN TANA.Donem_Bilgisi = '3' THEN 'Yaz '  
    ELSE 'SEÇİLMEMİŞ'
END Donem_Bilgisi,

CASE 
        WHEN TANA.Burs_Tipi = '1' THEN 'YÖK' 
        WHEN TANA.Burs_Tipi = '2' THEN '%25 Yönmetim Bursu' 
        WHEN TANA.Burs_Tipi = '3' THEN '%50 Yönmetim Bursu' 
        WHEN TANA.Burs_Tipi = '4' THEN '%75 Yönmetim Bursu' 
        WHEN TANA.Burs_Tipi = '5' THEN '%100 Yönmetim Bursu' 
        WHEN TANA.Burs_Tipi = '6' THEN '%50 Yurt Kredisi' 
        WHEN TANA.Burs_Tipi = '7' THEN '%100 Yurt Kredisi' 
        WHEN TANA.Burs_Tipi = '8' THEN '%100 Yönetim Burdu Mezuniyete Kadar' 
        WHEN TANA.Burs_Tipi = '9' THEN '%50 Burs %50 Yurt Kredisi' 
    ELSE 'Burssuz veya Seçilmemiş'
END Cinsiyet_Tipi,  

CASE 
        WHEN TANA.Cinsiyet_Tipi = '1' THEN 'Erkek ' 
        WHEN TANA.Cinsiyet_Tipi = '2' THEN 'Kız ' 
    ELSE 'SEÇİLMEMİŞ'
END Cinsiyet_Tipi,
CASE 
        WHEN TANA.Uyruk= '0' THEN 'TC ' 
        WHEN TANA.Uyruk= '1' THEN 'Diğer ' 
        ELSE 'SEÇİLMEMİŞ'
END Uyruk,

PLN.[FICHEREF] As 'Sipariş Referansı',
SIP.SPECODE As 'Sip.ÖK',
SIP.FICHENO As 'Sip.No',
SIP.DOCODE As 'Sip.Belge No',
CASE WHEN (FAT.[FICHENO] IS NULL) THEN '0' WHEN (FAT.[FICHENO] IS NOT NULL) THEN FAT.[FICHENO] END AS 'Fatura No',
PLN.[TRCODE],
PLN.[TOTAL] AS 'PLN Taksit Tutarı',
CASE WHEN (FAT.[NETTOTAL] IS NULL) THEN '0' WHEN (FAT.[NETTOTAL] IS NOT NULL) THEN FAT.[NETTOTAL] END AS 'Kesilen.Fat.Tut',
CASE WHEN (PLN.[TOTAL]- FAT.[NETTOTAL] IS NOT NULL) THEN '0' WHEN (PLN.[TOTAL]- FAT.[NETTOTAL] IS NULL) THEN 'Faturalanmamış' END AS 'FARK',
CASE WHEN (FAT.[TOTALVAT] IS NULL) THEN '0' WHEN (FAT.[TOTALVAT] IS NOT NULL) THEN FAT.[TOTALVAT] END AS 'Kesilen KDV',
CASE WHEN (FAT.[TOTALVAT] IS NOT NULL) THEN '0' WHEN (FAT.[TOTALVAT]IS NULL) THEN ROUND((PLN.[TOTAL]-(PLN.[TOTAL]/1.08)),2) END AS 'KesileCEK KDV',
PLN.[CANCELLED] As 'İPTAL Durumu',
PLN.[MODIFIED],
PLN.[PAYNO] AS 'Öd.Pln.Satırı',
CASE WHEN (FAT.[TRACKNR] IS NULL) THEN 'Faturalanmamış' WHEN (FAT.[TRACKNR] IS NOT NULL) THEN FAT.[TRACKNR] END AS 'FAT.SIRA.NO'
FROM LG_011_01_PAYTRANS AS PLN 
LEFT OUTER JOIN LG_011_CLCARD CC ON
CC.LOGICALREF = PLN.CARDREF LEFT OUTER JOIN
LG_011_01_INVOICE AS FAT ON  PLN.FICHEREF=FAT.DOCTRACKINGNR AND FAT.TRACKNR=PLN.PAYNO     LEFT OUTER JOIN
LG_011_01_ORFICHE AS SIP ON  PLN.FICHEREF=SIP.LOGICALREF INNER JOIN
LG_XT1015_011 AS TANA ON CC.LOGICALREF=TANA.PARLOGREF
Where MODULENR=3 
ORDER BY  PLN.[DATE_] ASC

Also, any application for a report, t-sql code that runs smoothly, queryanalzer. If you stack overflow error can be identified right from the stack, then it might be possible that convert data? How is it? You can find a document that explains step by step on video?

Error Detail:
cpu registers: eax = 0d3e2f60 ebx = 0d40ef98 ecx = 00000000 edx = 0042967e esi = 00000019 edi = 0012fc24 eip = 0042967e esp = 0012efa8 ebp = 0012f004

Error Detail:cpu registers: eax = 0d3e2f60 ebx = 0d40ef98 ecx = 00000000 edx = 0042967e esi = 00000019 edi = 0012fc24 eip = 0042967e esp = 0012efa8 ebp = 0012f004 — Ersoy AYDIN, Aug 23 '12 at 09:38

score 0 · Answer 1 · answered Aug 23 '12 at 11:11

0

If this is the error message - "The order by clause is invalid in views, inline functions,derived tables, sub-queries and common table expressions,unless TOP or FOR XML is also specified", you should enter "TOP 100 PERCENT" in the clause of the statement where "order" is used.

In your case I suppose it will be:

SELECT    TOP 100 PERCENT
          PLN.[DATE_] AS 'TARIH',
          CC.DEFINITION_ AS 'Cari Kodu',
          TANA.DONEM AS 'MNL_Dönem',
          CASE 
          ...

answered Aug 23 '12 at 11:11

gotqn

42,737
46
157
243

First of all, thank you very much for the reply you gave. But, as mentioned before, the Queryanalyzer code smoothly. Designed with Delphi LOGO (www.logo.com.tr) program, I get an error report that uses T-SQL architecture. – Ersoy AYDIN Aug 23 '12 at 11:51
If you should have the following block of code, the actual code block, delete tasks. I am not getting an error at that time. Code Blog : – Ersoy AYDIN Aug 23 '12 at 11:59
If you should have the following block of code, the actual code block, delete tasks. I am not getting an error at that time. CASE WHEN TANA.Oda_Tipleri='1' THEN 'Tek Kişilik' WHEN TANA.Oda_Tipleri='2' THEN '2 kişilik' WHEN TANA.Oda_Tipleri='3' THEN '3 kişilik' . . . . CASE WHEN TANA.Uyruk= '0' THEN 'TC ' WHEN TANA.Uyruk= '1' THEN 'Diğer ' ELSE 'SEÇİLMEMİŞ' END Uyruk, – Ersoy AYDIN Aug 23 '12 at 12:07
I am not quite sure what you are doing. The SQL statement that you have could be right if it is checked alone. If it is part of a statement that is executed in the context of a view, inline function, etc. it will be wrong because no "ORDER" clause is allowed in them. The only way to fix this is to add TOP or FOR XML clause. If you want to get all the records (as I suppose) you just add "TOP 100 PERCENT". Could you try this and tell if it helps? – gotqn Aug 23 '12 at 12:08
Have tested the method you say. However, results failed. The reason why there was this sharing, others experience with T-SQL code to revise. – Ersoy AYDIN Aug 23 '12 at 12:23
I found the cause of the problem is real, each CASE block, there is no problem if the code separately. However, I get an error immediately be CASE blocks. Now, the question is; "CASE blocks how to merge"? – Ersoy AYDIN Aug 23 '12 at 12:57
You can try to convert: CASE WHEN TANA.Donem_Bilgisi = '1' THEN 'Güz ' WHEN TANA.Donem_Bilgisi = '2' THEN 'Bahar ' WHEN TANA.Donem_Bilgisi = '3' THEN 'Yaz ' ELSE 'SEÇİLMEMİŞ' as CASE Donem_Bilgisi WHEN '1' THEN 'Güz ' WHEN '2' THEN 'Bahar ' WHEN '3' THEN 'Yaz ' ELSE 'SEÇILMEMIS' END – gotqn Aug 23 '12 at 13:03

MS SQL 2008 R2 Queryanalyzer successful run in the code. Failed SQL code field within the LOGO application is running

1 Answers1