Page 1 of 1

union all in sql

PostPosted: Thu Aug 01, 2013 4:20 pm
by johan
Hi,

I'm using this SQL in general tab of my form
Code: Select all
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

Re: union all in sql

PostPosted: Fri Aug 02, 2013 7:14 am
by massiws
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

Re: union all in sql

PostPosted: Fri Aug 02, 2013 3:51 pm
by johan
Max,

Thanks this works fine.

Johan

Re: union all in sql

PostPosted: Fri Aug 02, 2013 9:50 pm
by massiws
.