AI Zone Admin Forum Add your forum

NEWS: Chatbots.org survey on 3000 US and UK consumers shows it is time for chatbot integration in customer service!read more..

Store null-variables’ values as null?
 
 

Hello!

I am back again with yet another problem.

Say, I somewhere I have a variable that is assigned the value null for a reason.

$var null 

When I want to use $var in a query and want it to be null specifically.

In other words, say I have defined the following queue in ChatScript:

select *

from table

where  
case when $var is not null then name else 'NULLVALUE' end 
 
coalesce($var 'NULLVALUE'

I want this to translate to:

select *

from table

where 
case when null is not null then name else 'NULLVALUE' end 
 
coalesce(null 'NULLVALUE'

But instead, ChatScript translates it to:

select *

from table

where 
case when is not null then name else 'NULLVALUE' end 
 
coalesce( , 'NULLVALUE'

Of course, this throws an error and I cannot continue.

In simpler terms:

select *

from table

where column 
$var 

ends up as this:

select *

from table

where column 

Instead of this

select *

from table

where column 
null 

I need things such as $var = null to be null (not an empty string) when called.

Is there any way I can achieve this?

 

 
  [ # 1 ]

as you are generating the select statement, you can do
u: ()  select * from table where column =  if (!$var) {null} else {$var}

 

 
  [ # 2 ]
Bruce Wilcox - May 21, 2019:

as you are generating the select statement, you can do
u: ()  select * from table where column =  if (!$var) {null} else {$var}

Problem: The SQL Query is inside a “string”. The if clause does not work because it is not compiled since it is inside a string

TEST:  Dbexecute failed list from tableFEHLER:  Syntaxfehler bei ┬╗{┬½
LINE 1
: ... where queried and case when if (!Damenshirt{null} is ... 

It recognizes $var but not the other stuff.

Image Attachments
see_output.PNG
 

 
  [ # 3 ]

show me your real code.

 

 
  [ # 4 ]

Okay, this should do.

Notice that $things (previously referred to as $var) is within ” “

t: ($searchenable) if (^dbexecute( ^"select distinct beschreibung
from pseudotabellepro
where queried = 0 and
case when '
$things' is not null then name else 'NULLVALUE' end 
 = coalesce('
$things', 'NULLVALUE');" '^dbSec )) 
 

 
  [ # 5 ]

So are you working now?

You could have always done a custom select based on your variable

if ($things) { $_tmp = ^"select distinct beschreibung ...}
else {$_tmp = ^"select di ...}

if (^dbexecute( $_tmp);” ‘^dbSec ))

 

 
  [ # 6 ]

That’s not a good idea - in that case I would have to define every possible query individually.

 

 
  [ # 7 ]

you already define every type of query individually. You could simply write code that given a query, locates the variable reference and checks it, and reformats it to allow it to pass unchanged or edits it to handle NULLVALUE.  It’s just a subroutine you use.

 

 
  [ # 8 ]
Bruce Wilcox - May 22, 2019:

you already define every type of query individually. You could simply write code that given a query, locates the variable reference and checks it, and reformats it to allow it to pass unchanged or edits it to handle NULLVALUE.  It’s just a subroutine you use.

No, I have one query for all. What I showed was just a fraction of my query.

I will have to find a workaround for this in SQL.

 

 
  [ # 9 ]

I have thought of a workaround that uses default values instead of NULL in my database.

One more question though.

So far, I’ve used something like this to control the flow of my conversation:

uART2 (!$var) ....
 
a: (_~var2) .... 

How do I check if $var has a certain value? Say, I want $var to have the value ‘empty’

($var = ‘empty’)

how do I do this?

 

 

 
  [ # 10 ]

Presumably you meant to write $var2 not ~var2.  To test for NOT a value you do $var2!=xxx. For null value you can test $var2!=null or !$var2

 

 
  [ # 11 ]

Alright, so here is the solution to my problem:

Messing with NULL is no use in Chatscript if you are combining it with Postgresql. Just stay away from it.

Instead of having $var = null, is $var = ^“‘nothing’”.

Then if you have u: (!$var) .... or similar somewhere, what you need to do is u: (!$var=“‘nothing’”). In !$var the compiler checks if $var exists. In (!$var=“‘nothing’”) checks if $var has any value other than ‘nothing’

Then in your database query:

If you have something like this:

case when $things is not null then name else 'NULLVALUE' end 
 
coalesce('$things''NULLVALUE');" '^dbSec )) 

You can make a workaround like this:

column = (case when $things like '%nothing%' then name else $things end

This way, your [NULL] entries in your database can also remain as is.

 

 
  [ # 12 ]

Your test is cleanest with ($var!=nothing)  you dont have to put the ! in front and you dont have to quote nothing

 

 
  login or register to react