March 22, 2016 at 2:56 pm
Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'from'.
Select
pc.accountnbr 'account number',
qf.description 'Fund Name',
pc.checknbr 'Check Number',
p.fullname 'Pay Provider',
pc.amountpaid 'Amount Paid',
replace (pc.paydiscount,'-',''),
pc.advanceapplied 'Advance',
pc.checkamt 'Check AMT',
pc.checkprintdate As StartD,
pc.checkprintdate As EndD
from
paycheck pc
left join qfund qf on pc.fundid = qf.fundid,
left join payment pm on pm.paymentid = pc.paymentid,
left join provider p on pm.provid = p.provid
Where
pc.accountnbr Not In ('Null', '') And
pc.checknbr Not In ('Null', '') And
--pc.checkprintdate Between @StartD And @EndD
Group By
pc.accountnbr, qf.description, pc.checkamt, pc.advanceapplied, pc.checknbr,
pc.checkprintdate, p.provid, p.checkamt, pc.fundid, qf.fedid, qf.fundid,
p.checknbr, pc.amountpaid
March 22, 2016 at 2:59 pm
Select
pc.accountnbr 'account number',
qf.description 'Fund Name',
pc.checknbr 'Check Number',
p.fullname 'Pay Provider',
pc.amountpaid 'Amount Paid',
replace (pc.paydiscount,'-',''),
pc.advanceapplied 'Advance',
pc.checkamt 'Check AMT',
pc.checkprintdate As StartD,
pc.checkprintdate As EndD
from
paycheck pc
left join qfund qf on pc.fundid = qf.fundid,
left join payment pm on pm.paymentid = pc.paymentid,
left join provider p on pm.provid = p.provid
Where
pc.accountnbr Not In ('Null', '') And
pc.checknbr Not In ('Null', '') And
--pc.checkprintdate Between @StartD And @EndD
Group By
pc.accountnbr, qf.description, pc.checkamt, pc.advanceapplied, pc.checknbr,
pc.checkprintdate, p.provid, p.checkamt, pc.fundid, qf.fedid, qf.fundid,
p.checknbr, pc.amountpaid
WHERE needs to go after the FROM
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 22, 2016 at 3:03 pm
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'join'.
Michael L John (3/22/2016)
Select
pc.accountnbr 'account number',
qf.description 'Fund Name',
pc.checknbr 'Check Number',
p.fullname 'Pay Provider',
pc.amountpaid 'Amount Paid',
replace (pc.paydiscount,'-',''),
pc.advanceapplied 'Advance',
pc.checkamt 'Check AMT',
pc.checkprintdate As StartD,
pc.checkprintdate As EndD
from
paycheck pc
left join qfund qf on pc.fundid = qf.fundid,
left join payment pm on pm.paymentid = pc.paymentid,
left join provider p on pm.provid = p.provid
Where
pc.accountnbr Not In ('Null', '') And
pc.checknbr Not In ('Null', '') And
--pc.checkprintdate Between @StartD And @EndD
Group By
pc.accountnbr, qf.description, pc.checkamt, pc.advanceapplied, pc.checknbr,
pc.checkprintdate, p.provid, p.checkamt, pc.fundid, qf.fedid, qf.fundid,
p.checknbr, pc.amountpaid
WHERE needs to go after the FROM
March 22, 2016 at 3:09 pm
rcooper 78099 (3/22/2016)
Msg 156, Level 15, State 1, Line 15Incorrect syntax near the keyword 'join'.
Michael L John (3/22/2016)
Select
pc.accountnbr 'account number',
qf.description 'Fund Name',
pc.checknbr 'Check Number',
p.fullname 'Pay Provider',
pc.amountpaid 'Amount Paid',
replace (pc.paydiscount,'-',''),
pc.advanceapplied 'Advance',
pc.checkamt 'Check AMT',
pc.checkprintdate As StartD,
pc.checkprintdate As EndD
from
paycheck pc
left join qfund qf on pc.fundid = qf.fundid,
left join payment pm on pm.paymentid = pc.paymentid,
left join provider p on pm.provid = p.provid
Where
pc.accountnbr Not In ('Null', '') And
pc.checknbr Not In ('Null', '') And
--pc.checkprintdate Between @StartD And @EndD
Group By
pc.accountnbr, qf.description, pc.checkamt, pc.advanceapplied, pc.checknbr,
pc.checkprintdate, p.provid, p.checkamt, pc.fundid, qf.fedid, qf.fundid,
p.checknbr, pc.amountpaid
WHERE needs to go after the FROM
Did you try to spot that error? Did you double click the error in the results pane to take you right to the actual line?
Hint: It's related to the commented out line.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 22, 2016 at 3:54 pm
Michael L John (3/22/2016)
rcooper 78099 (3/22/2016)
Msg 156, Level 15, State 1, Line 15Incorrect syntax near the keyword 'join'.
Michael L John (3/22/2016)
Select
pc.accountnbr 'account number',
qf.description 'Fund Name',
pc.checknbr 'Check Number',
p.fullname 'Pay Provider',
pc.amountpaid 'Amount Paid',
replace (pc.paydiscount,'-',''),
pc.advanceapplied 'Advance',
pc.checkamt 'Check AMT',
pc.checkprintdate As StartD,
pc.checkprintdate As EndD
from
paycheck pc
left join qfund qf on pc.fundid = qf.fundid,
left join payment pm on pm.paymentid = pc.paymentid,
left join provider p on pm.provid = p.provid
Where
pc.accountnbr Not In ('Null', '') And
pc.checknbr Not In ('Null', '') And
--pc.checkprintdate Between @StartD And @EndD
Group By
pc.accountnbr, qf.description, pc.checkamt, pc.advanceapplied, pc.checknbr,
pc.checkprintdate, p.provid, p.checkamt, pc.fundid, qf.fedid, qf.fundid,
p.checknbr, pc.amountpaid
WHERE needs to go after the FROM
Did you try to spot that error? Did you double click the error in the results pane to take you right to the actual line?
Hint: It's related to the commented out line.
There's that, and there's an issue with the JOINs in the FROM clause. Pesky little characters where they shouldn't be 🙂
Cheers!
March 23, 2016 at 8:47 am
Also, this code will probably not do what you think it it should be doing.
Where
pc.accountnbr Not In ('Null', '') And
pc.checknbr Not In ('Null', '') And
--pc.checkprintdate Between @StartD And @EndD
It looks like you want to get accounts and checks that have a valid value.
Are you storing the string value "null" in the field? If you are, then it will work. If you the field is NULL, this will not work.
It also looks like there may be empty strings in those fields.
If you are trying to get valid values, this is the way you want to write this:
pc.accountnbr <> ''
AND pc.checknbr <> ''
NULLS are undefined in SQL. They cannot be used in a comparison of this type. In the code I provided, the NULLS will not be included in the results. The test for the empty string will also exclude the NULLS.
You also may have an issue with the date comparison
pc.checkprintdate Between @StartD And @EndD
Is the field pc.checkprintdate declared as a datetime data type?
Is the @EndD being assigned a value without the time portion?
Such as:
SET @EndD = '01/01/2016'
If the field pc.checkprintdate contains datetime values with the time portion, like "2016-01-01 10:41:17.750", then your query may exclude everything that occurred on the end date.
To explain further, if the start date was assigned '01/01/2016' and the end date was assigned '01/01/2016', you would not get records for January 1st, you would only get records for January 1st at MIDNIGHT.
I suggest that you take some time, and start here to get some basic learning under your belt.
http://www.sqlservercentral.com/stairway/75773/
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 23, 2016 at 9:43 am
Update- I got the error fixed. Not entirely sure what it was. However I can tell you the @StartD and @EndD are correct. It is my mistake for not listing that this Query is being used in SSRS and those are parameter query based values.
Michael L John (3/23/2016)
Also, this code will probably not do what you think it it should be doing.
Where
pc.accountnbr Not In ('Null', '') And
pc.checknbr Not In ('Null', '') And
--pc.checkprintdate Between @StartD And @EndD
It looks like you want to get accounts and checks that have a valid value.
Are you storing the string value "null" in the field? If you are, then it will work. If you the field is NULL, this will not work.
It also looks like there may be empty strings in those fields.
If you are trying to get valid values, this is the way you want to write this:
pc.accountnbr <> ''
AND pc.checknbr <> ''
NULLS are undefined in SQL. They cannot be used in a comparison of this type. In the code I provided, the NULLS will not be included in the results. The test for the empty string will also exclude the NULLS.
You also may have an issue with the date comparison
pc.checkprintdate Between @StartD And @EndD
Is the field pc.checkprintdate declared as a datetime data type?
Is the @EndD being assigned a value without the time portion?
Such as:
SET @EndD = '01/01/2016'
If the field pc.checkprintdate contains datetime values with the time portion, like "2016-01-01 10:41:17.750", then your query may exclude everything that occurred on the end date.
To explain further, if the start date was assigned '01/01/2016' and the end date was assigned '01/01/2016', you would not get records for January 1st, you would only get records for January 1st at MIDNIGHT.
I suggest that you take some time, and start here to get some basic learning under your belt.
March 23, 2016 at 10:04 am
rcooper 78099 (3/23/2016)
Update- I got the error fixed. Not entirely sure what it was. However I can tell you the @StartD and @EndD are correct. It is my mistake for not listing that this Query is being used in SSRS and those are parameter query based values.
Please take a look at the values in the checkprintdate field.
If these values contain a time portion of the value, your query will indeed leave off everything for the end date.
If you perform this query:
SELECT checkprintdate
FROM paycheck
And the records look like this:
2015-11-13 12:47:31.540
2015-11-19 23:35:01.640
You are storing the date and time in the field.
SSRS will pass in the values like this:
2015-11-19, which is midnight.
Another simple test would be to run it for the same starting and ending date. If you do get results for that day, then you are probably ok.
Also, when you say this, that's a concern.
Not entirely sure what it was.
The error you were getting is very basic syntax that should have been easily identified. This report looks like something related to financials for your company.
It would be unfortunate for your career if this report is incorrect and important decisions are being made based upon wrong data.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 24, 2016 at 6:00 pm
rcooper 78099
Posted 3/22/2016 9:56:36 PM
Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'from'.
Into Your original code, I added some notes.
Select
pc.accountnbr 'account number',
qf.description 'Fund Name',
pc.checknbr 'Check Number',
p.fullname 'Pay Provider',
pc.amountpaid 'Amount Paid',
replace (pc.paydiscount,'-',''),
pc.advanceapplied 'Advance',
pc.checkamt 'Check AMT',
pc.checkprintdate As StartD,
pc.checkprintdate As EndD
from
paycheck pc
left join qfund qf on pc.fundid = qf.fundid --, <--- sign comma at this point causes the syntax error
left join payment pm on pm.paymentid = pc.paymentid --, <--- sign comma at this point causes the syntax error
left join provider p on pm.provid = p.provid
Where
pc.accountnbr Not In ('Null', '') And
pc.checknbr Not In ('Null', '') --And<--- And at this point causes the syntax error
--pc.checkprintdate Between @StartD And @EndD
Group By
pc.accountnbr, qf.description, pc.checkamt, pc.advanceapplied, pc.checknbr,
pc.checkprintdate, p.provid, p.checkamt, pc.fundid, qf.fedid, qf.fundid,
p.checknbr, pc.amountpaid
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply