nuBuilder Forum

Posted: **Thu Aug 01, 2013 8:50 am**

Hi,

I'm using this SQL in general tab of my form

select  bib_count, bib_titel, bib_jaar, bib_id, '1' as actief from bibliotheek  where bib_id not in (
SELECT distinct(res_artikel) from reservaties left join registratie on res_reg_id = reg_id where res_in is null and ( (STR_TO_DATE('#reg_from#', '%d-%m-%Y') between reg_from and reg_to )OR ( STR_TO_DATE('#reg_to#', '%d-%m-%Y') between reg_from and reg_to) or ( reg_from between STR_TO_DATE('#reg_from#', '%d-%m-%Y') and STR_TO_DATE('#reg_to#', '%d-%m-%Y') )) ) 

union all

select  bib_count, bib_titel, bib_jaar, bib_id, '0' as actief from bibliotheek where bib_id in (
SELECT distinct(res_artikel) from reservaties left join registratie on res_reg_id = reg_id where res_in is null and ( (STR_TO_DATE('#reg_from#', '%d-%m-%Y') between reg_from and reg_to )OR ( STR_TO_DATE('#reg_to#', '%d-%m-%Y') between reg_from and reg_to) or ( reg_from between STR_TO_DATE('#reg_from#', '%d-%m-%Y') and STR_TO_DATE('#reg_to#', '%d-%m-%Y') )) ) order by bib_count

This works fine when I try the same SQl in PhpMyadmin.

When I open the form I get this error :

Code: Select all

SELECT bib_count, bib_titel, bib_jaar, bib_id from bibliotheek where bib_id not in ( SELECT distinct(res_artikel) from reservaties left join registratie on res_reg_id = reg_id where res_in is null and ( (STR_TO_DATE('23-07-2013', '%d-%m-%Y') between reg_from and reg_to )OR ( STR_TO_DATE('20-08-2013', '%d-%m-%Y') between reg_from and reg_to) or ( reg_from between STR_TO_DATE('23-07-2013', '%d-%m-%Y') and STR_TO_DATE('20-08-2013', '%d-%m-%Y') )) )
 union all 
 select bib_count, bib_titel, bib_jaar, bib_id, '0' as actief from bibliotheek where bib_id in ( SELECT distinct(res_artikel) from reservaties left join registratie on res_reg_id = reg_id where res_in is null and ( (STR_TO_DATE('23-07-2013', '%d-%m-%Y') between reg_from and reg_to )OR ( STR_TO_DATE('20-08-2013', '%d-%m-%Y') between reg_from and reg_to) or ( reg_from between STR_TO_DATE('23-07-2013', '%d-%m-%Y') and STR_TO_DATE('20-08-2013', '%d-%m-%Y') )) ) order by bib_count

As you can see '1' as actief is missing in first part of SQL.
How can I solve this?
Johan

Posted: **Thu Aug 01, 2013 11:44 pm**

Johan, you are right: it's seems nuBuilder don't allow quoted column alias in SQL.

You could run your query using Custom Code > Before Browse:

Code: Select all

$sql = "CREATE TABLE #browseTable# 
  select  bib_count, bib_titel, bib_jaar, bib_id, '1' as actief from ...";
nuRunQuery($sql);

Max

Posted: **Fri Aug 02, 2013 8:21 am**

Max,

Thanks this works fine.

Johan

Posted: **Fri Aug 02, 2013 2:20 pm**

nuBuilder Forum

union all in sql

union all in sql

Re: union all in sql

Re: union all in sql

Re: union all in sql