Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » RADICORE development » Transaction Patterns » Output2 - SQL
Output2 - SQL [message #7334] Wed, 21 August 2019 17:11 Go to previous message
htManager is currently offline  htManager
Messages: 220
Registered: May 2014
Senior Member
I have an output2 pattern and I want to list only records of the chosen value in field 'saison_id'. I searched the net to look for the right SQL solution but I failed. Can you say why my SQL code works with a 'hard' code (2018/2019) and not with the one (training_plan_kopf.saison_id) you see below?

Best regards,

Juergen

$table_id = 'training_plan_anwesenheit'; // table name
$report = 'training_plan_anwesenheit.gesamt.list.report.inc'; // file identifying report structure

// customise the SQL SELECT statement
$sql_select = "tplan_anwesenheit_kontakt_name, tplan_anwesenheit_kontakt_vorname, tplan_anwesenheit_status_grund, tplan_anwesenheit_saison_id, " .
"orga_vereine.vereine_name, orga_vereine.vereine_logo, orga_mannschaften_eigene.mannschaften_desc, " .

"SUM(CASE WHEN tplan_anwesenheit_status = 1 THEN tplan_anwesenheit_status ELSE 0 END) AS anzahl_anwesenheit, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND training_plan_kopf.training_datum > NOW() THEN 1 ELSE 0 END) AS anzahl_absagen_zukunft, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'URL' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_url, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'PRV' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_prv, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'BER' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_ber, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'VLZ' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_vlz, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'KRH' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_krh, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'SO' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_so, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'TAM' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_tam, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'SCH' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_sch, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'UNI' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_uni, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'NIK' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_nik, " .
"SUM(CASE WHEN tplan_anwesenheit_status = 0 AND tplan_anwesenheit_status_grund = 'UE' AND training_plan_kopf.training_datum < NOW()+1 THEN 1 ELSE 0 END) AS anzahl_absagen_ue";

$sql_from = 'training_plan_anwesenheit ' .
'LEFT JOIN orga_vereine ON (' .
'orga_vereine.verbaende_art_id = training_plan_anwesenheit.tplan_anwesenheit_verbaende_art_id AND ' .
'orga_vereine.verbaende_kuerzel = training_plan_anwesenheit.tplan_anwesenheit_verbaende_kuerze l AND ' .
'orga_vereine.vereine_kuerzel = training_plan_anwesenheit.tplan_anwesenheit_vereine_kuerzel) ' .

'LEFT JOIN orga_mannschaften_eigene ON (' .
'orga_mannschaften_eigene.verbaende_art_id = training_plan_anwesenheit.tplan_anwesenheit_verbaende_art_id AND ' .
'orga_mannschaften_eigene.verbaende_kuerzel = training_plan_anwesenheit.tplan_anwesenheit_verbaende_kuerze l AND ' .
'orga_mannschaften_eigene.vereine_kuerzel = training_plan_anwesenheit.tplan_anwesenheit_vereine_kuerzel AND ' .
'orga_mannschaften_eigene.saison_id = training_plan_anwesenheit.tplan_anwesenheit_saison_id AND ' .
'orga_mannschaften_eigene.mannschaften_id = training_plan_anwesenheit.tplan_anwesenheit_mannschaften_id) ' .

'LEFT JOIN training_plan_kopf ON (' .
'training_plan_kopf.verbaende_art_id = training_plan_anwesenheit.tplan_anwesenheit_verbaende_art_id AND ' .
'training_plan_kopf.verbaende_kuerzel = training_plan_anwesenheit.tplan_anwesenheit_verbaende_kuerze l AND ' .
'training_plan_kopf.vereine_kuerzel = training_plan_anwesenheit.tplan_anwesenheit_vereine_kuerzel AND ' .
'training_plan_kopf.saison_id = training_plan_anwesenheit.tplan_anwesenheit_saison_id AND ' .
'training_plan_kopf.mannschaften_id = training_plan_anwesenheit.tplan_anwesenheit_mannschaften_id AND '.
'training_plan_kopf.training_plan_kopf_no = training_plan_anwesenheit.tplan_anwesenheit_kopf_no)';

$sql_where = "training_plan_anwesenheit.tplan_anwesenheit_saison_id = training_plan_kopf.saison_id";
-> this code doesn't work.

$sql_where = "training_plan_anwesenheit.tplan_anwesenheit_saison_id = '2018/2019'";
-> this code works. Only records with value 2018/2019 are shown

$sql_groupby = 'tplan_anwesenheit_kontakt_name, tplan_anwesenheit_kontakt_vorname';
$sql_having = null;
$sql_orderby = null;

require 'std.output2.inc'; // activate page controller
 
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: add5 - Data Area
Next Topic: Output2 - Options / 90° cell rotation
Goto Forum:
  


Current Time: Mon Nov 18 10:24:05 EST 2019

Total time taken to generate the page: 0.01409 seconds