insert

[wutang26]

I get an error with my script:
saying: Invalid Column name 'Y' Invalid column name '200710'

Does someone know why this is

any help would be appreciated.


INSERT INTO eval_master ( PIDM, REGISTERED_IND, RSTS_CODE, TERM, RSTS_DESC, TERM_DESC, CRN, [LAST], DEGC_DESC, MAJR_DESC1, SSN, MAJR_DESC1, ASTD_CODE_END_OF_TERM, ASTD_DESC_END_OF_TERM, ASTD_DATE_END_OF_TERM, SUBJ_CODE, COURSE_NUMBER, SUBJ_DESC, COURSE_TITLE, SECTION_NUMBER, INSTRUCTOR_LAST_NAME, INSTRUCTOR_FIRST_NAME, INDEX1 )
SELECT AS_STUDENT_REGISTRATION_DETAIL.PIDM_KEY, AS_STUDENT_REGISTRATION_DETAIL.REGISTERED_IND, AS_STUDENT_REGISTRATION_DETAIL.RSTS_CODE, AS_STUDENT_REGISTRATION_DETAIL.TERM_CODE_KEY, AS_STUDENT_REGISTRATION_DETAIL.RSTS_DESC, AS_STUDENT_REGISTRATION_DETAIL.TERM_DESC, AS_STUDENT_REGISTRATION_DETAIL.CRN_KEY, AS_STUDENT_REGISTRATION_DETAIL.LAST_NAME, AS_STUDENT_REGISTRATION_DETAIL.DEGC_DESC, AS_STUDENT_REGISTRATION_DETAIL.MAJR_DESC1, AS_STUDENT_REGISTRATION_DETAIL.SSN, AS_STUDENT_REGISTRATION_DETAIL.MAJR_DESC1, AS_STUDENT_REGISTRATION_DETAIL.ASTD_CODE_END_OF_TERM, AS_STUDENT_REGISTRATION_DETAIL.ASTD_DESC_END_OF_TERM, AS_STUDENT_REGISTRATION_DETAIL.ASTD_DATE_END_OF_TERM, AS_STUDENT_REGISTRATION_DETAIL.SUBJ_CODE, AS_STUDENT_REGISTRATION_DETAIL.COURSE_NUMBER, AS_STUDENT_REGISTRATION_DETAIL.SUBJ_DESC, AS_STUDENT_REGISTRATION_DETAIL.COURSE_TITLE, AS_STUDENT_REGISTRATION_DETAIL.SECTION_NUMBER, AS_STUDENT_REGISTRATION_DETAIL.INSTRUCTOR_LAST_NAME, AS_STUDENT_REGISTRATION_DETAIL.INSTRUCTOR_FIRST_NAME, AS_STUDENT_REGISTRATION_DETAIL.[PIDM_KEY] & [TERM_CODE_KEY] & [CRN_KEY] AS INDEX1
FROM eval_master RIGHT JOIN AS_STUDENT_REGISTRATION_DETAIL ON (eval_master.CRN = AS_STUDENT_REGISTRATION_DETAIL.CRN_KEY) AND (eval_master.TERM = AS_STUDENT_REGISTRATION_DETAIL.TERM_CODE_KEY) AND (eval_master.PIDM = AS_STUDENT_REGISTRATION_DETAIL.PIDM_KEY)
WHERE (((AS_STUDENT_REGISTRATION_DETAIL.REGISTERED_IND)="Y") AND ((AS_STUDENT_REGISTRATION_DETAIL.TERM_CODE_KEY)="200710"))
ORDER BY AS_STUDENT_REGISTRATION_DETAIL.PIDM_KEY;

 

[unclerico]

try removing the parenthesis here:

WHERE (([b][COLOR=red]([/color][/b]AS_STUDENT_REGISTRATION_DETAIL.REGISTERED_IND[b][COLOR=red])[/color][/b]="Y") AND ([b][COLOR=red]([/color][/b]AS_STUDENT_REGISTRATION_DETAIL.TERM_CODE_KEY[b][COLOR=red])[/color][/b]="200710"))
ORDER BY AS_STUDENT_REGISTRATION_DETAIL.PIDM_KEY;

 

[wutang26]

no luck

 

[unclerico]

duh (me, not you)...you are using double quotes as opposed to single quotes, switch them around and you should be good to go

 

[wutang26]

I made those changes but
I get an error still.

Invalid operator for data type. Operator equals boolean AND, type equals numeric

 

[mutley1]

Check the data types between source and target fields. Might be a mismatch, e.g. trying to insert a date field into a varchar field with no conversion inbetween.

 

[wutang26]

this is where I think the problem is but I'm not sure how to fix it

AS_STUDENT_REGISTRATION_DETAIL.[PIDM_KEY] & [TERM_CODE_KEY] & [CRN_KEY] AS INDEX1

 

[mutley1]

Just going back to unclerico's first post, why do you need any parantheses? It's a simple where x = this AND y = that. Can't see any reason for any parentheses

WHERE (((AS_STUDENT_REGISTRATION_DETAIL.REGISTERED_IND)="Y") AND ((AS_STUDENT_REGISTRATION_DETAIL.TERM_CODE_KEY)="200710"))
ORDER BY AS_STUDENT_REGISTRATION_DETAIL.PIDM_KEY;

why not just (with single quotes)

WHERE AS_STUDENT_REGISTRATION_DETAIL.REGISTERED_IND = 'Y'
AND AS_STUDENT_REGISTRATION_DETAIL.TERM_CODE_KEY = '200710'
ORDER BY AS_STUDENT_REGISTRATION_DETAIL.PIDM_KEY;

If that doesn't fix it, can you let us know what the data types are for fields:

1. AS_STUDENT_REGISTRATION_DETAIL.[PIDM_KEY]
2. AS_STUDENT_REGISTRATION_DETAIL.[TERM_CODE_KEY]
3. AS_STUDENT_REGISTRATION_DETAIL.[CRN_KEY]
4. EVAL_MASTER.INDEX1

And I assume where you have the & symbol you are trying to concatenate the 3 fields into 1 string?

 

[wutang26]

did not work.

pidm key is numeric
term_code_ket varchar
crn_key varchar.
index varchar

in access it works great

 

[wutang26]

yes, I concatenate them. and placing that value in index column

 

[mutley1]

OK, instead of

AS_STUDENT_REGISTRATION_DETAIL.[PIDM_KEY] & [TERM_CODE_KEY] & [CRN_KEY] AS INDEX1

try

convert (varchar (50), AS_STUDENT_REGISTRATION_DETAIL.[PIDM_KEY])+AS_STUDENT_REGISTRATION_DETAIL.[TERM_CODE_KEY]+AS_STUDENT_REGISTRATION_DETAIL.[CRN_KEY]

 

[wutang26]

I get this now

String or binary data would be truncated
The statement has been terminated

Any suggestions

 

[unclerico]

can you repost your entire querty with the changes suggested here??

 

[mutley1]

try changing varchar (50) to varchar (255). Not sure what length your data is.

 

[mutley1]

Sorry, also check the target column index1. Script the table out and see what value comes after the varchar on that column.