|
I need to use a function inside my database that need have the option Concat NULL yields NULL ON, however my database doesn't use that, so , I need to ensure that'll be ON when the function executes.
But seens not possible to define that inside the function ( Set .. On, is not allowed ).
There's any way to do that? 
|
|
|
|
|
|
So code around it, ISULL(FieldName,'') will do for text fields
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Microsoft have already stated[^] that the option to turn CONCAT_NULL_YIELDS_NULL off will be removed in a future version. You should probably be looking to update the database so that it works with this setting turned on.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Yeah, we should start think about it here, thank you. The problem is that we're using that on the return of a XML. The error are on the XML return. to solve, before call the function we got to use this.
SET CONCAT_NULL_YIELDS_NULL ON;
GO
This is our function
CREATE FUNCTION Function_StripHTML(@Text NVARCHAR(MAX), @BreakLine BIT)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @XML AS XML
IF @BreakLine = 1
BEGIN
SET @Text= REPLACE(REPLACE(REPLACE(@Text,'<br>', CHAR(13)), '<br/>',CHAR(13)), '<br />',CHAR(13))
END
ELSE
BEGIN
SET @Text= REPLACE(REPLACE(@Text, '<br/>','<br>'), '<br />','<br>')
END
SET @XML = CAST(('<x>' + REPLACE(REPLACE(dbo.Function_RemoveInvalidCharacters(@Text), '&', '&'), '<', '</x><x>')+'</x>') AS XML)
SELECT
@Text=
(
SELECT
CASE WHEN LEFT(N.value('.', 'VARCHAR(MAX)'), 3) = 'br>' THEN
'<' + N.value('.', 'VARCHAR(MAX)')
ELSE
STUFF
(
N.value('.', 'VARCHAR(MAX)')
, 1
, CHARINDEX('>', N.value('.', 'VARCHAR(MAX)'))
, ''
)
END
FROM
@XML.nodes('x') AS T(N)
FOR XML PATH('')
, TYPE
).value('.', 'VARCHAR(MAX)')
RETURN LTRIM(RTRIM(@Text))
END
GO
I'm sorry if the code looks confuse, was made for a coworker here. 
|
|
|
|
|
from microsoft access 2003 link SQLEXPRESS 2005 saw the other table normal, but i can not see the table in the form of DBO.* ? why is that ? no way to fix it? (Note that I'm using SQL Express 2005)
|
|
|
|
|
Member 2458467 wrote: in the form of DBO.*
Is your user a sysadmin?
For example, if user Andrew is a member of the sysadmin fixed server role and creates a table T1, T1 belongs to dbo and is qualified as dbo.T1, not as Andrew.T1. Conversely, if Andrew is not a member of the sysadmin fixed server role but is a member only of the db_owner fixed database role and creates a table T1, T1 belongs to Andrew and is qualified as Andrew.T1. The table belongs to Andrew because he did not qualify the table as dbo.T1.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
If there are 4 attributes: Candidate name, Phone No., Address, Skills name. design 3 tables and write a sql query for sql server to display the candidate details on the basis of their skills name.
(For example in any job portal, searching the candidate details on the basis of their skills name)
I m new to sql server db, Please provide me the answer as soon as possible. Thank you.
|
|
|
|
|
Have you tried anything jet? Are you stuck with your solution?
Let see us your code - and probably fix it!
(Sorry but we are not here to do your homework! We have a job to do...)
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is (V).
|
|
|
|
|
You cut/pasted this from your assignment document, too lazy to even change the wording so it sounded genuine.
The people here are volunteers, mostly professional developers who are willing to help you to learn and benefit from their experience. As has been stated, do some research, try something and when you have a genuine problem come back and ask for help.
As for being new, getting the solution from a forum will insure you stay new and hopefully unused!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Member 10392875 wrote: Please provide me the answer as soon as possible.
Your answer is that you should do your own homework.
|
|
|
|
|
That would take me about ten minutes and I could do so in any of several different database systems.
You posted nearly a full day ago; what have you accomplished in that time?
|
|
|
|
|
I'm not going to do your homework, but I can point you in the right direction: Normalization and Joins.
|
|
|
|
|
Hi friends. I have 2 coulmns in a table (RealPersonID and CompanyID). They are foreign keys. My problem is that either of them is always 0 and the other one is none zero.
So i want to do that in only one query:
x=(Select CompanyID from Table1)
y=(Select RealPersonID from Table1)
if x!=0 (select companyName from Companies where companyID=x)
else (select personName from Person where personID=y)
|
|
|
|
|
Non tested!
Select IsNull(C.CompanyName, P.PersonName) Contact
From LinkTable L
Left Join ComapanyTable C on C.CompanyID = L.CompanyID
Left Join PersonTable P on P.RealPersonID = L.RealPersonID
This may help you understand joins Visual Representation of SQL Joins[^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I will try my best to describe this, it's the same PHP project from my post below, my next biggest hurdle to jump here.
I'm trying to solve this through SQL, without having to change much of the the PHP Code
I have this line of code, in which it takes a query from the calling class which passes it to this class that contains the line below. Sort of crazy or genius.
$lc_query = '(SELECT SQL_CALC_FOUND_ROWS ' . substr($pc_query, 6) . ') LIMIT ' . (($ln_page - 1) * $ln_pagesize) . ', ' . $ln_pagesize;
So I changed the code from using the old mysql handle to the new PDO. I could not find a way to pass an array of parameter tokens to this class, because the token count could be any number of them. The classic trying to bind parameters in a loop failure.
Original way:
Class 1, generate the query, and passes it to class 2
Class 2, calculate page count, then uses the line above to wrap the passed in query and transmit it.
So I did this:
class 1: generates the dynamic query, makes a PDO instance, bind parameters, pass PDO to class 2
class 2: receives the PDO handle, and executes.
So far so good, it works good, stable, but I need the calculations in the Class 2 for page indexing.
I can't figure out how to reintegrate the above statement in class 2 where the page numbers are generated.
I was hoping I would be able to extract the query out of the PDO object, and reconstruct it. Plus I don't think the line above works in PDO.
If you have any thoughts, comments or past experience with this, I'd appreciate it!
Class 1
$cmd_sp = $PDOClass->QUERY($lc_query);
if ($b_mfgName) $cmd_sp->bindParam(':mfgName', $lc_mfg, PDO::PARAM_STR);
if ($b_deptID) $cmd_sp->bindParam(':deptID', $pn_dept_id, PDO::PARAM_INT);
if ($b_partnerID) $cmd_sp->bindParam(':partnerID', $go_sitedict['partner_id'], PDO::PARAM_INT);
$SPClass = new SPClass(
$cmd_sp,
$cmd_sp_mfg,
FALSE,
'',
$ll_debug
);
Class 2:
$lc_query = '(SELECT SQL_CALC_FOUND_ROWS ' . substr($pc_query, 6) . ') LIMIT ' . (($ln_page - 1) * $ln_pagesize) . ', ' . $ln_pagesize;
try {
$cmd_showProduct->execute();
}
|
|
|
|
|
I'm upgrading a PHP website that uses MySQL to use PDO, pretty big project but I have this one SQL Query that won't play ball with me. I'm no expert in SQL Queries, my biggest weakness, but I'm just easter egg hunting on this one.
I get this error Unknown column 'p.prod_id' in 'on clause'
The script works on the customers production system, but it has never worked on my development system that I had to build to work on the project. It was originally written for the original mysql handle or object. I forget what version of MySQL they are using, but I'm using the lastest windows version.
I'm Interested in you just looking at it, and if you see or know something, then perhaps you can point me in the right direction.
I came to the conclusion that perhaps its poorly written, and somehow it worked, but in PDO, which is more strict, it won't fly.
SELECT DISTINCT p.prod_id, p.model, p.showpricing, p.image_file, p.image_width, p.image_height, p.image_border, p.name, m.manufacturer,
CASE WHEN sp.price IS NULL THEN 0 ELSE sp.price END AS list_price,
CASE WHEN sp.price IS NULL OR ssp.discount IS NULL THEN 0 ELSE sp.price - ssp.discount END AS sale_price,
CASE WHEN NOW() BETWEEN p.mfgrebatestart AND p.mfgrebateend THEN p.mfgrebate ELSE 0 END AS mfgrebate
FROM comm_product p, comm_manufacturer m
INNER JOIN comm_dept_prod dp ON p.prod_id = dp.prod_id
INNER JOIN comm_partners_products pa ON p.prod_id = pa.prod_id
LEFT JOIN comm_sku_price sp ON p.default_sku_id = sp.sku_id AND NOW()
BETWEEN sp.date_start AND sp.date_end
LEFT JOIN comm_sku_sale_price ssp ON p.default_sku_id = ssp.sku_id AND NOW()
BETWEEN ssp.date_start AND ssp.date_end
WHERE pa.p_id = :partnerID
AND p.mfg_id = m.mfg_id
AND p.avail_type < 3 AND p.image_file > ''
AND p.model NOT LIKE 'EBAY-%'
AND p.dept_ad = 1
AND dp.dept_id = :deptID
ORDER BY rand() LIMIT 4
comm_product, I copied the columns from mysql workbench
table comm_product
`prod_id`, `model`, `name`, `list_order`,
`image_file`, `image_width`, `image_height`, `image_border`,
`big_image_file`, `big_image_width`, `big_image_height`, `big_image_border`,
`feature_image_file`, `mfgrebate`, `mfgrebatestart`, `mfgrebateend`,
`taxable`, `comp_name`, `comp_price`, `item_desc`,
`attr_label1`, `attr_label2`, `attr_label3`, `attr_label4`,
'attr_label5`, `special_note`, `manager_note`, `page_id`,
`pdf_file`, `edittrack`, `date_created`, `time_created`,
`user_created`, `date_edited`, `user_edited`, `mfg_id`,
`condition`, `mfgrebatepost`, `mfgrebatefile`, `mfgrebatetype`,
`default_sku_id`, `preselect_default_sku`, `clean_pdf`, `avail_type`,
`showpricing`, `free_shipping`, `window_title`, `meta_description`,
`meta_keywords`, `page_header`, `dept_ad`, `seoname`,
`feed_title`, `feed_upc`, `feed_product_type`
|
|
|
|
|
First guess.. does prod_id exist in comm_product? Since p.prod_id is being referenced and p is the alias for comm_product, I'd start there.
|
|
|
|
|
Thanks for looking at it. I did check that. I went ahead and edited my post and added that table to it for inspection. I'll probably need to add all the tables.
|
|
|
|
|
What I can see is a mixture of ANSI joins and an implicit join. And I can understand if the optimizer pukes on that.
Try using all ANSI Joins:
SELECT DISTINCT p.prod_id, p.model, p.showpricing, p.image_file, p.image_width, p.image_height, p.image_border, p.name, m.manufacturer,
CASE WHEN sp.price IS NULL THEN 0 ELSE sp.price END AS list_price,
CASE WHEN sp.price IS NULL OR ssp.discount IS NULL THEN 0 ELSE sp.price - ssp.discount END AS sale_price,
CASE WHEN NOW() BETWEEN p.mfgrebatestart AND p.mfgrebateend THEN p.mfgrebate ELSE 0 END AS mfgrebate
FROM comm_product p
INNER JOIN comm_manufacturer m ON p.mfg_id = m.mfg_id
INNER JOIN comm_dept_prod dp ON p.prod_id = dp.prod_id
INNER JOIN comm_partners_products pa ON p.prod_id = pa.prod_id
LEFT JOIN comm_sku_price sp ON p.default_sku_id = sp.sku_id AND NOW()
BETWEEN sp.date_start AND sp.date_end
LEFT JOIN comm_sku_sale_price ssp ON p.default_sku_id = ssp.sku_id AND NOW()
BETWEEN ssp.date_start AND ssp.date_end
WHERE pa.p_id = :partnerID
AND p.avail_type < 3 AND p.image_file > ''
AND p.model NOT LIKE 'EBAY-%'
AND p.dept_ad = 1
AND dp.dept_id = :deptID
ORDER BY rand() LIMIT 4
Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers.
Buckminster Fuller
|
|
|
|
|
Wow thanks.
I ran it in the mysql workbence. it didn't produce an error and ran clean, but it didn't product any rows on :partner = 1000 and :dept = 52.
But it's a step closer.
Oh wait, changed the dept to 54, and got rows!
Cool.
Let me give it a test run in the code, should work fine. I may have another one today
This was way over my head here. It's hard to be a programmer, art guy, HTML and CSS all at once. The SQL stuff seems to be an expertise on a higher level.
Thanks Jorgen, your the best!
I don't understand the ANSI JOIN versus the implicit, guess I can Google that to learn exactly what was wrong.
|
|
|
|
|
jkirkerx wrote: Wow thanks
My pleasure.
jkirkerx wrote: I don't understand the ANSI JOIN versus the implicit, guess I can Google that to
learn exactly what was wrong.
Yes, or even better, in this case, Wikipedia[^].
Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers.
Buckminster Fuller
|
|
|
|
|
I plugged it in, pretty sure I got the translation right because I don't get a SQL error and the SQL Query matches when I print it out on the screen.
It's really close, but I guess it's suppose to produce a result set of 4 rows on almost every dept ID
This is out of my league here. oh well.
Thanks for help, At least I know my code changes work, and I can do testing with it.
|
|
|
|
|
hi.i want write insert procedure in access.
|
|
|
|
|
And your QUESTION is!
I think Access has a macro function so step through the insert while recording a macro and inspect the SQL code generated.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
