
### In Unterformular mit SQL-Befehl ###

SELECT 
       * 
FROM 
       "Abfrage_Schule1" 

WHERE
     ((UPPER("SName")    LIKE '%' || UPPER (:Var_SName) || '%' ) OR (:Var_SName  IS NULL))
	and
     ((UPPER("SWohnort")    LIKE '%' || UPPER (:Var_SWohnort) || '%' ) OR (:Var_SWohnort  IS NULL))
	and 
     ((UPPER("Klassennummer")    LIKE '%' || UPPER (:Var_Klassennummer) || '%' ) OR (:Var_Klassennummer  IS NULL))
	 and 
     ((UPPER("LName")    LIKE '%' || UPPER (:Var_LName) || '%' ) OR (:Var_LName  IS NULL))
	 and 
     ((UPPER("Fach")    LIKE '%' || UPPER (:Var_Fach) || '%' ) OR (:Var_Fach  IS NULL))
	 and 
     ((UPPER("Transportmittel")    LIKE '%' || UPPER (:Var_Transportmittel) || '%' ) OR (:Var_Transportmittel  IS NULL))
	 
	 
### Ohne Unterformular mit IS NULL ###

SELECT 
	"Schueler"."SName", 
	"Schueler"."SWohnort", 
	"Klasse"."Klassennummer", 
	"Lehrer"."LName", 
	"Fach"."Fach", 
	"Transportmittel"."Transportmittel" 
FROM 
	"Fach", 
	"Filter", 
	"Klasse", 
	"Lehrer", 
	"Schueler", 
	"Transportmittel" 
WHERE 
	 ( UPPER ( "SName" ) LIKE '%' || UPPER ( "Filter"."Filter_SName" ) || '%' OR "Filter"."Filter_SName" IS NULL )
	AND   
	( UPPER ( "SWohnort" ) LIKE '%' || UPPER ( "Filter"."Filter_SWohnort" ) || '%' OR "Filter"."Filter_SWohnort" IS NULL )
	AND   
	( UPPER ( "Klassennummer" ) LIKE '%' || UPPER ( "Filter"."Filter_Klassennummer" ) || '%' OR "Filter"."Filter_Klassennummer" IS NULL )
	AND   
	( UPPER ( "LName" ) LIKE '%' || UPPER ( "Filter"."Filter_LName" ) || '%' OR "Filter"."Filter_LName" IS NULL )
	AND   
	( UPPER ( "Fach" ) LIKE '%' || UPPER ( "Filter"."Filter_Fach" ) || '%' OR "Filter"."Filter_Fach" IS NULL )
	AND   
	( UPPER ( "Transportmittel" ) LIKE '%' || UPPER ( "Filter"."Filter_Transportmittel" ) || '%' OR "Filter"."Filter_Transportmittel" IS NULL )


### Ohne Unterformular mit COALESCE ###

SELECT 
	"SName",  
	"SWohnort",
	"Klassennummer",
	"LName",
	"Fach",
	"Transportmittel"
FROM 
	"Schueler",
	"Klasse",
	"Lehrer",
	"Fach",
	"Transportmittel"
WHERE 
		UPPER ( "SName" ) LIKE COALESCE ( '%' || UPPER ( ( SELECT "Filter_SName" FROM "Filter" WHERE "ID_Filter" = TRUE ) ) || '%', UPPER ( "SName" ) )
	AND
		UPPER ( "SWohnort" ) LIKE COALESCE ( '%' || UPPER ( ( SELECT "Filter_SWohnort" FROM "Filter" WHERE "ID_Filter" = TRUE ) ) || '%', UPPER ( "SWohnort" ) )
	AND
		UPPER ( "Klassennummer" ) LIKE COALESCE ( '%' || UPPER ( ( SELECT "Filter_Klassennummer" FROM "Filter" WHERE "ID_Filter" = TRUE ) ) || '%', UPPER ( "Klassennummer" ) )
	AND
		UPPER ( "LName" ) LIKE COALESCE ( '%' || UPPER ( ( SELECT "Filter_LName" FROM "Filter" WHERE "ID_Filter" = TRUE ) ) || '%', UPPER ( "LName" ) )
	AND
		UPPER ( "Fach" ) LIKE COALESCE ( '%' || UPPER ( ( SELECT "Filter_Fach" FROM "Filter" WHERE "ID_Filter" = TRUE ) ) || '%', UPPER ( "Fach" ) )
	AND
		UPPER ( "Transportmittel" ) LIKE COALESCE ( '%' || UPPER ( ( SELECT "Filter_Transportmittel" FROM "Filter" WHERE "ID_Filter" = TRUE ) ) || '%', UPPER ( "Transportmittel" ) )


### Weitere Möglichkeit ###


SELECT "Schueler"."SName", COALESCE ( "Schueler"."SName", 'Leer' ) AS "T" FROM "Schueler" WHERE "T" = COALESCE ( ( SELECT "Filter_SName" FROM "Filter" ), "T" )	 
	 
SELECT "Schueler"."SName",UPPER(COALESCE ( "Schueler"."SName", 'Leer' )) AS "T" FROM "Schueler" WHERE "T" = UPPER(COALESCE ( ( SELECT "Filter_SName" FROM "Filter" ), "T" ))




	 
	 
	 