Why does my sql statement work in my editor but not my JS function?

Question

I have sql statement that works perfectly fine running it in my sql editor but when I i put it in my javascript function it tells me invalid column for my client id.

function getFeedposts(data) {
 var limit = data.dollarLimit;
 var client_id = data.salesforce_username;
 var sqlQuery = `select coalesce(advertiser,'') as advertiser, 
coalesce(partner,'') as partner, 
coalesce(advertiser,'') || ' via ' || coalesce(partner,'') as line1,
' - SSP: blah' as line2,
' - Lead Date: ' || date as line3,
'Yesterday''s Activity' as line4,
 ' - Impressions: ' || coalesce(impressions,0) as line5,
 ' - CPM: $' || coalesce(round(ecpm,2),0) as line6,
 ' - Spend: $' || coalesce(round(revenue,2),0) as line7,
 'Yesterday''s Spend Breakout' as line7a,
 coalesce(device_type,'') as line8,
'Running Spend Totals' as line9,
 ' - 3 Day Spend: $' || coalesce(round(three,2),0) as line10,
 ' - 7 Day Spend: $' || coalesce(round(seven,2),0) as line11,
 ' - 30 Day Spend: $' || coalesce(round(thirty,2),0) as line12,
 coalesce(round(thirty,2),0) as line13,
 coalesce(round(seven,2),0) as line14,
 coalesce(round(three,2),0) as line15,
 coalesce(round(three,2),0) as line15,
 client_id as client_id
 from
 (select advertiser, partner, date, impressions, ecpm, revenue,       device_type, client_id
 ,(select sum(m.revenue)
 FROM blahblah as m
 WHERE m.date > rl.date -30
 and advertiser = rl.advertiser
 and partner = rl.partner
GROUP BY partner, advertiser) as thirty
,(select sum(m.revenue)
FROM blahblah as m
WHERE m.date > rl.date -7
and advertiser = rl.advertiser
and partner = rl.partner
GROUP BY partner, advertiser) as seven
,(select sum(revenue)
FROM blahblah as m
WHERE m.date > rl.date -3
and advertiser = rl.advertiser
and partner = rl.partner
GROUP BY partner, advertiser) as three
from blahblah as rl
) as idunno
WHERE 
date = to_date('${data.date}','mm-dd-yyyy')
and revenue > 1 and client_id ='[email protected]'
`;
queryRDS(sqlQuery, data);
}

this works but when i do

 function getFeedposts(data) {
 var limit = data.dollarLimit;
 var client_id = data.salesforce_username;
 var sqlQuery = `select coalesce(advertiser,'') as advertiser, 
coalesce(partner,'') as partner, 
coalesce(advertiser,'') || ' via ' || coalesce(partner,'') as line1,
' - SSP: blah' as line2,
' - Lead Date: ' || date as line3,
'Yesterday''s Activity' as line4,
 ' - Impressions: ' || coalesce(impressions,0) as line5,
 ' - CPM: $' || coalesce(round(ecpm,2),0) as line6,
 ' - Spend: $' || coalesce(round(revenue,2),0) as line7,
 'Yesterday''s Spend Breakout' as line7a,
 coalesce(device_type,'') as line8,
'Running Spend Totals' as line9,
 ' - 3 Day Spend: $' || coalesce(round(three,2),0) as line10,
 ' - 7 Day Spend: $' || coalesce(round(seven,2),0) as line11,
 ' - 30 Day Spend: $' || coalesce(round(thirty,2),0) as line12,
 coalesce(round(thirty,2),0) as line13,
 coalesce(round(seven,2),0) as line14,
 coalesce(round(three,2),0) as line15,
 coalesce(round(three,2),0) as line15,
 client_id as client_id
 from
 (select advertiser, partner, date, impressions, ecpm, revenue,       device_type, client_id
 ,(select sum(m.revenue)
 FROM blahblah as m
 WHERE m.date > rl.date -30
 and advertiser = rl.advertiser
 and partner = rl.partner
GROUP BY partner, advertiser) as thirty
,(select sum(m.revenue)
FROM blahblah as m
WHERE m.date > rl.date -7
and advertiser = rl.advertiser
and partner = rl.partner
GROUP BY partner, advertiser) as seven
,(select sum(revenue)
FROM blahblah as m
WHERE m.date > rl.date -3
and advertiser = rl.advertiser
and partner = rl.partner
GROUP BY partner, advertiser) as three
from blahblah as rl
) as idunno
WHERE 
date = to_date('${data.date}','mm-dd-yyyy')
and revenue >
 `;
  sqlQuery += limit + 'and client_id =' + client_id;

  queryRDS(sqlQuery, data);
   }

I would like to know why it tells invalid column for the email address that is the current client_id when doing it like this but when I hard code it it works perfectly fine any help would be greatly appreciated

this is the error message i recieve

RDS query successful
finished writing to rds
{ [error: column "joseph" does not exist]
 name: 'error',
 length: 101,
  severity: 'ERROR',
 code: '42703',
 detail: undefined,
 hint: undefined,
 position: '1596',
 internalPosition: undefined,
 internalQuery: undefined,
 where: undefined,
 schema: undefined,
 table: undefined,
 column: undefined,
 dataType: undefined,
 constraint: undefined,
 file: 'parse_relation.c',
 line: '3090',
 routine: 'errorMissingColumn' }

I'd say it's because you don't have a space in the string in this bit of code limit + 'and client_id =' so it's output to something like and revenue > 2and client_id — George
– George, Commented Apr 26, 2017 at 12:39
@George so you think if i add at a space via + " " it should work ? Ill try that real quick — Joseph Mckenzie
– Joseph Mckenzie, Commented Apr 26, 2017 at 12:44
Could be the way you're building your string. Check out my answer — Pineda
– Pineda, Commented Apr 26, 2017 at 12:51

George · Accepted Answer · 2017-04-26 12:48:13Z

2

You just need to change the line to this:

sqlQuery += limit + " and client_id = '" + client_id + "'";

Because the client_id variable isn't in quotes, the SQL statement is treating it as a column name, hence you get the invalid column id error.

edited Apr 26, 2017 at 12:48

George

6,7592 gold badges31 silver badges36 bronze badges

answered Apr 26, 2017 at 12:43

inkbleed

312 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joseph Mckenzie Over a year ago

Thank you I wish i could accept both answers however pineda answered 3 min before you so i accepted his

inkbleed Over a year ago

Not a problem :)

Pineda · Accepted Answer · 2017-04-26 13:01:26Z

1

In the working example you surround the email value with single quotes. In the latter, there are no quotes surrounding the email value.

Change this:

sqlQuery += limit + 'and client_id =' + client_id

To this:

sqlQuery += limit + "and client_id ='" + client_id +"'"
// Notice the single quotes --------^----------------^
// enclosed in double quotes

edited Apr 26, 2017 at 13:01

answered Apr 26, 2017 at 12:46

Pineda

7,5933 gold badges34 silver badges47 bronze badges

1 Comment

Pineda Over a year ago

Glad you found this useful :)

Collectives™ on Stack Overflow

Why does my sql statement work in my editor but not my JS function?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related