error in select statement - SQL Server Forums

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

error in select statement

Author

Topic

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-11-19 : 06:10:33

i have written the select statement like this

SELECT distinct PAYMENTCODE,

VOUCHERMONTH,

HOUSEHOLDID,

PAYMENTCATEGORYCODE AS PAYMENTCATEGORYCODE1,

PREMIUMPAYMENT,

OLDPREMIUMPAYMENT,

PAYMENTAMT,

ADJUSTMENTEFFECTIVEDATE,

RUNDATE

INTO #TEMP_CHP_CASE1_RESULT

FROM UST_GPA_CHP_PYMT pymt

inner JOIN UST_GPA_CHP_PYMT1 pymt1
on UST_GPA_CHP_PYMT.UST_GPA_CHP_PYMT1=UST_GPA_CHP_PYMT1.UST_GPA_CHP_PYMT1

(
SELECT PAYMENTCATEGORYCODE AS PAYMENTCATEGORYCODE2
FROM UST_GPA_CHP_PYMT1
where
PAYMENTCATEGORYCODE IN ('I','B','L','A')

GROUP BY PAYMENTCODE, HOUSEHOLDID, ADJUSTMENTEFFECTIVEDATE, PAYMENTCATEGORYCODE

HAVING PAYMENTCATEGORYCODE IN ('A','D')

)

its throwing an error in the select statement in the subquery i cant trace it out

GROUP BY PAYMENTCODE, HOUSEHOLDID, ADJUSTMENTEFFECTIVEDATE, PAYMENTCATEGORYCODE

HAVING PAYMENTCATEGORYCODE IN ('A','D')

susan

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-19 : 06:21:59

Not sure what you have towards the last part there. If you want that included in the select for all rows, then include that in the select.
Or instead use cross join.

SELECT distinct PAYMENTCODE,

VOUCHERMONTH,

HOUSEHOLDID,

PAYMENTCATEGORYCODE AS PAYMENTCATEGORYCODE1,

PREMIUMPAYMENT,

OLDPREMIUMPAYMENT,

PAYMENTAMT,

ADJUSTMENTEFFECTIVEDATE,

RUNDATE,

(
SELECT PAYMENTCATEGORYCODE AS PAYMENTCATEGORYCODE2
FROM UST_GPA_CHP_PYMT1
where 
PAYMENTCATEGORYCODE IN ('I','B','L','A')

GROUP BY PAYMENTCODE, HOUSEHOLDID, ADJUSTMENTEFFECTIVEDATE, PAYMENTCATEGORYCODE

HAVING PAYMENTCATEGORYCODE IN ('A','D')

) 

INTO #TEMP_CHP_CASE1_RESULT

FROM UST_GPA_CHP_PYMT pymt

inner JOIN UST_GPA_CHP_PYMT1 pymt1
on UST_GPA_CHP_PYMT.UST_GPA_CHP_PYMT1=UST_GPA_CHP_PYMT1.UST_GPA_CHP_PYMT1

--use cross join

SELECT distinct PAYMENTCODE,

VOUCHERMONTH,

HOUSEHOLDID,

PAYMENTCATEGORYCODE AS PAYMENTCATEGORYCODE1,

PREMIUMPAYMENT,

OLDPREMIUMPAYMENT,

PAYMENTAMT,

ADJUSTMENTEFFECTIVEDATE,

RUNDATE



INTO #TEMP_CHP_CASE1_RESULT

FROM UST_GPA_CHP_PYMT pymt

inner JOIN UST_GPA_CHP_PYMT1 pymt1
on UST_GPA_CHP_PYMT.UST_GPA_CHP_PYMT1=UST_GPA_CHP_PYMT1.UST_GPA_CHP_PYMT1
cross join
(
SELECT PAYMENTCATEGORYCODE AS PAYMENTCATEGORYCODE2
FROM UST_GPA_CHP_PYMT1
where 
PAYMENTCATEGORYCODE IN ('I','B','L','A')

GROUP BY PAYMENTCODE, HOUSEHOLDID, ADJUSTMENTEFFECTIVEDATE, PAYMENTCATEGORYCODE

HAVING PAYMENTCATEGORYCODE IN ('A','D')

) t

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-11-19 : 06:26:45

Hi i tried ur method it is showing an error in the bracket present at the last so i replace before group by and tried its throwing an error in the bracket like incorrecct syntax near )

susan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 06:46:02

your below subsquery doesnt make sense at all


(
SELECT PAYMENTCATEGORYCODE AS PAYMENTCATEGORYCODE2
FROM UST_GPA_CHP_PYMT1
where 
PAYMENTCATEGORYCODE IN ('I','B','L','A')

GROUP BY PAYMENTCODE, HOUSEHOLDID, ADJUSTMENTEFFECTIVEDATE, PAYMENTCATEGORYCODE

HAVING PAYMENTCATEGORYCODE IN ('A','D')

)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-19 : 06:47:31

Is this related to this?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114757

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-11-19 : 06:51:18

ya it is related to that only

susan

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-19 : 07:50:50

Your inner select doesn't look right. Not sure what you want there. Correct that bit and the above will work fine. I believe you're already on that in another forum.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 08:31:53

quote:
Originally posted by susan_151615

ya it is related to that only

susan

can you state whats the purpose of subquery i posted? you're using two different conditions on WHERE & HAVING

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources