December 4, 2012 at 8:05 am
I need to bring back the greatest date (of 4 date fields) from the row I am inserting into policy_t from staging_table. I don't want to insert the greatest date from another table.
staging_table has 6 fields: stg_row_id, code, date1, date2, date3, date4
I want to insert into policy_t.........the code field and the greatest of date1, date2, date3, date4 for the same row.
I have commented out staging_table because that will give me a different answer?
But now syntax is not right, what should I do?
INSERT INTO policy_t
(
code
,pol_stat_eff_dt
)
SELECT
code
,CASE WHEN CODE = 'A' THEN
(SELECT MAX(n) AS MaxDate
--FROM staging_table
CROSS APPLY (VALUES (DATE1),(DATE2),(DATE3),(DATE4)) m(n)
END)
FROM staging_table
Thanks in advance
December 4, 2012 at 8:18 am
Hi and welcome to SSC. In order to be able to provide an answer we need some details. Specifically we need ddl (create table statements), sample data (insert statements) and desired output based on your sample data. Please see the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 ā Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 4, 2012 at 11:39 am
One open question your query leaves open is whether or not there's any other join condition needed to pick a specific record from the staging table to insert into the policy table other than " code = 'A' ".
If not, then you might try something like this:
INSERT INTO POLICY (CODE, POL_STAT_EFF_DT)
SELECT CODE, MAX(POL_STAT_EFF_DT)
FROM STAGING_TABLE
UNPIVOT (WHICH_DATE FOR POL_STAT_EFF_DT IN (DATE1, DATE2, DATE3, DATE4)) AS UPVT
WHERE CODE = 'A'
For small record counts, the UNPIVOT makes this easy, and there's even a field available to designate which of the 4 fields you'll get the value from, but as they always, say, it seems likely there's more to this story... Please elaborate, and see Sean Lange's post about how to get the best help.
Steve (aka sgmunson) š š š
Rent Servers for Income (picks and shovels strategy)
December 4, 2012 at 1:57 pm
I have actually developed this query as follows, but not I get the following error message:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
SELECT
AD.admin_typ_cd
,'GENESIS'
,'GENESIS'
,PO.pol_stat_typ_cd
,SP.spsr_id
,'SUNLIFE'
,CASE STG.de_iss_co_gaaa
WHEN '1'THEN 'SLOC'
WHEN '4'THEN 'SLUS'
WHEN '5'THEN 'SLNY'
ELSE 'SLUS'
END
,STG.num_pol_gaaa
,STG.dte_pol_eff_gaaa
,NULL
-- NOTE FOR CASE STATEMENT BELOW:
-- dte_last_pol_cncl_gaaa if cde_pol_stat_gaaa in (āCā, āDā, āNā).
-- Greater of dte_renw_eff_gaaa or dte_last_pol_reinst_gaaa
-- or dte_pol_eff_gaaa if cde_pol_stat_gaaa = āAā (THE MAX OF 3 DATE FIELDS).
-- dte_pol_eff_gaaa if cde_pol_stat_gaaa = B.
-- Otherwise this will be greatest of dte_last_pol_reinst_gaaa, dte_last_pol_cncl_gaaa, dte_renw_eff_gaaa,
-- dte_pol_eff_gaaa. (THE MAX OF 4 DATE FIELDS).
-- If no date available, use last update date.
,CASE WHEN STG.cde_pol_stat_gaaa IN ('C','D','N') THEN STG.dte_last_pol_cncl_gaaa
ELSE
---- greatest of 3 fields
CASE WHEN STG.cde_pol_stat_gaaa ='A' THEN
(SELECT MaxDate FROM stg_GENESIS_gaaa_t a
LEFT JOIN (SELECT MAX(convert(date,CASE WHEN n = '00/00/0000' THEN '01/01/1900' ELSE N END)) AS MaxDate,num_pol_gaaa
FROM stg_GENESIS_gaaa_t
CROSS APPLY (VALUES (dte_last_pol_reinst_gaaa),(dte_renw_eff_gaaa),(dte_pol_eff_gaaa)) m(n)
GROUP BY num_pol_gaaa) b
on a.num_pol_gaaa = b.num_pol_gaaa)
ELSE
CASE WHEN STG.cde_pol_stat_gaaa ='B' THEN STG.dte_pol_eff_gaaa
ELSE
---- greatest of 4 fields
CASE WHEN STG.cde_pol_stat_gaaa NOT IN ('C','D','N','A','B') THEN
(SELECT MaxDate FROM stg_GENESIS_gaaa_t a
LEFT JOIN (SELECT MAX(convert(date,CASE WHEN n = '00/00/0000' THEN '01/01/1900' ELSE N END)) AS MaxDate,num_pol_gaaa
FROM stg_GENESIS_gaaa_t
CROSS APPLY (VALUES (dte_last_pol_cncl_gaaa),(dte_last_pol_reinst_gaaa),(dte_renw_eff_gaaa),(dte_pol_eff_gaaa)) m(n)
GROUP BY num_pol_gaaa) b
on a.num_pol_gaaa = b.num_pol_gaaa)
ELSE
CASE WHEN STG.dte_last_pol_cncl_gaaa IS NULL OR STG.dte_last_pol_reinst_gaaa IS NULL OR STG.dte_renw_eff_gaaa IS NULL OR
STG.dte_pol_eff_gaaa IS NULL THEN (SELECT CONVERT(VARCHAR(10),GETDATE(),111))
END
END
END
END
END
--,NULL
,NULL
,NULL
,'N'
,NULL
,GETDATE()
,SUSER_SNAME()
,'N'
,NULL
FROMstg_GENESIS_gaaa_tSTG
INNER JOIN sponsor_tSP
ONSTG.num_pol_gaaa= SP.src_spsr_id
ANDSP.src_sys_cd= 'GENESIS'
LEFT OUTER JOIN admin_type_trnsl_tAD
ONSTG.cde_admin_clrk_gaaa = AD.src_admn_typ_cd
ANDAD.src_sys_cd= 'GENESIS'
INNER JOIN policy_status_type_trnsl_tPO
ONSTG.cde_pol_stat_gaaa = PO.src_pol_stat_typ_cd
ANDAD.src_sys_cd= 'GENESIS'
Can anyone see a problem in the code?
December 4, 2012 at 2:11 pm
I can see a lot of things wrong with that query.
Let's start here.
CASE
WHEN STG.cde_pol_stat_gaaa = 'A'
THEN (
SELECT MaxDate
FROM stg_GENESIS_gaaa_t a
Can the subquery return more than 1 row? It is impossible to say, it is even more impossible to read this. There are so many subqueries and nested case statements it is impossible to decipher. I tried to run through an auto formatter and it crashes.
Remember we can't see your screen, we don't know your tables and we are not familiar with your project.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 ā Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 4, 2012 at 5:43 pm
Unpivoting is is probably one of the slowest ways to do this. Just write a nice case statement a be done with it.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply