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.
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 #TMP1AIF 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 POSTFLAGINTO #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.POSTFLAGFROM 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.BADRFROM #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.HOUSEDamian |
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
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 |
|
|
|
|
|
|
|