Can someone show me how to change this to sql

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Can someone show me how to change this to sql

Author

Topic

kdnichols
Posting Yak Master

232 Posts

Posted - 2007-02-20 : 08:44:15

Hello Everyone,

Thanks for everyone's help on this mess so far especially Peso. Yes this was originally Oracle SQL and I now need to convert it to SQL.

Can someone start by showing me at least part of this the best way to convert this to regular SQL? Also are there any tips, guides out there that anyone knows of?


select	CSG_Hist.dbo.OJB_JOBS.IR_TECH_OJB as TECHN,
		substring(CSG_Hist.dbo.OJB_JOBS.COMPL_CDE_OJB, pstn.p, 3) as RESCODE,
		Parser.dbo.OJB_JOBS.COMPL_DTE_OJB as COMPLET,
		Parser.dbo.ELP_Codes.CodeDes as [DESCRIPTION],
		CSG_Hist.dbo.SBB_BASE.RES_NAME_SBB as RES_NAME,
		CSG_Hist.dbo.HSE_BASE.ADDR1_HSE as [ADDRESS],
		CSG_Hist.dbo.OJB_JOBS.JOB_TYP_OJB as JOB,
		CSG_Hist.dbo.OJB_JOBS.JOB_CLASS_OJB as TYP,
		CSG_Hist.dbo.OCR_ORDER_COMP.ORDER_NO_OCR as NUMB,
		CSG_Hist.dbo.OCR_ORDER_COMP.LS_CHG_OP_ID_OCR as OPR,
		Parser.dbo.ELP_Codes.CommissionAMT as NCommissionAMT,
		COUNT(DISTINCT Parser.dbo.Parser_OCR.ORDER_NO_OCR) as QTYW
from		Parser_OCR
Inner Join	Parser.dbo.Parser_OJB on Parser.dbo.Parser_OCR.ORDER_NO_OCR = CSG_Hist.dbo.OJB_JOBS.ORDER_NO_OJB
Inner JOIN	Parser.dbo.RGV_Codes on substring (Parser.dbo.Parser_OJB.COMPL_CDE_OJB, pstn.p, 3) = CODE -- IS THIS A VARIABLE/PARAMETER OR A COLUMN NAME?
Inner JOIN	CSG_Hist.dbo.SBB_BASE on CSG_Hist.dbo.OCR_ORDER_COMP.HSE_KEY_OCR = CSG_Hist.dbo.SBB_BASE.HSE_KEY_SBB
Inner JOIN	CSG_Hist.dbo.HSE_BASE on CSG_Hist.dbo.OCR_ORDER_COMP.HSE_KEY_OCR = Parser.dbo.Parser_OCR.HSE_KEY_OCR
Inner JOIN	CSG_NRT.dbo.NRT_OJB_JOBS ON CSG_NRT.dbo.NRT_OJB_JOBS.IR_TECH_NOJB = CSG_Hist.dbo.OJB_JOBS.IR_TECH_OJB
cross Join	(
			select 1 as p union all
			select 4 union all
			select 7 union all
			select 10 union all
			select 13 union all
			select 16
		) as pstn
WHERE		CSG_Hist.dbo.OJB_JOBS.IR_TECH_OJB between 950 and 999
		and Parser.dbo.Parser_OJB.COMPL_DTE_OJB BETWEEN '2007-01-09' AND '2007-01-22'
		and CSG_Hist.dbo.Parser_OCR.prin_ocr = 8600
GROUP BY	CSG_Hist.dbo.OJB_JOBS.IR_TECH_OJB,
		substring(CSG_Hist.dbo.OJB_JOBS.COMPL_CDE_OJB, pstn.p, 3),
		Parser.dbo.Parser_OJB.COMPL_DTE_OJB,
		Parser.dbo.ELP_Codes.CodeDes,
		CSG_Hist.dbo.SBB_BASE.RES_NAME_SBB,
		CSG_Hist.dbo.HSE_BASE.ADDR1_HSE,
		Parser.dbo.Parser_OJB.JOB_TYP_OJB,
		Parser.dbo.Parser_OJB.JOB_CLASS_OJB,
		CSG_Hist.dbo.OCR_ORDER_COMP.ORDER_NO_OCR,
		CSG_Hist.dbo.OCR_ORDER_COMP.LS_CHG_OP_ID_OCR,
		Parser.dbo.ELP_Codes.CommissionAMT

