union all in sql

union all in sql

Postby johan » Thu Aug 01, 2013 4:20 pm

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
johan
 
Posts: 268
Joined: Sun Feb 27, 2011 7:46 pm
Location: Belgium

Re: union all in sql

Postby massiws » Fri Aug 02, 2013 7:14 am

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
massiws
 
Posts: 503
Joined: Thu May 24, 2012 9:38 am
Location: Milan, Italy

Re: union all in sql

Postby johan » Fri Aug 02, 2013 3:51 pm

Max,

Thanks this works fine.

Johan
johan
 
Posts: 268
Joined: Sun Feb 27, 2011 7:46 pm
Location: Belgium

Re: union all in sql

Postby massiws » Fri Aug 02, 2013 9:50 pm

.
massiws
 
Posts: 503
Joined: Thu May 24, 2012 9:38 am
Location: Milan, Italy


Return to Custom Code



cron