String variable passed to OPENQUERY

[CrystalDuck]

I am on SQL Server, rtying to hit an Oracle database, and feel like I'm so close, but it just don't wanna take it....help.

DECLARE
@previous_date	smalldatetime,
@cmd 			varchar(4000)
--DROP TABLE #OPEN_CLOSE_ALL
Create Table #OPEN_CLOSE_ALL
(PRI_ACCT_ID		varchar(14),
SHRHLD_SHORT_NAME 	varchar(25),
ADDR_LINE1_DESC		varchar(50),
CITY_NAME		varchar(50),
ST_CODE_SHORT_DESC	varchar(25),
POSTAL_CODE		varchar(20),
CUSIP_ID		varchar(9),
CLASS_NAME		varchar(25),  
FUND_ID			int,  
BEG_SHR_BAL_AMT		float,
purch_shr_amt		float,
rdmp_shr_amt		float,
PROC_DATE		char(10)
)


IF @Previous_date IS NULL
	SET @Previous_date = CONVERT(varchar(12),(dateadd(dd,-1,GETDATE()),101)


SELECT  @cmd = 
'SELECT *  INTO #OPEN_CLOSE_ALL FROM OPENQUERY(PHPROD,
''SELECT DISTINCT
A.PRI_ACCT_ID,
A.SHRHLD_SHORT_NAME,
A.ADDR_LINE1_DESC,
A.CITY_NAME, 
D.ST_CODE_SHORT_DESC,
A.POSTAL_CODE,
B.CUSIP_ID,
C.CUSIP_SHORT_NAME, 
C.FUND_ID,
E.BEG_SHR_BAL_AMT,
E.purch_shr_amt,
E.rdmp_shr_amt,
TO_CHAR(E.PROC_DATE,''DD/MM/YYYY'')

FROM INST.TB_SHRHLD A
INNER JOIN INST.TB_SHRHLD_CUSIP B
ON A.SHRHLD_ID = B.SHRHLD_ID

INNER JOIN INST.TB_CUSIP C
ON B.CUSIP_ID = C.CUSIP_ID

INNER JOIN INST.TB_ST D
ON A.ST_CODE = D.ST_CODE

INNER JOIN INST.TB_SHRHLD_BAL E
ON (A.SHRHLD_ID = E.SHRHLD_ID
AND B.CUSIP_ID = E.CUSIP_ID)

WHERE c.inst_flag = ''Y''
AND c.subacct_cusip_flag = ''N''
AND TO_CHAR(E.PROC_DATE,''DD/MM/YYYY'') = '''''' + @previous_date + '''''' )'

--select (@cmd)


EXEC (@cmd)

I've converted both dates (PROC_DATE & @previous_date) from SQL and Oracle to strings, hoping that would make it easier, but to no avail. When I hard code a date (ex. 08/04/2005) in the where clause, it pulls cacbk everything just fine, but it is definitely struggling with recognizing that the variable (@previous_date) is also a string, exactly the same format that it's looking for. *ARGH*

When I run the above code, I get two errors, and I just don't get, what the heck??!! I've tried adding and deleting quotemarks, but I feel like I'm just going in circles...any one have any clues?

The errors are:

Server: Msg 170, Level 15, State 1, Line 15
Line 15: Incorrect syntax near 'DD'.
Server: Msg 105, Level 15, State 1, Line 35
Unclosed quotation mark before the character string '' )'.

ANY help is greatly appreciated!

Thanks in advance,
CrystalDuck

 

[mrdenny]

First why are you using dynamic SQL to create this query? It's just going to make it more complex than it needs to.

Second, why not simply create a linked server to this data and access the data that way?

Have it print the SQL statement to the screen. They copy and paste the generated statement and run it. Fix any errors there, they move those changes back to the generated code.

You'll want to put your result set into a table other than the temp table you are currently using. The outer code that is running the dyanamic SQL won't be able to access the temp table.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[CrystalDuck]

I wasn't originally doing it that way...but after reading a few of the threads on this site, this was the advice I had picked up. But obviously I was WRONG! Or at least that's the message I got loud and clear from you.

I HAVE to dump it into a temp table no matter if I do the dynamic SQL or not. I do not have free reign of the database, and need a damn good reason to creat a regular table. So I do it within my stored procedure, and create a temp table.

If I just use the linked server...it takes FOREVER to run because of the amount of tables I have to access in the ORACLE side.

I tried this...just as simple as you please, and the query is still running after 25 minutes. When I hard code the date into my code with the OPENQUERY, it comes back in less than 2 minutes.

DECLARE
@previous_date	Char(10)

--DROP TABLE #OPEN_CLOSE_ALL
Create Table #OPEN_CLOSE_ALL
(PRI_ACCT_ID		varchar(14),
SHRHLD_SHORT_NAME 	varchar(25),
ADDR_LINE1_DESC		varchar(50),
ADDR_LINE2_DESC		varchar(50),
ADDR_LINE3_DESC		varchar(50),
ADDR_LINE4_DESC		varchar(50),
ADDR_LINE5_DESC		varchar(50),
CITY_NAME		varchar(50),
ST_CODE_SHORT_DESC	varchar(25),
POSTAL_CODE		varchar(20),
CUSIP_ID		varchar(9),
CLASS_NAME		varchar(25),  
FUND_ID			int,  
subacct_cusip_flag	varchar(1),
inst_flag		varchar(1),
BEG_SHR_BAL_AMT		float,
purch_shr_amt		float,
rdmp_shr_amt		float,
PROC_DATE		char(10),
STMT_TYPE_SHORT_DESC  	varchar(50),
ACCT_ID			int,
Acct_name		varchar(25), 
Fund_name		varchar(25)
)


IF @Previous_date IS NULL
	SET @Previous_date = CONVERT(varchar(10),(dateadd(dd,-1,GETDATE()),101)


INSERT INTO #OPEN_CLOSE_ALL 
SELECT DISTINCT
A.PRI_ACCT_ID,
A.SHRHLD_SHORT_NAME,
A.ADDR_LINE1_DESC,
A.ADDR_LINE2_DESC,
A.ADDR_LINE3_DESC,
A.ADDR_LINE4_DESC,
A.ADDR_LINE5_DESC,
A.CITY_NAME, 
D.ST_CODE_SHORT_DESC,
A.POSTAL_CODE,
B.CUSIP_ID,
C.CUSIP_SHORT_NAME, 
C.FUND_ID,
C.SUBACCT_CUSIP_FLAG,
C.INST_FLAG,
E.BEG_SHR_BAL_AMT,
E.PURCH_SHR_AMT,
E.RDMP_SHR_AMT,
convert(char(10),E.PROC_DATE,101),
F.STMT_TYPE_SHORT_DESC,
H.ACCT_ID,
H.ACCT_SHORT_NAME,
I.FUND_SHORT_NAME

FROM PHPROD..INST.TB_SHRHLD A
INNER JOIN PHPROD..INST.TB_SHRHLD_CUSIP B
ON A.SHRHLD_ID = B.SHRHLD_ID

INNER JOIN PHPROD..INST.TB_CUSIP C
ON B.CUSIP_ID = C.CUSIP_ID

INNER JOIN PHPROD..INST.TB_ST D
ON A.ST_CODE = D.ST_CODE

INNER JOIN PHPROD..INST.TB_SHRHLD_BAL E
ON (A.SHRHLD_ID = E.SHRHLD_ID
AND B.CUSIP_ID = E.CUSIP_ID)

INNER JOIN PHPROD..INST.TB_STMT_TYPE F
ON A.STMT_TYPE_CODE = F.STMT_TYPE_CODE

INNER JOIN PHPROD..INST.TB_CNTRY G
ON A.CNTRY_CODE = G.CNTRY_CODE

INNER JOIN PHPROD..INST.TB_ACCT H
ON A.ACCT_ID = H.ACCT_ID

INNER JOIN PHPROD..INST.TB_FUND I
ON C.FUND_ID = I.FUND_ID

WHERE c.INST_FLAG = 'Y'
AND c.SUBACCT_CUSIP_FLAG = 'N'
AND convert(char(10),E.PROC_DATE,101) =  @comparedt


Select * from #OPEN_CLOSE_ALL

So I guess I'm not an expert at this thing...but I'm trying and that's why I come on the forum.

So I hope I've answered all your questions, and if anyone out there can help me come up with a better way to do this, please, I need help.

Thank you for your time, and patience.

Sincerely,
Brenda L.

 

[mrdenny]

One reason that this is going to take a long time will be that you are converting the E.PROC_DATE to char(10).

Since we've gotten ride of the dynamic SQL the temp table is fine.

We can stick to the openquery. Without knowing the databases I can only make broad sugestions. If you have large tables on the Oracle side then the openquery will indead be the better option for you. I certinelly didn't mean to stear you in the wrong direction.

How does something like this grab you.

declare @Previous_date varchar(10)

insert into #OPEN_CLOSE_ALL
SELECT *  
FROM OPENQUERY(PHPROD, 'SELECT DISTINCT
A.PRI_ACCT_ID,
A.SHRHLD_SHORT_NAME,
A.ADDR_LINE1_DESC,
A.CITY_NAME, 
D.ST_CODE_SHORT_DESC,
A.POSTAL_CODE,
B.CUSIP_ID,
C.CUSIP_SHORT_NAME, 
C.FUND_ID,
E.BEG_SHR_BAL_AMT,
E.purch_shr_amt,
E.rdmp_shr_amt,
TO_CHAR(E.PROC_DATE,''DD/MM/YYYY'')

FROM INST.TB_SHRHLD A
INNER JOIN INST.TB_SHRHLD_CUSIP B
ON A.SHRHLD_ID = B.SHRHLD_ID

INNER JOIN INST.TB_CUSIP C
ON B.CUSIP_ID = C.CUSIP_ID

INNER JOIN INST.TB_ST D
ON A.ST_CODE = D.ST_CODE

INNER JOIN INST.TB_SHRHLD_BAL E
ON (A.SHRHLD_ID = E.SHRHLD_ID
AND B.CUSIP_ID = E.CUSIP_ID)

WHERE c.inst_flag = ''Y''
AND c.subacct_cusip_flag = ''N'''
)
where PROC_DATE = @Previous_date

See what kind of performance you get out of this.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[ClayG]

SELECT @cmd =
'SELECT * INTO #OPEN_CLOSE_ALL FROM OPENQUERY(PHPROD,
''SELECT DISTINCT
A.PRI_ACCT_ID,
...
TO_CHAR(E.PROC_DATE,''DD/MM/YYYY'')
...

You have string within strings within strings.

Level1: @cmd = '..'
Level2: the Oracle statement ''Select Distinct ...'' correctly has two quotes.
Level3: DD/MM/YYYY is a string within the oracle statment, so needs FOUR quotes ''''DD/MM/YYYY''''
I have not worked out the end part of the statement.

One trick I like, is to use a different character in place of embedded quotes, and replace it, makes it
easier to read, e.g.:
set @cmd =
'SELECT * INTO #OPEN_CLOSE_ALL FROM OPENQUERY(PHPROD,
''SELECT DISTINCT
A.PRI_ACCT_ID,
...
TO_CHAR(E.PROC_DATE,^DD/MM/YYYY^)
... '
set @cmd = REPLACE( @cmd, '^', REPLICATE( Chr(39), 4))