Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1124
  • Last Modified:

CTE, with a Global Temporary Table doesn't work

Hi,

 I have a SQL code (attached), which has a global temp table declared and inserted into.
 Also, there is a CTE table. I was trying to join both these tables, but it is not working.
  Can someone kindly suggest any changes to make it work?

Thanks!
SQL.sql
0
pvsbandi
Asked:
pvsbandi
  • 16
  • 12
  • 2
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Pvsbandi,

What do you mean "not working"?


Kent
0
 
pvsbandiAuthor Commented:
Hi,

   It is throwing errors like, semicolon present before "with" etc. Basically, it is not compiling.
0
 
wolfgang_93Commented:
You appear to have a ";" in front of the WITH part of the INSERT command.
Try removing it.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
Kent OlsenData Warehouse Architect / DBACommented:
Is this part of a stored procedure or are you running it through an SQL client?

If you're using a client, have you made sure that the semicolon is the statement terminator?

Last, can you run each of these statements "one at a time"?


Kent
0
 
wolfgang_93Commented:
kdo: I don't think running the second statement in a "one at a time" mode will work as the
global temporary table (on which it is dependent) won't exist as it will have been dropped
after the end of the first session.
0
 
pvsbandiAuthor Commented:
Hi,

 Yes, they work one at a time and yes, comma is the delimiter.
  Also, i tried running it as a freehand SQL and also tried to run as a stored procedure. Neither worked.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi wolfgang,

I was looking to see if there was a syntax error in one of the statements or if the problem lied elsewhere.


Hi pvsbandi,

The semicolon needs to be the delimiter (statement terminator).


Kent
0
 
pvsbandiAuthor Commented:
Hi Kent,

           I have put the semicolon after the declare, insert and after the end of the final select.
           But it still throws an error and it is pickling the Rand function to have incorrect number of arguments. But i'm sure, that's not where the error is. Bottomline, didn't work :(
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi pvsbandi,

You're on UDB/LUW 9.x, right?

0
 
Kent OlsenData Warehouse Architect / DBACommented:
I reformatted your query so that I could better follow it.  I don't see an issue.

Here's the CTE without the join to the temp table.  Try highlighting and executing each of the subqueries and seeing if they all work as intended.


Kent

WITH temp2 as 
(
  SELECT   distinct
    value_tx, county_cd,referral_id,ch_client_id,ch_last_nm,ch_first_nm,ch_middle_nm,ch_dob_dt,investigation_start_dt		
    ,inv_completion_dt,supervisor_first_nm,supervisor_last_nm,worker_first_nm,worker_last_nm
    ,row_number() over (partition by county_cd order by bias) AS RN
    ,BIAS
	FROM
	(
		select   
			value_tx,pl_vals.county_cd,referral_id,ch_client_id,ch_last_nm,ch_first_nm,ch_middle_nm,ch_dob_dt		
			,investigation_accepted_dt as investigation_start_dt,action_dt as inv_completion_dt
			,supervisor_first_nm,supervisor_last_nm,worker_first_nm,worker_last_nm
			,cast(rand() * 10000 as integer) as BIAS
		from 
		(	
			SELECT
				picklist_value_cd  as county_cd,value_tx
			FROM tb_picklist_values	
			WHERE picklist_type_id=104  
				AND DELETE_SW ='N'
				AND picklist_value_cd NOT IN('3824','3825','3826')
		) pl_vals
		join
		(
			select
				t1.referral_id,worker_id,investigation_accepted_dt,action_dt,sf.primary_county_cd as county_cd
				,ssf.first_nm      AS supervisor_first_nm,ssf.last_nm       AS supervisor_last_nm
				,sf.first_nm       AS worker_first_nm,    sf.last_nm        AS worker_last_nm	
				,tbl_chs.client_id as ch_client_id,       tbl_chs.last_nm   as ch_last_nm
				,tbl_chs.first_nm  as  ch_first_nm,       tbl_chs.middle_nm as ch_middle_nm	
				,tbl_chs.dob_dt as ch_dob_dt		
			from
			(
				select 
					referral_id 			
					,CHESSIE.F_PRIM_WORKER_ID(referral_id ,'2957') as worker_id 
					,investigation_accepted_dt,action_dt
				from tb_referral rf			
				left join tb_supervisor_approvals sa
					on (sa.approval_key_id = rf.referral_id  
         and (sa.delete_sw = 'N' or sa.delete_sw ='N')
         and sa.approval_status_cd ='3047' 
				 and sa.approval_nature_cd ='2967'
				 and sa.entity_type_cd ='2957')
				WHERE (rf.delete_sw='N' or rf.expungement_sw='Y')	
					and upper(referral_type_sw)='C' 
					AND cps_recommend_sw='Y'
					and ref_approval_status_cd='3047'
					and  investigation_accepted_dt between DATE('08/20/2010') and DATE('10/20/2010') 
			)  t1
			inner join tb_staff sf
				 on (sf.staff_id = t1.worker_id AND sf.delete_sw ='N')
			inner join tb_county_unit cu
				 on (sf.primary_county_unit_id = cu.county_unit_id AND cu.delete_sw ='N' )
			inner join tb_staff ssf
				 on (ssf.staff_id = cu.unit_supervisor_id AND ssf.delete_sw ='N' )	
			inner join
			(
				select cl.client_id ,cl.dob_dt, referral_id ,first_nm ,last_nm,middle_nm
				from tb_referral_clients rc 
				inner join tb_client cl 
					on ( cl.client_id  = rc.client_id  and cl.delete_sw ='N' and rc.delete_sw ='N')
				where rc.delete_sw ='N' 
					and referral_client_id in (select  referral_client_id  from tb_client_roles 
                                     where  delete_sw = 'N' 
                                       and role_cd ='2085' 
                                       and referral_client_id in (select referral_client_id 
                                                                  from tb_referral_clients 
                                                                  where  delete_sw ='N'
                                                                 )
                                    )
			) tbl_chs
				on ( tbl_chs.referral_id = t1.referral_id )
		) t2
		on t2.county_cd = pl_vals.county_cd
		order by  pl_vals.county_cd
	) AS T
	ORDER BY 15
) 
select temp2.*

Open in new window

0
 
pvsbandiAuthor Commented:
Hmm.. you are right! It worked a while ago, but it is now failing at the Rand runction.
  If i comment the Rand function line, it works. But it worked before :(
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Weird.  

The rand function should work just as you have it.  I copied/pasted the cast(rand...) text and it works fine.

Just for test, try inserting an integer value as the parameter to rand().  Perhaps there's something wrong with your environment so that rand(void) is corrupted but rand(int) is OK.


Kent
0
 
Kent OlsenData Warehouse Architect / DBACommented:
For test purposes, what if you omit the call to rand() and just use a constant?

      ,cast(rand() * 10000 as integer) as BIAS


becomes

      , 123 as BIAS


Kent
0
 
pvsbandiAuthor Commented:
I tried Rand() * 10000, but it still gives error.
   Can you give an example for your suggestion?

Thanks much for all the help!
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Try the constant suggested two posts back.

Kent
0
 
pvsbandiAuthor Commented:
yes, a constant works absolutely fine. But, i need a random selection of the number of rows and that's the purpose of the whole effort.
 
  Is there a workaround for it?
0
 
Kent OlsenData Warehouse Architect / DBACommented:

Rand() doesn't work.  Amazing.

Can you confirm your system.  UDB/LUW 9.x?

And let's see if the rand function is there.  Try this query:

  select cast(rand() * 10000 as integer) from sysibm.sysdummy1;


Kent
0
 
pvsbandiAuthor Commented:
No, i'm on DB2 UDB 8.2

select cast(rand() * 10000 as integer) from sysibm.sysdummy1

The above query doesn't work and it throws the same error (no function Rand with paramteres ....)

0
 
Kent OlsenData Warehouse Architect / DBACommented:
Version 8.  Rats.  I don't have a version 8 system to test against.

Try passing an integer to rand.  I just don't rememeber....


  select cast(rand(127) * 10000 as integer) from sysibm.sysdummy1;


Kent
0
 
Kent OlsenData Warehouse Architect / DBACommented:
According to the version 8 documentation, rand() behaves similarly in versions 8 and 9.

That's the good news.

Somehow, you're not connecting to it.


rand() should be in the sysfun schema.  There should be two entries, one for rand() and a second for rand(integer).  Check to see if they exist.

0
 
pvsbandiAuthor Commented:
nothing seem to work, related to Rand.. But it really worked before :((
   Anyways, thanks much for all your help. Really appreciate.
0
 
pvsbandiAuthor Commented:
>>rand() should be in the sysfun schema.  There should be two entries, one for rand() and a second for rand(integer).  Check to see if they exist

BTW, how do we check the above?
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi...

I think that it's your environment.  Either the rand() functions have been deleted from the sysfun schema (an odd thing to do) or you environment isn't automatically accessing the schema or is somehow not authorized to it.

Run this from your user:

   select sysfun.rand() from sysibm.sysdummy1;


If that doesn't work, run it from the sysadmin user.


Kent
0
 
pvsbandiAuthor Commented:
You are a rockstar!!!

     In the query, i made it sysfun.rand() and ran the whole thing.. and it worked out excellently!!
0
 
Kent OlsenData Warehouse Architect / DBACommented:

Or run this query:

  select * from sysibm.sysroutines where routinename = 'RAND';


0
 
Kent OlsenData Warehouse Architect / DBACommented:
Something is messed up in your environment.  You should link the sysfun routines without having to specify the schema name.


Perhaps you have something in your own schema named rand?


Kent
0
 
pvsbandiAuthor Commented:
in the db2cli file on the IBM folder, there is no entry for the Currentfunctionpath.
  But even after i added the SYSFUN to it, didn't work when i didn't explicitly mention the schema name before the function..
0
 
pvsbandiAuthor Commented:
Also, when i ran this query

select * from sysibm.sysroutines where routinename = 'RAND';

I see two rows under the sysfun schema; Specific names for these are Rand1 and Rand2.
 Could this have anything to do with my struggle here?
0
 
Kent OlsenData Warehouse Architect / DBACommented:

Let's see if you have a view called RAND.

 select * from sysibm.views where table_name = 'RAND';

Oh, and if you have a table named RAND in that schema you'll also get that error.


Kent
0
 
Kent OlsenData Warehouse Architect / DBACommented:

Those appear to be the correct items in the sysfun schema.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 16
  • 12
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now