Welcome to the nuBuilder forums!

Please register and login to view forums and other content only available to registered users.

union all in sql

Locked
johan
Posts: 392
Joined: Sun Feb 27, 2011 11:16 am
Location: Belgium

union all in sql

Unread post 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
massiws
Posts: 503
Joined: Thu May 24, 2012 2:08 am
Location: Milan, Italy
Contact:

Re: union all in sql

Unread post 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
johan
Posts: 392
Joined: Sun Feb 27, 2011 11:16 am
Location: Belgium

Re: union all in sql

Unread post by johan »

Max,

Thanks this works fine.

Johan
massiws
Posts: 503
Joined: Thu May 24, 2012 2:08 am
Location: Milan, Italy
Contact:

Re: union all in sql

Unread post by massiws »

.
Locked