Stored Procedure Not Reading Duplicate Entries

Question

Post reply

Stored Procedure Not Reading Duplicate Entries

emmettjarlath

SSC Veteran

Points: 232
More actions
March 21, 2012 at 3:28 am

#270402

Hi,
I have a stored procedure that creates a template but it is not pulling one description from a table. The table includes a unique number which is matched in another table where a general ledger number is held.
There is a duplicate entry for the general ledger number. The stored procedure is not pulling any of the information for this one description.
Would the duplicate general ledger number be the cause?
Any input would be great. Thank you in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

With actually seeing the procedure itself, all we can do is offer basic advise.

if the Prof is skipping data, it is usually because a WHERE or JOIN statement in it s excluding.the data you expect.

You also might want to look for a DISTINCT command, or see if there is a GROUP BY.

You didn't give anyone who is not actually looking over your shoulder anything to help diagnose the issue in your post above. Yup will need to provide a lot more detail to get the help to dig intero the issue.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

emmettjarlath SSC Veteran Points: 232 More actions · Answer 2

Hi,

I have attached a snippet of the stored procedure. I inherited this database when I started this job. When the stored procedure runs it pulls information from table A and inserts it into the scheme.cc_invoiced table. The information pulled is the unit no, description, vat code and line no. The nett is pulled a different table (scheme.nltranm). Table 1 contains another column, line no2. This is matched with another column (Line No) from Billing_GLCodes. This pulls the general ledger code which is matched with the account code from nltranm.

It looks and sounds weird but the stored procedure is returning everything else bar one line. The Billing_GLCodes tabel ahs a duplicate general ledger code for the line that is not being pulled. Could this be the case? Or would it be a dataype issue? Although that was my first instinct and I have checked and rechecked the datatypes and everythings looks since all the other lines are being pulled.

Hope this helps. Let me know if you need more information.

Thanks,

Emmett

Lowell SSC Guru Points: 323504 More actions · Answer 3

what are the specific row values that are not coming over;

the WHERE statement you posted has WHERE items that might filter it out, as well as a group by to avoid duplicates:

the grouping on posting_code might be the issue, or the row you think should come over does nto match the WHERE based on any of these criteria:

Line_No

left(e4w_attribute_code2,2)

and (e4w_attribute_code3)

look at the data for both the row that comes over and the row that does not come over.

do they both share the same substring(posting_code,3,3)?

where Line_No =' + @single_quote + @line_no2 +@single_quote + ')

and

left(e4w_attribute_code2,2)= ' + @single_quote + @type + @single_quote + '

and (e4w_attribute_code3) ='+ @single_quote + convert(char(1),@processing) +@single_quote + '

group by substring(posting_code,3,3)

having sum(journal_amount)<>0.00

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

tfifield SSCrazy Eights Points: 9655 More actions · Answer 4

Emmet,

You need to post the DDL for the tables involved and some sample data for the tables. Also we don't have an idea as to what the variables are and what values they typically contain when running the query.

Also, instead of posting the dynamic SQL you should post what the query really looks like from the @query string where the query is built. It would make things much easier to help you.

Todd Fifield

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

emmettjarlath (3/21/2012)
Hi,
I have attached a snippet of the stored procedure. I inherited this database when I started this job. When the stored procedure runs it pulls information from table A and inserts it into the scheme.cc_invoiced table. The information pulled is the unit no, description, vat code and line no. The nett is pulled a different table (scheme.nltranm). Table 1 contains another column, line no2. This is matched with another column (Line No) from Billing_GLCodes. This pulls the general ledger code which is matched with the account code from nltranm.
It looks and sounds weird but the stored procedure is returning everything else bar one line. The Billing_GLCodes tabel ahs a duplicate general ledger code for the line that is not being pulled. Could this be the case? Or would it be a dataype issue? Although that was my first instinct and I have checked and rechecked the datatypes and everythings looks since all the other lines are being pulled.
Hope this helps. Let me know if you need more information.
Thanks,
Emmett

Interesting snippet, doesn't help.

Please post the ddl for the stored procedure and the tables. We will also need sample data that is representative of the problem domain (and the problem) in a readily useable format to populate the tables.

Since we can't see from here what you see there you really have to give us more information to work with to help you solve this problem.

Please remember that we are volunteers here helping on our own time. If you can't help us help you, we can't help you. We are SQL Professionals, not psychics.