def to_bq(self, schema, not_null=False, boolean=False):
value = self.value.to_bq(schema, not_null=True)[0].sql.s
start = self.start.to_bq(schema, not_null=True)[0].sql.n
if self.length is NULL:
sql = "SUBSTR" + sql_iso(sql_list([value, start]))
else:
length = self.length.to_bq(schema, not_null=True)[0].sql.n
sql = "SUBSTR" + sql_iso(sql_list([value, start, length]))
return wrap([{"name": ".", "sql": sql}])
def to_bq(self, schema, not_null=False, boolean=False):
value = self.value.to_bq(schema, not_null=True)[0].sql.s
find = self.find.to_bq(schema, not_null=True)[0].sql.s
return wrap(
[{"name": ".", "sql": {"n": "INSTR" + sql_iso(sql_list([value, find]))}}]
)
def to_bq(self, schema, not_null=False, boolean=False):
value = BQLang[self.value].partial_eval().to_bq(schema)[0].sql.s
find = BQLang[self.find].partial_eval().to_bq(schema)[0].sql.s
start = BQLang[self.start].partial_eval().to_bq(schema)[0].sql.n
default = coalesce(
BQLang[self.default].partial_eval().to_bq(schema)[0].sql.n, SQL_NULL
)
if start.sql != SQL_ZERO.sql.strip():
value = NotRightOp([self.value, self.start]).to_bq(schema)[0].sql.s
index = "INSTR" + sql_iso(sql_list([value, find]))
sql = (
SQL_CASE
+ SQL_WHEN
+ index
+ SQL_THEN
+ index
+ SQL("-1+")
+ start
+ SQL_ELSE
+ default
+ SQL_END
)
return wrap([{"name": ".", "sql": {"n": sql}}])
def to_bq(self, schema, not_null=False, boolean=False):
terms = [
BQLang[t].partial_eval().to_bq(schema)[0].sql.n for t in self.terms
]
return wrap([{
"name": ".",
"sql": {
"n": "min" + sql_iso((sql_list(terms)))
}
}])
def to_bq(self, schema, not_null=False, boolean=False):
default = self.default.to_bq(schema)
if len(self.terms) == 0:
return default
default = coalesce(default[0].sql.s, SQL_NULL)
sep = BQLang[self.separator].to_bq(schema)[0].sql.s
acc = []
for t in self.terms:
t = BQLang[t]
missing = t.missing().partial_eval()
term = t.to_bq(schema, not_null=True)[0].sql
if term.s:
term_sql = term.s
elif term.n:
term_sql = "cast(" + term.n + " as text)"
else:
term_sql = (SQL_CASE + SQL_WHEN + term.b + SQL_THEN +
quote_value("true") + SQL_ELSE +
quote_value("false") + SQL_END)
if isinstance(missing, TrueOp):
acc.append(SQL_EMPTY_STRING)
elif missing:
acc.append(
SQL_CASE + SQL_WHEN +
sql_iso(missing.to_bq(schema, boolean=True)[0].sql.b) +
SQL_THEN + SQL_EMPTY_STRING + SQL_ELSE +
sql_iso(sql_concat_text([sep, term_sql])) + SQL_END)
else:
acc.append(sql_concat_text([sep, term_sql]))
expr_ = "SUBSTR" + sql_iso(
sql_list([
sql_concat_text(acc),
LengthOp(self.separator).to_bq(schema)[0].sql.n + SQL("+1"),
]))
return BQLScript(
expr=expr_,
data_type=STRING,
frum=self,
miss=self.missing(),
many=False,
schema=schema,
)