Home » RADICORE development » Transaction Patterns » Output2 - SQL
| Output2 - SQL [message #7334] |
Wed, 21 August 2019 17:11  |
htManager
Messages: 433
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
|
|
|
|
 |
|
Output2 - SQL
|
|
|
Re: Output2 - SQL
By: AJM on Thu, 22 August 2019 04:07 |
|
|
Re: Output2 - SQL
|
|
|
Re: Output2 - SQL
By: AJM on Sat, 24 August 2019 04:27 |
|
|
Re: Output2 - SQL
|
|
|
Re: Output2 - SQL
By: AJM on Mon, 26 August 2019 04:42 |
|
|
Re: Output2 - SQL
|
|
|
Re: Output2 - SQL
By: AJM on Tue, 27 August 2019 04:46 |
|
|
Re: Output2 - SQL
|
|
|
Re: Output2 - SQL
By: AJM on Wed, 28 August 2019 05:15 |
|
|
Re: Output2 - SQL
|
|
|
Re: Output2 - SQL
By: AJM on Thu, 29 August 2019 03:49 |
|
|
Re: Output2 - SQL
|
|
|
Re: Output2 - SQL
By: AJM on Sat, 31 August 2019 04:41 |
|
|
Re: Output2 - SQL
By: htManager on Tue, 03 September 2019 06:15 |
|
|
Re: Output2 - SQL
By: AJM on Wed, 04 September 2019 04:41 |
|
|
Re: Output2 - SQL
By: htManager on Wed, 04 September 2019 06:25 |
|
|
Re: Output2 - SQL
By: AJM on Thu, 05 September 2019 04:14 |
Goto Forum:
Current Time: Fri Nov 22 18:46:26 EST 2024
Total time taken to generate the page: 0.01098 seconds
|
Radicore Forum
Search
Help
Members
Register
Login
Home
