March 9, 2011 at 5:27 am
Hello,
I have a business requirement to include a condition in the where clause. The condition is:
When comparing the input parameter TRADE, it needs to be compared to the
TRADE column in MG_BOOKING table. If this is NULL, then compare it to the TRADE column in MG_BILL table.
Also, if the TRADE in MG_BOOKING table is not NULL, then the parameter TRADE should match the TRADE columns in both the MG_BOOKING and MG_BILL tables.
The query that I have created for the above condition (still incomplete) is:
WHERE (MG_BOOKING.TRADE IN (select distinct Item From dbo.Split(@trades,','))
OR MG_BILL.TRADE IN (select distinct Item From dbo.Split(@trades,',')))
Could someone please suggest a solution for the business requirement to fit into the query above ?
Thanks and Regards,
Paul
March 9, 2011 at 5:45 am
Could you post table definitions, sample data and expected result please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 9, 2011 at 6:53 am
GilaMonster (3/9/2011)
Could you post table definitions, sample data and expected result please?
Now this is really frustrating. Without even reading the question, you have given a template answer. This way you block the post from others without even answering yourself.
I dont think you need to include table definitions or sample data everytime you ask a question. It always depends on the type of question asked.
I have multiple tables being used for the stored procedure I have created. The query is part of the stored procedure and its useless to include all the table definitions that are not even related to the question.
The question I asked is self-explanatory and just needs to be changed to a sql statement.
March 9, 2011 at 7:06 am
pwalter83 (3/9/2011)
GilaMonster (3/9/2011)
Could you post table definitions, sample data and expected result please?Now this is really frustrating. Without even reading the question, you have replied in a template answer. This way you block the post from others without even answering yourself.
I read the question. Three times. After three reads I still could not fully understand what you wanted.
If you want my untested guess as to the solution for your problem, sure, I'll try based on what you wrote. If you want a tested solution, one that does exactly what you want first time, not after 10 revisits and retries then please post what I asked for.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 9, 2011 at 7:32 am
GilaMonster (3/9/2011)
pwalter83 (3/9/2011)
GilaMonster (3/9/2011)
Could you post table definitions, sample data and expected result please?Now this is really frustrating. Without even reading the question, you have replied in a template answer. This way you block the post from others without even answering yourself.
I read the question. Three times. After three reads I still could not fully understand what you wanted.
If you want my untested guess as to the solution for your problem, sure, I'll try based on what you wrote. If you want a tested solution, one that does exactly what you want first time, not after 10 revisits and retries then please post what I asked for.
I admit that the question is confusing. But based on what I wrote can you please provide some hint or some solution. Based on your solution, I can further try to refine it on my own end. This would be really helpful as it would at least give me a start. I am really sorry about what I wrote.
Thanks and Regards,
Paul
March 9, 2011 at 7:48 am
Can you at least post the definitions of the tables that are involved in this query - MG_BOOKING and MG_BILL (create table statement)? Data types is what I'm most interested in. At least then I can get you a solution that parses even if not completely correct.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 9, 2011 at 7:58 am
GilaMonster (3/9/2011)
Can you at least post the definitions of the tables that are involved in this query - MG_BOOKING and MG_BILL (create table statement)? Data types is what I'm most interested in. At least then I can get you a solution that parses even if not completely correct.
Hi Gail,
Thanks a lot for your reply. The table definition for the MG_BOOKING table is:
CREATE TABLE [dbo].[MG_BOOKING](
[BOOKING_ID] [numeric](10, 0) NOT NULL,
[BOOKING_NUM] [varchar](15) NULL,
[TRADE] [varchar](10) NULL
table definition for the MG_BILL table is:
CREATE TABLE [dbo].[MG_BILL](
[ID] [int] IDENTITY(1,1) NOT NULL,
[VESSEL_CD] [nvarchar](6) NULL,
[VOYAGE_CD] [nvarchar](6) NULL,
[SERVICE_CD] [nvarchar](10) NULL,
[TRADE] [nvarchar](50) NULL
I hope this would help you.
Thanks and Regards,
Paul
March 9, 2011 at 8:11 am
What is the definition of the input parameter?
At a guess
If input parameter TRADE is singular then
CASE WHEN MG_BOOKING.TRADE IS NULL THEN CASE WHEN MG_BILL.TRADE = @TRADE THEN 1
WHEN MG_BILL.TRADE IS NULL THEN 0
WHEN MG_BOOKING.TRADE = @TRADE AND MG_BILL.TRADE = @TRADE THEN 1
ELSE 0
END = 1
If it is comma separated list then
CASE WHEN MG_BOOKING.TRADE IS NULL THEN SIGN(CHARINDEX(','+MG_BILL.TRADE+',',','+@trades+','))+1
WHEN MG_BILL.TRADE IS NULL THEN 0
ELSE SIGN(CHARINDEX(','+MG_BOOKING.TRADE+',',','+@trades+','))+SIGN(CHARINDEX(','+MG_BILL.TRADE+',',','+@trades+','))
END = 2
Far away is close at hand in the images of elsewhere.
Anon.
March 9, 2011 at 8:13 am
Rough guess...
SELECT DISTINCT Item INTO #Trades FROM dbo.Split(@trades,',');
SELECT ....
FROM ....
INNER JOIN #Trades t ON t.Item = CASE WHEN MG_BOOKING.Trade IS NOT NULL THEN MG_BOOKING.Trade ELSE MGS_CurrentReportingVessels.Trade END
AND (MG_BOOKING.Trade IS NULL OR MG_BOOKING.Trade = MGS_CurrentReportingVessels.Trade)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 9, 2011 at 9:00 am
David Burrows (3/9/2011)
What is the definition of the input parameter?At a guess
If input parameter TRADE is singular then
CASE WHEN MG_BOOKING.TRADE IS NULL THEN CASE WHEN MG_BILL.TRADE = @TRADE THEN 1WHEN MG_BILL.TRADE IS NULL THEN 0
WHEN MG_BOOKING.TRADE = @TRADE AND MG_BILL.TRADE = @TRADE THEN 1
ELSE 0
END = 1
If it is comma separated list then
CASE WHEN MG_BOOKING.TRADE IS NULL THEN SIGN(CHARINDEX(','+MG_BILL.TRADE+',',','+@trades+','))+1WHEN MG_BILL.TRADE IS NULL THEN 0
ELSE SIGN(CHARINDEX(','+MG_BOOKING.TRADE+',',','+@trades+','))+SIGN(CHARINDEX(','+MG_BILL.TRADE+',',','+@trades+','))
END = 2
The only problem is I wish to use this query in the WHERE clause section. Is it possible to achieve this ?
March 9, 2011 at 9:13 am
Hi Gail and David,
Thanks a lot for your answers but would it be possible to convert your solution in a way that they can be included in the WHERE clause section ?
Thanks and Regards,
Paul
March 9, 2011 at 9:13 am
pwalter83 (3/9/2011)
GilaMonster (3/9/2011)
Could you post table definitions, sample data and expected result please?Now this is really frustrating. Without even reading the question, you have given a template answer. This way you block the post from others without even answering yourself.
I dont think you need to include table definitions or sample data everytime you ask a question. It always depends on the type of question asked.
I have multiple tables being used for the stored procedure I have created. The query is part of the stored procedure and its useless to include all the table definitions that are not even related to the question.
The question I asked is self-explanatory and just needs to be changed to a sql statement.
No need to get frustrated pwalter83...this is a VERY common response to original posters that do not supply enough information for others to respond purposefully without [too much] speculation and/or to create a local testbed so they can attempt to work towards a solution. Those of us that cruise these forums in their free time looking to help folks don't ask for much more than a decent starting point.
Please have a look at this article at your convenience: http://qa.sqlservercentral.com/articles/Best+Practices/61537/[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 9, 2011 at 9:22 am
pwalter83 (3/9/2011)
Hi Gail and David,Thanks a lot for your answers but would it be possible to convert your solution in a way that they can be included in the WHERE clause section ?
Thanks and Regards,
Paul
The case statements can be used as is in a WHERE clause ie
WHERE CASE .... END = 1
or
WHERE .....
AND CASE .... END = 1
Gails solution needs a bit more work and probably converted to use EXISTS which can be aaded to a WHERE clause
Far away is close at hand in the images of elsewhere.
Anon.
March 9, 2011 at 9:47 am
David Burrows (3/9/2011)
The case statements can be used as is in a WHERE clause ie
WHERE CASE .... END = 1
or
WHERE .....
AND CASE .... END = 1
Gails solution needs a bit more work and probably converted to use EXISTS which can be aaded to a WHERE clause
Thanks again for your reply, David. I have tried to use your solution in the WHERE clause as below:
------------------------------------
AND CASE WHEN MG_BOOKING.TRADE_CD IS NULL THEN CASE WHEN BA.TRADE IN (select distinct Item From dbo.Split(@trades,',')) THEN 1
WHEN BA.TRADE IS NULL THEN 0
WHEN MG_BOOKING.TRADE IN (select distinct Item From dbo.Split(@trades,',')) AND BA.TRADE IN (select distinct Item From dbo.Split(@trades,',')) THEN 1
ELSE 0
END = 1
---------------------------------------
however, when i try to compile it, this returns an error:
Incorrect syntax near '='. (last line).
Can you please tell what am i doing wrong ?
March 9, 2011 at 9:59 am
Sorry my bad the inner CASE is missing END, try this
AND CASE WHEN MG_BOOKING.TRADE_CD IS NULL THEN
CASE WHEN BA.TRADE IN (select distinct Item From dbo.Split(@trades,',')) THEN 1 ELSE 0 END
WHEN BA.TRADE IS NULL THEN 0
WHEN MG_BOOKING.TRADE IN (select distinct Item From dbo.Split(@trades,','))
AND BA.TRADE IN (select distinct Item From dbo.Split(@trades,',')) THEN 1
ELSE 0
END = 1
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply