| Author |
Topic |
|
meef
Posting Yak Master
113 Posts |
 Posted - 2010-08-04 : 13:58:37
|
| I'm trying to use this line in my stored procedure to get a shipping period, but it's giving me this error:"Cannot find either column "dbo" or the user-defined function or aggregate "dbo.padl", or the name is ambiguous."The line in question is this:substring(LTRIM(str(year(shdt))),3,2)+dbo.padl(ltrim(str(month(shdt))),'0',2) as [Per_Ship]I'm not sure what I need to add to the dbo. section to make it work, anyone know? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
meef
Posting Yak Master
113 Posts |
Posted - 2010-08-04 : 14:15:54
|
| Hmm, not sure, I was just sent the snippet of code to put in my stored procedure, it was used in other SPs I think. I guess I can try to find where it originally was used to see if there's anything else needed. |
 |
|
|
ewomack
Starting Member
33 Posts |
Posted - 2010-08-05 : 09:50:27
|
| Can you find padl anywhere? Maybe it needs a cross-server reference? You need to find that thing and identify it, then you should be able to properly refer to it. Without that info it's hard to know what to do.Ed Womackwww.getmilked.com |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-08-05 : 10:03:53
|
I believe instead of this:dbo.padl(ltrim(str(month(shdt))),'0',2)you can use thisright('00'+ltrim(str(month(shdt))),2)because it looks like padl has only the job to add leading zeroes to a month so that august '8' becomes '08' for eaxmple.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2010-08-05 : 12:09:31
|
| This looks like you are casting temporal data into strings, then playing with the strings in the worst possible way. That is what you have to do in COBOL and never in SQL. We have temporal data types. Good programmers NEVER format data in the databases because it violates the principle of a tiered architecture. What are you actually trying to do?--CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
meef
Posting Yak Master
113 Posts |
Posted - 2010-08-05 : 13:26:51
|
| I did find where the function was and set it to use the database I'm using, so I got rid of the error. That piece of code just created a "period shipped" line. When you put a date range in, it takes the year and month and formats them like "1004" for April 2010. I didn't make any of it, I'm just trying to get it working with another stored procedure and I'm still very new at it all.Now I have this error: All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.I don't know if you want me to paste the whole SP as it's pretty long. Let me know.EDIT: I think I figured it out... Yep, query is running now. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-08-09 : 04:48:44
|
| 1 As said, do formation at front end application2 Post the code used in the procedure. There should be equal number of columns in union / union all is usedMadhivananFailing to plan is Planning to fail |
|
|
|
|
Help with one line