Again thanks for everyone's help especially Peso!

Have a great day!

Kurt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-20 : 08:55:24

1) Inner Join Parser.dbo.Parser_OJB on Parser.dbo.Parser_OCR.ORDER_NO_OCR = CSG_Hist.dbo.OJB_JOBS.ORDER_NO_OJB
This line needs to be properly bound in the JOIN. The table joined is not included in the binding.

2) Inner JOIN Parser.dbo.RGV_Codes on substring (Parser.dbo.Parser_OJB.COMPL_CDE_OJB, pstn.p, 3) = CODE
Where do the CODE come from? Is is a column in another table, or a parameter/variable?

3) Inner JOIN CSG_Hist.dbo.SBB_BASE on CSG_Hist.dbo.OCR_ORDER_COMP.HSE_KEY_OCR = CSG_Hist.dbo.SBB_BASE.HSE_KEY_SBB
The table OCR_ORDER_COMP is not previously joined in the query.

4) Inner JOIN CSG_Hist.dbo.HSE_BASE on CSG_Hist.dbo.OCR_ORDER_COMP.HSE_KEY_OCR = Parser.dbo.Parser_OCR.HSE_KEY_OCR
The table OCR_ORDER_COMP is not previously joined in the query, and is not used in the binding for the JOIN.

All these errors have been pointed out to you here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79274
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79264
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79234
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79227

and they are not specific T-SQL errors. Putting this query in ORACLE would have thrown an error too.

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-02-20 : 09:06:01

next piece of advice (continuing from the previous thread): Are you familiar with table aliases? It is a way to alias something like "Parser.dbo.Parser_OJB" as something short and simple and easy to read like "OJB".

As an example, look at this:

select A.Col1, A.Col2, A.Col3, A.Col4 + A.Col5
from SomeLongTableNameThatIsHardToReadAndType A
where A.Col1 > 2

In the above, SomeLongTableNameThatIsHardToReadAndType has been aliased to simply "A". This means that you can refer to that table as A anywhere in your SELECT. Makes things much easier to read and work with rather than repeating the long table names over and over. I recommend going through your SQL and using table aliases to make it easier to read and understand.

Then, from there, if you really want help, you need to ask specific questions. Providing some sample data and information about your table structures will help greatly as well.

Also, as I mentioned before, you should not be grouping on all those columns. Chances are, your grouping should be done only for a table or two, not for the entire thing. Did you read the blog entry I gave you a link for in the last thread about using GROUP BY properly? It will make your sql shorter and easier to work with if you correctly break it up into small parts with short, concise aliases. That also makes it easier for you to work with your code and easier for us to help you, since your code is clearer and cleaner. Those are good things!

Finally, if you are having general trouble with SQL Server syntax, I *STRONGLY* recommend closing up all windows that reference this database and playing around in some simple sample databases with one or two tables to really get a feel for exactly how things work. Create two tables, test some joins, see how aliases work, see different ways you can group, etc. I know, I know, you don't have time for that, you need to work on this now, why would you waste time on silly small sample data tables, etc, but that's how you learn. Trial and error on complicated SQL statements that you don't fully understand, just moving things around and hoping that they work, and then wading through thousand of rows of data to hopefully verify what is happening is NOT how you learn.

Even better, slowly rebuild this SELECT table by table, adding them in as you go, each time testing and verifying and understanding exactly what you are adding, how it relates, what filtering or grouping you should do, etc.

Long story short: NEVER learn by taking existing code that you don't understand. Learn recreating the features of that code, step by step, in a small sample area. If you see GROUP BY's in the code, learn about group by in your sample data. If you see sorts that you don't understand, play with sorts in your sample data until you get it. If you see joins that you don't understand, etc, and so on ...

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-02-20 : 09:08:05

ALSO: IF YOU USE CODE TAGS, PLEASE ADD SOME LINE BREAKS IN THERE OR THE ENTIRE PAGE STRETCHES OUT AND IT IS HARD TO READ AND MOST PEOPLE WILL NOT BOTHER. PLEASE EDIT YOUR POST AND PUT IN SOME LINE BREAKS. THANK YOU !!!!!!

- Jeff
http://weblogs.sqlteam.com/JeffS

kdnichols
Posting Yak Master

232 Posts

Posted - 2007-02-20 : 11:16:19

Hello Peso and Dr. Join,

Peso are you saying that I need to add the database like this:




from		Parser_OCR, CSG_Hist
Inner Join	Parser.dbo.Parser_OCR on Parser.dbo.Parser_OCR.ORDER_NO_OCR = CSG_Hist.dbo.OJB_JOBS.ORDER_NO_OJB
Inner JOIN	Parser.dbo.RGV_Codes on substring (Parser.dbo.Parser_OJB.COMPL_CDE_OJB, pstn.p, 3) = CODE -- IS THIS A VARIABLE/PARAMETER OR A COLUMN NAME?
Inner JOIN	CSG_Hist.dbo.SBB_BASE on CSG_Hist.dbo.OCR_ORDER_COMP.HSE_KEY_OCR = CSG_Hist.dbo.SBB_BASE.HSE_KEY_SBB
Inner JOIN	CSG_Hist.dbo.HSE_BASE on CSG_Hist.dbo.OCR_ORDER_COMP.HSE_KEY_OCR = Parser.dbo.Parser_OCR.HSE_KEY_OCR
Inner JOIN	CSG_NRT.dbo.NRT_OJB_JOBS ON CSG_NRT.dbo.NRT_OJB_JOBS.IR_TECH_NOJB = CSG_Hist.dbo.OJB_JOBS.IR_TECH_OJB

I am trying to bind the first table you told me to and I still get the same error message.

Msg 208, Level 16, State 1, Line 1
Invalid object name 'Parser_OCR'.

Dr. Join, yes I am taking some time to read and study on joins. I agree with you and I am doing this. I am still under the gun to get this done.

Again thanks for all input and help.

Kurt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-20 : 11:59:54

You need to INNER/LEFT/RIGHT JOIN the CSG_HIST table! Not CROSS JOIN it...
And we need an answer to what "CODE" is...

Peter Larsson
Helsingborg, Sweden

kdnichols
Posting Yak Master

232 Posts

Posted - 2007-02-20 : 12:35:01

Hello Peso,

CODE is a Parameter Value.

I just sat down with her for a bit.

Here is the code as it now stands:



select	OJB.IR_TECH_OJB as TECHN,
		substring(OJB.COMPL_CDE_OJB, pstn.p, 3) as RESCODE,
		OJB.COMPL_DTE_OJB as COMPLET,
		RGV.CodeDes as [DESCRIPTION],
		SBB.RES_NAME_SBB as RES_NAME,
		HSE.ADDR1_HSE as [ADDRESS],
		OJB.JOB_TYP_OJB as JOB,
		OJB.JOB_CLASS_OJB as TYP,
		OCR.ORDER_NO_OCR as NUMB,
		OCR.LS_CHG_OP_ID_OCR as OPR,
		RGV.CommissionAMT as NCommissionAMT,
		COUNT(DISTINCT OCR.ORDER_NO_OCR) as QTYW
from		Parser.dbo.Parser_OCR OCR
Inner Join	Parser.dbo.Parser_OJB OJB on OCR.ORDER_NO_OCR = OJB.ORDER_NO_OJB
Inner JOIN	Parser.dbo.RGV_Codes RGV on substring (OJB.COMPL_CDE_OJB, pstn.p, 3) = CODE -- IS THIS A VARIABLE/PARAMETER OR A COLUMN NAME?
Inner JOIN	CSG_Hist.dbo.SBB_BASE SBB on OCR.HSE_KEY_OCR = SBB.HSE_KEY_SBB
Inner JOIN	CSG_Hist.dbo.HSE_BASE HSE on OCR.HSE_KEY_OCR = HSE.HSE_KEY_HSE
cross Join	(
			select 1 as p union all
			select 4 union all
			select 7 union all
			select 10 union all
			select 13 union all
			select 16
		) as pstn
WHERE	OJB.IR_TECH_OJB between 950 and 999
		and OJB.COMPL_DTE_OJB BETWEEN '2007-01-09' AND '2007-01-22'
		and OCR.PRIN_OCR = 8600
GROUP BY	OJB.IR_TECH_OJB,
		substring(OJB.COMPL_CDE_OJB, pstn.p, 3),
		OJB.COMPL_DTE_OJB,
		RGV.CodeDes,
		SBB.RES_NAME_SBB,
		HSE.ADDR1_HSE,
		OJB.JOB_TYP_OJB,
		OJB.JOB_CLASS_OJB,
		OCR.ORDER_NO_OCR,
		OCR.LS_CHG_OP_ID_OCR,
		RGV.CommissionAMT

There were a few mistakes that she did fix.

The error message now points back to:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "pstn.p" could not be bound.

I thank you for all of your expertise and help on this and so does she.

I have also taken Dr. Cross Join's advice to heart and I am diligently studying. I take this extreme exercise to help make me better.

Thanks again to all and especially Patron Saint Peso.

Kurt

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-02-20 : 12:40:54

Your joins need to refernce tables in the correct order. i.e., you cannot reference pstn BEFORE it is defined. So, you'd need to write your join expression like this:


from		Parser.dbo.Parser_OCR OCR
Inner Join	Parser.dbo.Parser_OJB OJB on OCR.ORDER_NO_OCR = OJB.ORDER_NO_OJB
cross Join	(
			select 1 as p union all
			select 4 union all
			select 7 union all
			select 10 union all
			select 13 union all
			select 16
		) as pstn
Inner JOIN	Parser.dbo.RGV_Codes RGV on substring (OJB.COMPL_CDE_OJB, pstn.p, 3) = CODE -- IS THIS A VARIABLE/PARAMETER OR A COLUMN NAME?
Inner JOIN	CSG_Hist.dbo.SBB_BASE SBB on OCR.HSE_KEY_OCR = SBB.HSE_KEY_SBB
Inner JOIN	CSG_Hist.dbo.HSE_BASE HSE on OCR.HSE_KEY_OCR = HSE.HSE_KEY_HSE

This applies to all tables in your JOIN conditions -- they must be put in there in the proper order. Does this make sense?

Finally, again, for the tenth time or so, look at the part in GREEN -- WHAT IS CODE ????? if it is a column in a table, you need to prefix it with the table name or alias !!!!! We have asked this over and over and you keep ignoring it.

- Jeff
http://weblogs.sqlteam.com/JeffS

kdnichols
Posting Yak Master

232 Posts

Posted - 2007-02-20 : 13:54:48

Hello Dr. Join,

I think I posted earier that it is a Parameter Variable.

If I didn't I apologize.

Thanks again for your help.

Kurt

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-02-20 : 14:01:37

Sorry, missed your post, we posted at about the same time.

All parameters or variables in SQL Server are prefixed with @, otherwise it assumes a column name.

You need to replace CODE with @CODE and be sure that @CODE is defined somewhere (as either a parameter or a variable).

Everything else I wrote still applies.

Please take the time to carefully read every point we are telling you in our posts; it seems that you pick one or two sentences out of 10 and ignore the rest, forcing us to repeat over and over and over ....

- Jeff
http://weblogs.sqlteam.com/JeffS

kdnichols
Posting Yak Master

232 Posts

Posted - 2007-02-20 : 14:48:17

Hello Dr. Join and Peso,

Hats off to you!

I am now returning the grids in this query. I still need to ask my boss where to place the @Code parameter but it is now working.

Seriously Jeff what part of the states are you in can I buy you a pizza for lunch sometime and I would like to do the same for Peso!

Or since Peso is the Patron Saint of this board which church does he want me to donate too?? hehehe but seriously a big thanks!

Jeff, I have taken to heart to study very diligently. I know this exercise was to make me better and it is.

So again except for the @Code parameter and I will find out about that shortly the code now works.

Jeff i apologize if I tested your patience.

Here is the correct code so others can see and learn:



select	OJB.IR_TECH_OJB as TECHN,
		substring(OJB.COMPL_CDE_OJB, pstn.p, 3) as RESCODE,
		OJB.COMPL_DTE_OJB as COMPLET,
		RGV.CodeDes as [DESCRIPTION],
		SBB.RES_NAME_SBB as RES_NAME,
		HSE.ADDR1_HSE as [ADDRESS],
		OJB.JOB_TYP_OJB as JOB,
		OJB.JOB_CLASS_OJB as TYP,
		OCR.ORDER_NO_OCR as NUMB,
		OCR.LS_CHG_OP_ID_OCR as OPR,
		RGV.CommissionAMT as NCommissionAMT,
		COUNT(DISTINCT OCR.ORDER_NO_OCR) as QTYW
from		Parser.dbo.Parser_OCR OCR
Inner Join	Parser.dbo.Parser_OJB OJB on OCR.ORDER_NO_OCR = OJB.ORDER_NO_OJB
cross Join	(
			select 1 as p union all
			select 4 union all
			select 7 union all
			select 10 union all
			select 13 union all
			select 16
		) as pstn
Inner JOIN	Parser.dbo.RGV_Codes RGV on substring (OJB.COMPL_CDE_OJB, pstn.p, 3) = CODE 
Inner JOIN	CSG_Hist.dbo.SBB_BASE SBB on OCR.HSE_KEY_OCR = SBB.HSE_KEY_SBB
Inner JOIN	CSG_Hist.dbo.HSE_BASE HSE on OCR.HSE_KEY_OCR = HSE.HSE_KEY_HSE
WHERE	OJB.IR_TECH_OJB between 950 and 999
		and OJB.COMPL_DTE_OJB BETWEEN '2007-01-09' AND '2007-01-22'
		and OCR.PRIN_OCR = 8600
GROUP BY	OJB.IR_TECH_OJB,
		substring(OJB.COMPL_CDE_OJB, pstn.p, 3),
		OJB.COMPL_DTE_OJB,
		RGV.CodeDes,
		SBB.RES_NAME_SBB,
		HSE.ADDR1_HSE,
		OJB.JOB_TYP_OJB,
		OJB.JOB_CLASS_OJB,
		OCR.ORDER_NO_OCR,
		OCR.LS_CHG_OP_ID_OCR,
		RGV.CommissionAMT

AGAIN BIG THANKS TO PESO AND DR. JOIN!

HAVE A GREAT DAY!

KURT

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-20 : 15:12:05

I think a INNER JOIN to no other table and only a substring check, makes it equal to a CROSS JOIN.
Do you concur, Jeff?

quote:
Inner JOIN Parser.dbo.RGV_Codes RGV on substring (OJB.COMPL_CDE_OJB, pstn.p, 3) = @CODE

Peter Larsson
Helsingborg, Sweden

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-02-20 : 15:27:17

Yup. That gets my vote as a cross-join.

STAR SCHEMAS ARE NOT DATA WAREHOUSES!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-20 : 15:30:25

Kurt, try this and see if you get the same result

select		OJB.IR_TECH_OJB as TECHN,
		substring(OJB.COMPL_CDE_OJB, pstn.p, 3) as RESCODE,
		OJB.COMPL_DTE_OJB as COMPLET,
		RGV.CodeDes as [DESCRIPTION],
		SBB.RES_NAME_SBB as RES_NAME,
		HSE.ADDR1_HSE as [ADDRESS],
		OJB.JOB_TYP_OJB as JOB,
		OJB.JOB_CLASS_OJB as TYP,
		OCR.ORDER_NO_OCR as NUMB,
		OCR.LS_CHG_OP_ID_OCR as OPR,
		RGV.CommissionAMT as NCommissionAMT,
		COUNT(DISTINCT OCR.ORDER_NO_OCR) as QTYW
from		Parser.dbo.Parser_OCR as OCR
Inner Join	Parser.dbo.Parser_OJB as OJB on OCR.ORDER_NO_OCR = OJB.ORDER_NO_OJB
cross Join	(
			select 1 as p union all
			select 4 union all
			select 7 union all
			select 10 union all
			select 13 union all
			select 16
		) as pstn
Cross JOIN	Parser.dbo.RGV_Codes as RGV
Inner JOIN	CSG_Hist.dbo.SBB_BASE as SBB on OCR.HSE_KEY_OCR = SBB.HSE_KEY_SBB
Inner JOIN	CSG_Hist.dbo.HSE_BASE as HSE on OCR.HSE_KEY_OCR = HSE.HSE_KEY_HSE
WHERE		substring(OJB.COMPL_CDE_OJB, pstn.p, 3) = @CODE
		and OJB.IR_TECH_OJB between 950 and 999
		and OJB.COMPL_DTE_OJB BETWEEN '2007-01-09' AND '2007-01-22'
		and OCR.PRIN_OCR = 8600
GROUP BY	OJB.IR_TECH_OJB,
		substring(OJB.COMPL_CDE_OJB, pstn.p, 3),
		OJB.COMPL_DTE_OJB,
		RGV.CodeDes,
		SBB.RES_NAME_SBB,
		HSE.ADDR1_HSE,
		OJB.JOB_TYP_OJB,
		OJB.JOB_CLASS_OJB,
		OCR.ORDER_NO_OCR,
		OCR.LS_CHG_OP_ID_OCR,
		RGV.CommissionAMT

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-20 : 15:32:10

Or this? This might fail because I have made some assumptions...

select		OJB.IR_TECH_OJB as TECHN,
		substring(OJB.COMPL_CDE_OJB, pstn.p, 3) as RESCODE,
		OJB.COMPL_DTE_OJB as COMPLET,
		RGV.CodeDes as [DESCRIPTION],
		SBB.RES_NAME_SBB as RES_NAME,
		HSE.ADDR1_HSE as [ADDRESS],
		OJB.JOB_TYP_OJB as JOB,
		OJB.JOB_CLASS_OJB as TYP,
		OCR.ORDER_NO_OCR as NUMB,
		OCR.LS_CHG_OP_ID_OCR as OPR,
		RGV.CommissionAMT as NCommissionAMT,
		COUNT(DISTINCT OCR.ORDER_NO_OCR) as QTYW
from		Parser.dbo.Parser_OCR as OCR
Inner Join	Parser.dbo.Parser_OJB as OJB on OCR.ORDER_NO_OCR = OJB.ORDER_NO_OJB
cross Join	(
			select 1 as p union all
			select 4 union all
			select 7 union all
			select 10 union all
			select 13 union all
			select 16
		) as pstn
INNER JOIN	Parser.dbo.RGV_Codes as RGV on substring(OJB.COMPL_CDE_OJB, pstn.p, 3) = RGV.CODE
Inner JOIN	CSG_Hist.dbo.SBB_BASE as SBB on OCR.HSE_KEY_OCR = SBB.HSE_KEY_SBB
Inner JOIN	CSG_Hist.dbo.HSE_BASE as HSE on OCR.HSE_KEY_OCR = HSE.HSE_KEY_HSE
WHERE		substring(OJB.COMPL_CDE_OJB, pstn.p, 3) = @CODE
		and OJB.IR_TECH_OJB between 950 and 999
		and OJB.COMPL_DTE_OJB BETWEEN '2007-01-09' AND '2007-01-22'
		and OCR.PRIN_OCR = 8600
GROUP BY	OJB.IR_TECH_OJB,
		substring(OJB.COMPL_CDE_OJB, pstn.p, 3),
		OJB.COMPL_DTE_OJB,
		RGV.CodeDes,
		SBB.RES_NAME_SBB,
		HSE.ADDR1_HSE,
		OJB.JOB_TYP_OJB,
		OJB.JOB_CLASS_OJB,
		OCR.ORDER_NO_OCR,
		OCR.LS_CHG_OP_ID_OCR,
		RGV.CommissionAMT

Peter Larsson
Helsingborg, Sweden

kdnichols
Posting Yak Master

232 Posts

Posted - 2007-02-20 : 15:33:07

Hello Peter,

I am getting this error messge:

Msg 137, Level 15, State 2, Line 26
Must declare the scalar variable "@CODE".

Thanks,

Kurt

kdnichols
Posting Yak Master

232 Posts

Posted - 2007-02-20 : 15:38:27

Hello Peter,

I have asked my supervisor to define @CODE since it is a Parameter.

I should have that soon.

Again many thanks for your help.

Kurt

kdnichols
Posting Yak Master

232 Posts

Posted - 2007-02-20 : 16:53:29

Hello Peter,

Still waiting.

If you were to take a guess where would you put it?

Just curious.

Thanks,

Kurt

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-20 : 17:16:36

Kurt, you have to declare it before you use it, just like in any other language.

DECLARE ...

SELECT ...

Tara Kizer

kdnichols
Posting Yak Master

232 Posts

Posted - 2007-02-20 : 17:41:40

Hello Peso and Jeff,

CODE is actually a field in the Parser.dbo.RGV_Codes table and it is actually shown as Codes.

So then I don't believe the parameter query is correct.

What is the correct syntax now?

Thanks for Peso's and Jeff's help.

Kurt

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-20 : 17:42:52

Kurt,

I thought you said your query was working. Please post your current query and the current error.

Tara Kizer

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources