August 22, 2008 at 2:25 am
Currentely I am facing a problem while converting Access qurey into sql server
I want to convert following Access query statement into sql
(IIf(Positions.Ticker<>"" And Positions.Ticker NOT IN ("SPY", "LEH", "BA", "DHI", "CTX", "RTH", "PHM", "QQQ", "LEN")
,qryPNLByFund.Ticker Like Positions.Ticker & "*",False)
As I converted it as
case when Positions.Ticker<>'' And Positions.Ticker NOT IN
('SPY', 'LEH', 'BA', 'DHI', 'CTX', 'RTH', 'PHM', 'QQQ', 'LEN') then qryPNLByFund.Ticker LIKE '%' + Positions.Ticker + '%' else 'False' end
It showing following error:
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'LIKE'.
Please help me to solve this problem ASAP.
Thanks in advance.
M.P.Rodge
August 22, 2008 at 2:55 am
Is this a condition for a select statement? If so, you can get rid of the case statement, and have something like:
WHERE Positions.Ticker <> ''
AND Positions.Ticker NOT IN ( 'SPY', 'LEH', 'BA', 'DHI', 'CTX', 'RTH',
'PHM', 'QQQ', 'LEN' )
AND qryPNLByFund.Ticker LIKE '%' + Positions.Ticker + '%'
Regards,
Andras
August 22, 2008 at 3:09 am
Thanks for you reply
I am using this statement in the from clause as
LEFT JOIN qryPNLByFund ON
((case when Positions.Ticker<>'' And Positions.Ticker NOT IN ('SPY', 'LEH', 'BA', 'DHI', 'CTX', 'RTH', 'PHM', 'QQQ', 'LEN')
then qryPNLByFund.Ticker LIKE '%' + Positions.Ticker + '%' else 'False' end) Or
(Positions.Cusip=qryPNLByFund.Ticker) Or
(Positions.[Blackrock Ticker]=qryPNLByFund.Ticker ...............................
August 22, 2008 at 3:41 am
hi
still I am geting problem
I have used the condition in where clause and kept that column name in the join
but still showing error
incorrect syntax near or..
the complete from clause as...............
FROM
maintSubStrategySecurityType RIGHT JOIN (qryBlackrockHaircuts
RIGHT JOIN (((qryBlackrockReturn RIGHT JOIN (qryBlackrockRatings RIGHT JOIN
(tblFXRates RIGHT JOIN (maintBlackrockPortfolio RIGHT JOIN (tblBlackrockPositionsByFund AS
Positions LEFT JOIN tblBlackrockSecurityMaster AS [Security Master] ON Positions.CUSIP=[Security Master].CUSIP)
ON maintBlackrockPortfolio.[Portfolio Name]=Positions.Portfolio) ON tblFXRates.Currency=[Security Master].[Price Currency])
ON qryBlackrockRatings.CUSIP=[Security Master].CUSIP) ON qryBlackrockReturn.CUSIP=[Security Master].CUSIP) LEFT JOIN maintBlackrockCountry
ON [Security Master].[Country Code]=maintBlackrockCountry.[Country Code]) LEFT JOIN qryPNLByFund ON
(Positions.Ticker Or (Positions.Cusip=qryPNLByFund.Ticker) Or
(Positions.[Blackrock Ticker]=qryPNLByFund.Ticker
And (Positions.[Security Group]='OPTION' And Positions.[Security Type]='EQUITY')) Or (Positions.ISIN=qryPNLByFund.ISIN)) AND
(Positions.Portfolio=qryPNLByFund.[Blackrock Portfolio Name])) ON qryBlackrockHaircuts.CUSIP=[Security Master].CUSIP) ON
(maintSubStrategySecurityType.[Sub Strategy]=Positions.[Sub Strategy]) AND (maintSubStrategySecurityType.Strategy=Positions.[Allocation Strategy])
ORDER BY [Security Master].Description
WHERE Positions.Ticker <> ''
AND Positions.Ticker NOT IN ( 'SPY', 'LEH', 'BA', 'DHI', 'CTX', 'RTH',
'PHM', 'QQQ', 'LEN' )
AND qryPNLByFund.Ticker LIKE '%' + Positions.Ticker + '%'ORDER BY [Security Master].Description
August 22, 2008 at 3:46 am
Can you provide the necessary logic for one join to be selected? One option you could use is to LEFT JOIN for each of the three possible joins and pick the one you want in the output:
SELECT p.*, CASE WHEN q1.ticker IS NOT NULL THEN q1.somecolumn ELSE
CASE WHEN q2.ticker IS NOT NULL THEN q2.somecolumn ELSE q3.somecolumn END
END
FROM Positions p
LEFT JOIN qryPNLByFund q1 ON q1.Ticker LIKE '%' + p.Ticker + '%'
AND q1.Ticker <> ''
AND q1.Ticker NOT IN ('SPY', 'LEH', 'BA', 'DHI', 'CTX', 'RTH', 'PHM', 'QQQ', 'LEN')
LEFT JOIN qryPNLByFund q2 ON q2.Ticker = p.Cusip
LEFT JOIN qryPNLByFund q2 ON q2.Ticker = p.[Blackrock Ticker]
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 22, 2008 at 3:47 am
Have a look at the line that you have higlighted:
(Positions.Ticker Or (Positions.Cusip=qryPNLByFund.Ticker) Or
You try to OR together Positions.Ticker with a logical expression, however, I suspect that Positions.Ticker is not a logical expression. You may want to add something like Positions.Ticker NOT NULL, or some equality check.
Regards,
Andras
August 22, 2008 at 4:14 am
When I tried this(By skipping Positions.Ticker in Join) it's working As...
FROM maintSubStrategySecurityType RIGHT JOIN (qryBlackrockHaircuts
RIGHT JOIN (((qryBlackrockReturn RIGHT JOIN (qryBlackrockRatings RIGHT JOIN
(tblFXRates RIGHT JOIN (maintBlackrockPortfolio RIGHT JOIN (tblBlackrockPositionsByFund AS
Positions LEFT JOIN tblBlackrockSecurityMaster AS [Security Master] ON Positions.CUSIP=[Security Master].CUSIP)
ON maintBlackrockPortfolio.[Portfolio Name]=Positions.Portfolio) ON tblFXRates.Currency=[Security Master].[Price Currency])
ON qryBlackrockRatings.CUSIP=[Security Master].CUSIP) ON qryBlackrockReturn.CUSIP=[Security Master].CUSIP) LEFT JOIN maintBlackrockCountry
ON [Security Master].[Country Code]=maintBlackrockCountry.[Country Code]) LEFT JOIN qryPNLByFund ON
--(Positions.Ticker or
(Positions.Cusip=qryPNLByFund.Ticker) Or
(Positions.[Blackrock Ticker]=qryPNLByFund.Ticker
And (Positions.[Security Group]='OPTION' And Positions.[Security Type]='EQUITY')) Or (Positions.ISIN=qryPNLByFund.ISIN)--)
AND
(Positions.Portfolio=qryPNLByFund.[Blackrock Portfolio Name])) ON qryBlackrockHaircuts.CUSIP=[Security Master].CUSIP) ON
(maintSubStrategySecurityType.[Sub Strategy]=Positions.[Sub Strategy]) AND (maintSubStrategySecurityType.Strategy=Positions.[Allocation Strategy])
ORDER BY [Security Master].Description
But when I add Positions.Ticker in query it's not allowing me OR ing in join using Positions.Ticker
Please do reply ASAP
Thanks
August 22, 2008 at 4:24 am
Maroti, this would be a heck of a lot easier to solve if the query was nicely formatted, as follows:
[font="Courier New"]
FROM maintSubStrategySecurityType
RIGHT JOIN tblBlackrockPositionsByFund AS Positions
ON maintSubStrategySecurityType.[Sub Strategy]=Positions.[Sub Strategy] AND maintSubStrategySecurityType.Strategy=Positions.[Allocation Strategy]
LEFT JOIN tblBlackrockSecurityMaster AS [Security Master]
ON Positions.CUSIP=[Security Master].CUSIP
RIGHT JOIN qryBlackrockHaircuts
ON qryBlackrockHaircuts.CUSIP=[Security Master].CUSIP
RIGHT JOIN qryBlackrockReturn
ON qryBlackrockReturn.CUSIP=[Security Master].CUSIP
RIGHT JOIN qryBlackrockRatings
ON qryBlackrockRatings.CUSIP=[Security Master].CUSIP
RIGHT JOIN tblFXRates
ON tblFXRates.Currency=[Security Master].[Price Currency]
RIGHT JOIN maintBlackrockPortfolio
ON maintBlackrockPortfolio.[Portfolio Name]=Positions.Portfolio
LEFT JOIN maintBlackrockCountry
ON [Security Master].[Country Code]=maintBlackrockCountry.[Country Code]
LEFT JOIN qryPNLByFund
ON Positions.Ticker = Positions.Ticker -- change this to required logic
OR Positions.Cusip=qryPNLByFund.Ticker
OR (Positions.[Blackrock Ticker]=qryPNLByFund.Ticker
AND Positions.[Security Group]='OPTION'
AND Positions.[Security Type]='EQUITY')
OR (Positions.ISIN=qryPNLByFund.ISIN
AND Positions.Portfolio=qryPNLByFund.[Blackrock Portfolio Name]) [/font]
Can you do this?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 22, 2008 at 6:26 am
Thanks a lot for your suggestions.
Actually I received this query from my client and he want it in sql.
I converted it properly except that 'Like ' clause.
Once again thanks to all of you for kind cooperation.
September 26, 2008 at 6:08 am
LIKE IS USED YOUR QUERY IN RESULT PART
IT IS NOT WORKED
GURURAJAN.K MSc,MBA
SOFTWARE ENGINEER
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply