SQL statement and returned values blank

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL statement and returned values blank

Stephen Smiroldo-4
Anyone see anything wrong with this?

- - - - - - - - -
var:'sql_statement'=String;
$sql_statement+='SELECT email FROM mailing_list WHERE status=\'clean\' AND full_name<>\'null\'';
       
Inline: -SQL=$sql_statement,
        -username=$un,
        -password=(Decrypt_Blowfish2: (decode_base64:$pass), -Seed=$seed),
        -Database=‘list’, -SortField='updated',-MaxRecords=100;

         if(found_count);
                records;
                        local: 'full_name' = field:'full_name';
                        output:(field:'email')+' ('+#full_name+')<br />',-EncodeSmart;
                /records;
        /if;
/inline;
- - - - - - - - - -

It’s outputting the “email” value but NOT the “full_name” value. I’ve also tried many other column names in this table but the ONLY value it’s pulling is the “email” column. I also created a new table and imported a CSV of the email list data into the new table but am getting the same results.

The data is there to pull the full name but it’s just not getting any value other than the email. Is there something obvious I’m missing?? I feel like Gremlins have invaded.  ;-)

Stephen


#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: SQL statement and returned values blank

Peter D Bethke
Hi Stephen,

You are missing the column full_name from your SQL.

SELECT email, full_name FROM mailing_list WHERE status=\'clean\' AND full_name<>\'null\’’;


Best,

Peter

> On Jul 15, 2016, at 10:40 AM, Stephen Smiroldo <[hidden email]> wrote:
>
> Anyone see anything wrong with this?
>
> - - - - - - - - -
> var:'sql_statement'=String;
> $sql_statement+='SELECT email FROM mailing_list WHERE status=\'clean\' AND full_name<>\'null\'';
>
> Inline: -SQL=$sql_statement,
> -username=$un,
> -password=(Decrypt_Blowfish2: (decode_base64:$pass), -Seed=$seed),
> -Database=‘list’, -SortField='updated',-MaxRecords=100;
>
> if(found_count);
> records;
> local: 'full_name' = field:'full_name';
> output:(field:'email')+' ('+#full_name+')<br />',-EncodeSmart;
> /records;
> /if;
> /inline;
> - - - - - - - - - -
>
> It’s outputting the “email” value but NOT the “full_name” value. I’ve also tried many other column names in this table but the ONLY value it’s pulling is the “email” column. I also created a new table and imported a CSV of the email list data into the new table but am getting the same results.
>
> The data is there to pull the full name but it’s just not getting any value other than the email. Is there something obvious I’m missing?? I feel like Gremlins have invaded.  ;-)
>
> Stephen
>
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>  the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>


#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: SQL statement and returned values blank

Stephen Smiroldo-4
Oh. Good. Grief. I knew it had to be obvious.

As you can tell, I don’t create many raw SQL statements. Learning though.

Thanks!


> On Jul 15, 2016, at 10:46 AM, Peter D Bethke <[hidden email]> wrote:
>
> Hi Stephen,
>
> You are missing the column full_name from your SQL.
>
> SELECT email, full_name FROM mailing_list WHERE status=\'clean\' AND full_name<>\'null\’’;
>
>
> Best,
>
> Peter
>
>> On Jul 15, 2016, at 10:40 AM, Stephen Smiroldo <[hidden email]> wrote:
>>
>> Anyone see anything wrong with this?
>>
>> - - - - - - - - -
>> var:'sql_statement'=String;
>> $sql_statement+='SELECT email FROM mailing_list WHERE status=\'clean\' AND full_name<>\'null\'';
>>
>> Inline: -SQL=$sql_statement,
>> -username=$un,
>> -password=(Decrypt_Blowfish2: (decode_base64:$pass), -Seed=$seed),
>> -Database=‘list’, -SortField='updated',-MaxRecords=100;
>>
>> if(found_count);
>> records;
>> local: 'full_name' = field:'full_name';
>> output:(field:'email')+' ('+#full_name+')<br />',-EncodeSmart;
>> /records;
>> /if;
>> /inline;
>> - - - - - - - - - -
>>
>> It’s outputting the “email” value but NOT the “full_name” value. I’ve also tried many other column names in this table but the ONLY value it’s pulling is the “email” column. I also created a new table and imported a CSV of the email list data into the new table but am getting the same results.
>>
>> The data is there to pull the full name but it’s just not getting any value other than the email. Is there something obvious I’m missing?? I feel like Gremlins have invaded.  ;-)
>>
>> Stephen
>>
>>
>> #############################################################
>>
>> This message is sent to you because you are subscribed to
>> the mailing list Lasso [hidden email]
>> Official list archives available at http://www.lassotalk.com
>> To unsubscribe, E-mail to: <[hidden email]>
>> Send administrative queries to  <[hidden email]>
>
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>  the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>


#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: SQL statement and returned values blank

Peter D Bethke
No problem - we’ve all had that “ghah” moment (I know I have).

One thing about lasso thats cool though is the little tricks you can play with the inline statements to build sql statements on the fly with arrays, then generate the sql from the action statement tag. Its a nifty trick at least to understand how the sql statements are built, but I’ve used it to make my code more readable, or modify sql on the fly.

For example, your sql might look like (untested):

local: ‘sqlarray' = (array);

#sqlarray->(insert(‘-database’=‘mailing_list_db’));
#sqlarray->(insert(‘-table’=‘mailing_list’));
#sqlarray->(insert(‘-op'=‘eq'));
#sqlarray->(insert(‘status’=‘clean'));
#sqlarray->(insert(‘-oplogical’=‘and'));
#sqlarray->(insert(‘-op'=‘neq'));
#sqlarray->(insert(‘full_name’=NULL));
#sqlarray->(insert(‘return column’=‘email’));
#sqlarray->(insert(‘return column’=‘full_name’));

#sqlarray->(insert(‘-statementonly'));

local(‘resultsql’) = string;

inline(#sqlarray);
    #resultsql = (action_statement);
/inline;

#resultsql; /** Should be more or less your original sql **/

The nifty thing is, you can then manipulate the array to alter the sql:

#resultsql->removeall(‘full_name’);

if you pass that into the inline it will generate the sql minus the full_name column.

The -statementonly tag is nifty because it will generate the statement without hitting the database, so you can then use the sql in another action, or pass it into another inline.

Peter


> On Jul 15, 2016, at 11:09 AM, Stephen Smiroldo <[hidden email]> wrote:
>
> Oh. Good. Grief. I knew it had to be obvious.
>
> As you can tell, I don’t create many raw SQL statements. Learning though.
>
> Thanks!
>
>
>> On Jul 15, 2016, at 10:46 AM, Peter D Bethke <[hidden email]> wrote:
>>
>> Hi Stephen,
>>
>> You are missing the column full_name from your SQL.
>>
>> SELECT email, full_name FROM mailing_list WHERE status=\'clean\' AND full_name<>\'null\’’;
>>
>>
>> Best,
>>
>> Peter
>>
>>> On Jul 15, 2016, at 10:40 AM, Stephen Smiroldo <[hidden email]> wrote:
>>>
>>> Anyone see anything wrong with this?
>>>
>>> - - - - - - - - -
>>> var:'sql_statement'=String;
>>> $sql_statement+='SELECT email FROM mailing_list WHERE status=\'clean\' AND full_name<>\'null\'';
>>>
>>> Inline: -SQL=$sql_statement,
>>> -username=$un,
>>> -password=(Decrypt_Blowfish2: (decode_base64:$pass), -Seed=$seed),
>>> -Database=‘list’, -SortField='updated',-MaxRecords=100;
>>>
>>> if(found_count);
>>> records;
>>> local: 'full_name' = field:'full_name';
>>> output:(field:'email')+' ('+#full_name+')<br />',-EncodeSmart;
>>> /records;
>>> /if;
>>> /inline;
>>> - - - - - - - - - -
>>>
>>> It’s outputting the “email” value but NOT the “full_name” value. I’ve also tried many other column names in this table but the ONLY value it’s pulling is the “email” column. I also created a new table and imported a CSV of the email list data into the new table but am getting the same results.
>>>
>>> The data is there to pull the full name but it’s just not getting any value other than the email. Is there something obvious I’m missing?? I feel like Gremlins have invaded.  ;-)
>>>
>>> Stephen
>>>
>>>
>>> #############################################################
>>>
>>> This message is sent to you because you are subscribed to
>>> the mailing list Lasso [hidden email]
>>> Official list archives available at http://www.lassotalk.com
>>> To unsubscribe, E-mail to: <[hidden email]>
>>> Send administrative queries to  <[hidden email]>
>>
>>
>> #############################################################
>>
>> This message is sent to you because you are subscribed to
>> the mailing list Lasso [hidden email]
>> Official list archives available at http://www.lassotalk.com
>> To unsubscribe, E-mail to: <[hidden email]>
>> Send administrative queries to  <[hidden email]>
>
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>  the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>


#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: SQL statement and returned values blank

Peter D Bethke
Well, case in point (aka Pilsonism) - my code example has a couple of flaws i just spotted:

return column should be -returncolumn

and my “removeall” would not work of course because it would remove the column full_name = NULL which would kack the whole thing.

BUT you could insert another column on the fly, or with a little extra code remove the full_name. The concept is the same :).

#sqlarray->(insert(‘-returncolumn’=‘someotherfield’));


Peter

> On Jul 15, 2016, at 11:34 AM, Peter D Bethke <[hidden email]> wrote:
>
> No problem - we’ve all had that “ghah” moment (I know I have).
>
> One thing about lasso thats cool though is the little tricks you can play with the inline statements to build sql statements on the fly with arrays, then generate the sql from the action statement tag. Its a nifty trick at least to understand how the sql statements are built, but I’ve used it to make my code more readable, or modify sql on the fly.
>
> For example, your sql might look like (untested):
>
> local: ‘sqlarray' = (array);
>
> #sqlarray->(insert(‘-database’=‘mailing_list_db’));
> #sqlarray->(insert(‘-table’=‘mailing_list’));
> #sqlarray->(insert(‘-op'=‘eq'));
> #sqlarray->(insert(‘status’=‘clean'));
> #sqlarray->(insert(‘-oplogical’=‘and'));
> #sqlarray->(insert(‘-op'=‘neq'));
> #sqlarray->(insert(‘full_name’=NULL));
> #sqlarray->(insert(‘return column’=‘email’));
> #sqlarray->(insert(‘return column’=‘full_name’));
>
> #sqlarray->(insert(‘-statementonly'));
>
> local(‘resultsql’) = string;
>
> inline(#sqlarray);
>    #resultsql = (action_statement);
> /inline;
>
> #resultsql; /** Should be more or less your original sql **/
>
> The nifty thing is, you can then manipulate the array to alter the sql:
>
> #resultsql->removeall(‘full_name’);
>
> if you pass that into the inline it will generate the sql minus the full_name column.
>
> The -statementonly tag is nifty because it will generate the statement without hitting the database, so you can then use the sql in another action, or pass it into another inline.
>
> Peter
>
>
>> On Jul 15, 2016, at 11:09 AM, Stephen Smiroldo <[hidden email]> wrote:
>>
>> Oh. Good. Grief. I knew it had to be obvious.
>>
>> As you can tell, I don’t create many raw SQL statements. Learning though.
>>
>> Thanks!
>>
>>
>>> On Jul 15, 2016, at 10:46 AM, Peter D Bethke <[hidden email]> wrote:
>>>
>>> Hi Stephen,
>>>
>>> You are missing the column full_name from your SQL.
>>>
>>> SELECT email, full_name FROM mailing_list WHERE status=\'clean\' AND full_name<>\'null\’’;
>>>
>>>
>>> Best,
>>>
>>> Peter
>>>
>>>> On Jul 15, 2016, at 10:40 AM, Stephen Smiroldo <[hidden email]> wrote:
>>>>
>>>> Anyone see anything wrong with this?
>>>>
>>>> - - - - - - - - -
>>>> var:'sql_statement'=String;
>>>> $sql_statement+='SELECT email FROM mailing_list WHERE status=\'clean\' AND full_name<>\'null\'';
>>>>
>>>> Inline: -SQL=$sql_statement,
>>>> -username=$un,
>>>> -password=(Decrypt_Blowfish2: (decode_base64:$pass), -Seed=$seed),
>>>> -Database=‘list’, -SortField='updated',-MaxRecords=100;
>>>>
>>>> if(found_count);
>>>> records;
>>>> local: 'full_name' = field:'full_name';
>>>> output:(field:'email')+' ('+#full_name+')<br />',-EncodeSmart;
>>>> /records;
>>>> /if;
>>>> /inline;
>>>> - - - - - - - - - -
>>>>
>>>> It’s outputting the “email” value but NOT the “full_name” value. I’ve also tried many other column names in this table but the ONLY value it’s pulling is the “email” column. I also created a new table and imported a CSV of the email list data into the new table but am getting the same results.
>>>>
>>>> The data is there to pull the full name but it’s just not getting any value other than the email. Is there something obvious I’m missing?? I feel like Gremlins have invaded.  ;-)
>>>>
>>>> Stephen
>>>>
>>>>
>>>> #############################################################
>>>>
>>>> This message is sent to you because you are subscribed to
>>>> the mailing list Lasso [hidden email]
>>>> Official list archives available at http://www.lassotalk.com
>>>> To unsubscribe, E-mail to: <[hidden email]>
>>>> Send administrative queries to  <[hidden email]>
>>>
>>>
>>> #############################################################
>>>
>>> This message is sent to you because you are subscribed to
>>> the mailing list Lasso [hidden email]
>>> Official list archives available at http://www.lassotalk.com
>>> To unsubscribe, E-mail to: <[hidden email]>
>>> Send administrative queries to  <[hidden email]>
>>
>>
>> #############################################################
>>
>> This message is sent to you because you are subscribed to
>> the mailing list Lasso [hidden email]
>> Official list archives available at http://www.lassotalk.com
>> To unsubscribe, E-mail to: <[hidden email]>
>> Send administrative queries to  <[hidden email]>
>
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>  the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>


#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>