Where clause not working

Question

Post reply

Where clause not working

Viewing 8 posts - 16 through 22 (of 22 total)

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

It's not hard.

Just right-click on QA on table DR, select "Script Object into Clipboard as -> Insert" and paste it here.

_____________
Code for TallyGenerator

dba321 Hall of Fame Points: 3917 More actions · Answer 2

when this is in there, it screws things up and the query brings back no results:

WHERE DR.customernumber IS NULL

when I take that out, the query brings in all the numbers in my IN clause but the problem still remains that I do not want an insert on those numbers which are already in the DR table.

Table Structure:

DR

----

customernumber(FK)

master

---------

number (PK that matches with DR.customernumber)

customer

----------

customer (PK which links to DR.customer and m.customer)

DR is a transaction table so you'll end up with multiple customer numbers for the same customer....I want to filter out only one distinct line and insert that per customer

dba321 Hall of Fame Points: 3917 More actions · Answer 3

it's actually "DCR", not DR..I changed it to DR for privacy purposes so don't be alarmed, that's not the problem:

dcr table: http://www.photopizzaz.biz/drscript.txt

customer table: http://www.photopizzaz.biz/customerscript.txt

master table: http://www.photopizzaz.biz/masterscript.txt

dba321 Hall of Fame Points: 3917 More actions · Answer 4

Your query to test using UNIONs produces syntax errors:

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'As'.

Msg 170, Level 15, State 1, Line 47

Line 47: Incorrect syntax near 'dt'.

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 5

Your Inner Query :

produces more than 1 record.

You should check it.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 6

>>Your query to test using UNIONs produces syntax errors:

Indeed it does. Fix the 3 Case expressions and add the "End" keywords that I forgot:

Case When m.Customer Is Null Then 'N' Else 'Y' End As InMaster,

Case When c.Customer Is Null Then 'N' Else 'Y' End As InCustomer,

Case When d.CustomerNumber Is Null Then 'N' Else 'Y' End As InDR

dba321 Hall of Fame Points: 3917 More actions · Answer 7

yes, my inner query does produce muiltiple records which is not wrong...however I just added DISTINCT to it to solve that problem. Now back to why this isn't working!

JeffB Hall of Fame Points: 3541 More actions · Answer 8

It is probably not the cause but in the DCR table, customernumber is declared as varchar(50) while in the customer and master tables, customer is declared as varchar(7). Since they are the same, they should be the same size.

Now on to why this isn't working. Are you sure there are some rows that exist that satisfy the criteria? The syntax I provided gives the expected results if the data is OK.