February 19, 2014 at 2:57 pm
Hi,
I am using SQL Server 2008 Enterprise edition 64 bit on Windows serer 2008 enterprise edition 64 bit.
The below query works fine. I am trying to rewrite the code without using the sub query. In the end i should get the same results for both the query which we are going to rewrite and the below mentioned query.
Can any help me please.
SELECT
t1.DOCUMENT_NO,
RTRIM(CAST(t1."ENVIRONMENT_CD" AS VARCHAR(5))) + '*' + RTRIM(CAST(t1."ORDER_NO" AS VARCHAR(25)))
+
CASE WHEN (SELECT COUNT(DISTINCT S1.TEST_REPORTING_MATERIAL_SID)
FROM dbo.TBLTEST S1 ON S1.DOCUMENT_NO = T1.DOCUMENT_NO
WHERE S1.TEST_REPORTING_MATERIAL_SID > 0
AND S1.COMPANY_CD = T1.COMPANY_CD AND S1.INVOICE_SEQ_NO = T1.INVOICE_SEQ_NO
AND S1.DOCUMENT_ITEM_NO = T1.DOCUMENT_ITEM_NO
AND S1.ORDER_NO = T1.ORDER_NO) > 1 THEN
RTRIM(CAST(t1."TEST_REPORTING_MATERIAL_SID" AS VARCHAR(10)))
ELSE
'*-'
END as sum_key,
t1.SALES_ANALYSIS_CD
FROM
dbo.TBLTEST_ALL T1
WHERE
t1.TIME_SID >= 20001001;
I tried to use a left outer Join , it did not work out. I tried to take the subquery and assign it to a variable and use in the above mentioned query. But i am not getting the same results.
I don't want to use the above query so i am planning to rewrite the code.
Thank You,
February 19, 2014 at 3:01 pm
sql2k8 (2/19/2014)
Hi,I am using SQL Server 2008 Enterprise edition 64 bit on Windows serer 2008 enterprise edition 64 bit.
The below query works fine. I am trying to rewrite the code without using the sub query. In the end i should get the same results for both the query which we are going to rewrite and the below mentioned query.
Can any help me please.
SELECT
t1.DOCUMENT_NO,
RTRIM(CAST(t1."ENVIRONMENT_CD" AS VARCHAR(5))) + '*' + RTRIM(CAST(t1."ORDER_NO" AS VARCHAR(25)))
+
CASE WHEN (SELECT COUNT(DISTINCT S1.TEST_REPORTING_MATERIAL_SID)
FROM dbo.TBLTEST S1 ON S1.DOCUMENT_NO = T1.DOCUMENT_NO
WHERE S1.TEST_REPORTING_MATERIAL_SID > 0
AND S1.COMPANY_CD = T1.COMPANY_CD AND S1.INVOICE_SEQ_NO = T1.INVOICE_SEQ_NO
AND S1.DOCUMENT_ITEM_NO = T1.DOCUMENT_ITEM_NO
AND S1.ORDER_NO = T1.ORDER_NO) > 1 THEN
RTRIM(CAST(t1."TEST_REPORTING_MATERIAL_SID" AS VARCHAR(10)))
ELSE
'*-'
END as sum_key,
t1.SALES_ANALYSIS_CD
FROM
dbo.TBLTEST_ALL T1
WHERE
t1.TIME_SID >= 20001001;
I tried to use a left outer Join , it did not work out. I tried to take the subquery and assign it to a variable and use in the above mentioned query. But i am not getting the same results.
I don't want to use the above query so i am planning to rewrite the code.
Thank You,
Sure there are lots of people around here that can help. All you need to do is post the ddl and some sample data along with the desired output.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 19, 2014 at 3:12 pm
sql2k8 (2/19/2014)
The below query works fine. I am trying to rewrite the code without using the sub query. In the end i should get the same results for both the query which we are going to rewrite and the below mentioned query.
If it works fine, why do you want to change it?
Have you considered using APPLY?
February 19, 2014 at 3:23 pm
I have to rewrite the code in another language. This correlated subquery is not working in the another language. So i am trying to rewrite the code without correlated sub query.
February 19, 2014 at 3:25 pm
Hi ,
I cannot post the output of this data. It is confidential. If you can share me the logic i will try and see if it is working.
I need some suggestions in logic.
Thank You,
February 19, 2014 at 3:30 pm
sql2k8 (2/19/2014)
Hi ,I cannot post the output of this data. It is confidential. If you can share me the logic i will try and see if it is working.
I need some suggestions in logic.
Thank You,
That's why it would be sample data. Nobody asked or wants to see real data.
How can we possibly try to figure out a way to get data out of a table that we don't have? We can't test it, we can't even know if it is close.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 19, 2014 at 3:31 pm
sql2k8 (2/19/2014)
I have to rewrite the code in another language. This correlated subquery is not working in the another language. So i am trying to rewrite the code without correlated sub query.
What does that mean? Are you getting an error message? Are you trying to move this out of sql server to another DBMS? I am curious what other language you would be using. Would it not still be t-sql?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 19, 2014 at 3:44 pm
sql2k8 (2/19/2014)
I have to rewrite the code in another language. This correlated subquery is not working in the another language. So i am trying to rewrite the code without correlated sub query.
I'm not aware of any SQL language that won't support correlated subqueries. You might have a problem with the quotes.
February 19, 2014 at 4:44 pm
Luis Cazares (2/19/2014)
sql2k8 (2/19/2014)
I have to rewrite the code in another language. This correlated subquery is not working in the another language. So i am trying to rewrite the code without correlated sub query.I'm not aware of any SQL language that won't support correlated subqueries. You might have a problem with the quotes.
PostGres had a problem for a bit, if my memory hasn't completely short circuited from work I did 6 years ago. I remember creating a truckload of functions for that RDBMS.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 19, 2014 at 5:08 pm
Hi,
I am converting the code into Netezza. Netezza doesnt like the correlated sub queries.
Thank You,
February 20, 2014 at 3:04 pm
The "ON" clause after the "FROM" is not valid. That condition should just be in the WHERE conditions, since it's a correlated condition.
I don't know about Netezza, but the original query you posted won't work in SQL Server itself even. After correcting that, you might want to re-test.
I'm working on converting the query to use a LOJ as well, but I thought I'd make you aware of that first.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 20, 2014 at 3:11 pm
Maybe this?!:
SELECT
t1.DOCUMENT_NO,
RTRIM(CAST(t1."ENVIRONMENT_CD" AS VARCHAR(5))) + '*' + RTRIM(CAST(t1."ORDER_NO" AS VARCHAR(25)))
+ CASE WHEN TEST_REPORTING_MATERIAL_SID__COUNT > 1 THEN
RTRIM(CAST(t1."TEST_REPORTING_MATERIAL_SID" AS VARCHAR(10)))
ELSE
'*-'
END as sum_key,
t1.SALES_ANALYSIS_CD
FROM
dbo.TBLTEST_ALL T1
LEFT OUTER JOIN (
SELECT
S1.DOCUMENT_NO,
S1.COMPANY_CD,
S1.INVOICE_SEQ_NO,
S1.DOCUMENT_ITEM_NO,
S1.ORDER_NO,
COUNT(DISTINCT S1.TEST_REPORTING_MATERIAL_SID) AS TEST_REPORTING_MATERIAL_SID__COUNT
FROM dbo.TBLTEST S1
WHERE
S1.TEST_REPORTING_MATERIAL_SID > 0
GROUP BY
S1.DOCUMENT_NO,
S1.COMPANY_CD,
S1.INVOICE_SEQ_NO,
S1.DOCUMENT_ITEM_NO,
S1.ORDER_NO
) AS S1_SUBQ ON
S1_SUBQ.DOCUMENT_NO = T1.DOCUMENT_NO AND
S1_SUBQ.COMPANY_CD = T1.COMPANY_CD AND
S1_SUBQ.INVOICE_SEQ_NO = T1.INVOICE_SEQ_NO AND
S1_SUBQ.DOCUMENT_ITEM_NO = T1.DOCUMENT_ITEM_NO AND
S1_SUBQ.ORDER_NO = T1.ORDER_NO
WHERE
t1.TIME_SID >= 20001001;
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 21, 2014 at 9:43 am
Luis Cazares (2/19/2014)
sql2k8 (2/19/2014)
I have to rewrite the code in another language. This correlated subquery is not working in the another language. So i am trying to rewrite the code without correlated sub query.I'm not aware of any SQL language that won't support correlated subqueries. You might have a problem with the quotes.
I can think of one
:sick:
February 21, 2014 at 10:09 am
sqldriver (2/21/2014)
Luis Cazares (2/19/2014)
sql2k8 (2/19/2014)
I have to rewrite the code in another language. This correlated subquery is not working in the another language. So i am trying to rewrite the code without correlated sub query.I'm not aware of any SQL language that won't support correlated subqueries. You might have a problem with the quotes.
I can think of one
:sick:
Why do they put those products on the market? And why do people insist on using them?
February 21, 2014 at 10:25 am
Luis Cazares (2/21/2014)
sqldriver (2/21/2014)
Luis Cazares (2/19/2014)
sql2k8 (2/19/2014)
I have to rewrite the code in another language. This correlated subquery is not working in the another language. So i am trying to rewrite the code without correlated sub query.I'm not aware of any SQL language that won't support correlated subqueries. You might have a problem with the quotes.
I can think of one
:sick:
Why do they put those products on the market? And why do people insist on using them?
Some people just want a database that does less, Luis. Jeez 😉
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply