SELECT NULL AS

[hiFloh]

how do i select null as something?

I have the following Tables in a mariadb.

CREATE TABLE pos (
  id INT,
  ts DATETIME,
  lon DOUBLE,
  lat DOUBLE
);

CREATE TABLE spec (
  id INT,
  ts DATETIME,
  type  enum('sensor1','sensor2')
  content INT
);

Now i want to use the following SQL statement.

SELECT ts, lon, lat, NULL AS leads FROM pos WHERE id = :id
UNION ALL
SELECT ts, NULL, NULL , content FROM spec WHERE id = :id AND type='sensor1'
GROUP BY ts ORDER BY ts;

my current solution compiles, but i would like to use std::optional to check whether a value existed at the specific time

SQLPP_ALIAS_PROVIDER(sensor1);
SQLPP_ALIAS_PROVIDER(timestamp);
(int id){

auto position=select(pos.ts.as(timestamp),pos.lon,pos.lat,sqlpp::value(NULL).as(senor1)).where(pos.id==id);
auto sensor1=select(spec.ts.as(timestamp),sqlpp::value(0.0).as(pos.lon),sqlpp::value(0.0).as(pos.lat),spec.content.as(sensor1).where((spec.id==id)&&spec.type=="sensor1"))
db(position.union_all(sensor1));
}

[rbock]

Can't test right now, but here is what I think at first glance:

NULL in C++ is 0. So you are selecting the value 0 AS senor1.

In the library, SQL NULL is represented by std::nullopt. In this case you need to ensure that it is an integral value, so you do sqlpp::value(std::optional<int>{}).as(senor1).

Fingers crossed :-)

[MeanSquaredError]

What @rbock suggested works just fine. Here is my simple test program:

#include <sqlpp23/sqlpp23.h>
#include <sqlpp23/postgresql/postgresql.h>

#include <iostream>

int main()
{
	auto config = sqlpp::postgresql::connection_config {};
	config.user = "postgres";
	config.dbname = "template1";
	auto dbc = sqlpp::postgresql::connection {config};
	for (const auto& row : dbc (
		sqlpp::select (sqlpp::value (std::optional<int> {5}).as (sqlpp::alias::a))
	)) {
		std::cout << (row.a ? std::to_string (*row.a) : "NULL") << "\n";
	}
}

It outputs 5. Replacing std::optional<int> {5} with std::optional<int> {} outputs NULL

[hiFloh]

Thank you,
it works. Haven´t thought of that.
Also I realized I was still using the headers for sqlpp11 =).

The last part of the sql statement does not work, but i created a new issue #83 , because its technically a different problem.