Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Code explanation

Author  Topic 

Damian39
Starting Member

33 Posts

Posted - 2014-07-28 : 10:09:36
Hello all,
It has been a while since I've had to do any serious SQL coding, and I have been trying to make sense of some code I have been asked to modify. I would very much like to know if anyone can possibly explain what is being done with the following code snippet. I understand they are creating a temp table, but then the select statement is where I am having difficulty understanding. First the SELECT statement is between two tics, and the acctcorp parameters are in between 2 tics each. When I attempt to run the code, I receive an error: Cannot create an instance of OLE DB provider... Here is the code I am looking at:

IF OBJECT_ID('TEMPDB..#TMP1A') IS NOT NULL DROP TABLE #TMP1A
IF OBJECT_ID('TEMPDB..#TEMPSUBS1') IS NOT NULL DROP TABLE #TEMPSUBS1
IF OBJECT_ID('TEMPDB..#TEMPSUBS2') IS NOT NULL DROP TABLE #TEMPSUBS2


SELECT
ACCTCORP,
HOUSE,
CUST,
BADATE,
AMOUNT,
ADJRSN,
CASE WHEN BATCH BETWEEN 10000 AND 10999 THEN 'A' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 11000 AND 11999 THEN 'B' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 12000 AND 12999 THEN 'C' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 13000 AND 13999 THEN 'D' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 14000 AND 14999 THEN 'E' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 15000 AND 15999 THEN 'F' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 16000 AND 16999 THEN 'G' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 17000 AND 17999 THEN 'H' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 18000 AND 18999 THEN 'I' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 19000 AND 19999 THEN 'J' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 20000 AND 20999 THEN 'K' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 21000 AND 21999 THEN 'L' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 22000 AND 22999 THEN 'M' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 23000 AND 23999 THEN 'N' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 24000 AND 24999 THEN 'O' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 25000 AND 25999 THEN 'P' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 26000 AND 26999 THEN 'Q' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 27000 AND 27999 THEN 'R' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 28000 AND 28999 THEN 'S' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 29000 AND 29999 THEN 'T' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 30000 AND 30999 THEN 'U' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 31000 AND 31999 THEN 'V' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 32000 AND 32999 THEN 'W' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 33000 AND 33999 THEN 'X' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 34000 AND 34999 THEN 'Y' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 35000 AND 35999 THEN 'Z' + RIGHT(BATCH,3)
WHEN CONVERT(VARCHAR,BATCH) <= 9999 THEN CONVERT(VARCHAR,BATCH)
ELSE '' END AS 'DDP_BATCH_#',BATCH AS [ORIG_BATCH_#],
--BATCH,
CTLGRP,
CASE WHEN POSTFLAG = ' ' THEN 'No'
ELSE 'Yes' END AS POSTFLAG
INTO #TMP1A

--FROM OPENQUERY(/*INFODDP*/THUNDER_ETL,

FROM OPENQUERY(DSSOPT_DDP,

'
SELECT
BC.ACCTCORP,BD.HOUSE,BD.CUST,BC.BADATE,BD.AMOUNT,BD.ADJRSN,BC.BATCH,BC.CTLGRP,BD.POSTFLAG
FROM DDPDB.IDST_BATCH_CATALOG BC JOIN DDPDB.IDST_BATCH_DETAIL BD
ON(BC.ACCTCORP=BD.ACCTCORP) AND (BC.BATCH=BD.BATCH)
WHERE BD.ACCTCORP IN (''1624'', ''1638'', ''1641'', ''1643'', ''1710'',''01105'',''01636'',''01719'',''09587'',''15515'',''19204'')
AND BC.CTLGRP = ''REFND'' AND BD.AMOUNT >= ''500.00''
'
)


CREATE TABLE #TEMPSUBS1 (
AcctCorp INT,
House INT,
Cust TINYINT,
Ftax TINYINT,
BADR CHAR(1) DEFAULT ' '
)

INSERT INTO #TEMPSUBS1 (AcctCorp,House,Cust,Ftax,BADR)


SELECT A.ACCTCORP,A.HOUSE,A.CUST,H.Ftax,C.BADR

FROM #TMP1A A INNER JOIN [INFODDPFlorida].[ggs].[IDST_CUSTOMER] C (NOLOCK)
ON A.ACCTCORP=C.ACCTCORP AND
A.HOUSE=C.HOUSE AND
A.CUST=C.CUST

INNER JOIN [INFODDPFlorida].[ggs].[IDST_HOUSE] H (NOLOCK)
ON C.ACCTCORP=H.ACCTCORP AND
C.HOUSE=H.HOUSE


Damian

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-28 : 12:13:57
The key part is here: FROM OPENQUERY(DSSOPT_DDP...

It's using OPENQUERY to query a remote data source (DSSOPT_DDP).

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Damian39
Starting Member

33 Posts

Posted - 2014-07-28 : 13:01:16
Hi Tara, thanks for replying back to my posts. I thought it had something to do with an external data source, but I wasn't entirely sure. As I stated, it has been a while since I've had to do any in depth SQL coding so my skill set has basically gone back to that of a newbie. The original query is pretty long, but basically what it is doing is looking for any accounts that were issued a refund of $500 or more. I would like to modify the query to no longer have that monitary limitation on it, and to also pull the information from the tables within our DB rather than an independent data source. Unfortunately, I have been unable to get my brain around temp tables, and the best way to go about modifying the original query. Would you suggest I use the SELECT statement the original query has within the tics (after the OPENQUERY line) as my SELECT statement for the first temp table?

Damian
Go to Top of Page
   

- Advertisement -