x

SQL server procedure from Access Query

 SELECT Max(IIf([media] Like '*tv*',[AdDate],#1/1/2001#)) AS tv, Max(IIf([media] Not Like '*tv*',[AdDate],#1/1/2001#)) AS print INTO tblRecent
 FROM tblMain
 WHERE (((tblMain.AdDate)


I've been creating case statements from access. This is just odd with the max in front.

I am sorry I didn't realize part of my text didn't save. I was asking how to convert this to sql stored procedure.

more ▼

asked Dec 18, 2012 at 04:34 PM in Default

avatar image

artistlover
866 48 64 68

Forgive me, but what exactly is your question?

Dec 18, 2012 at 04:37 PM JohnM

I'm afraid that I also seem to be missing the question.

Dec 18, 2012 at 05:21 PM TimothyAWiseman

Well I dropped the ball my apologies. Ask for help and don't provide details.

It is SQL server 2008.

Dec 18, 2012 at 06:15 PM artistlover
(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

So would this work? There very well could be a better/different way to accomplish this.

     ;WITH myCTE (tv)  
     AS (SELECT CASE 
      WHEN media LIKE '%tv%' THEN [adDate]
      ELSE '1/1/2001'
      END AS TV
     FROM tblMain
     WHERE AdDate = AdDate)    
      SELECT MAX(tv) 
              INTO tblRecent
              FROM myCTE
             GO 

  

You didn't specify which version of SQL Server you're working with so that could play a role as well. Also, do you need the TV & Print columns returned?

Also, you didn't specify the WHERE clause so I just set it to itself so that it would always return. You can modify that to suit your needs.

Other folks here might have a much better solution.

more ▼

answered Dec 18, 2012 at 05:59 PM

avatar image

JohnM
14.2k 3 7 14

(comments are locked)
10|1200 characters needed characters left
 ;WITH myCTE (tv,[print])
     AS (SELECT CASE 
      WHEN media LIKE '%tv%' THEN [adDate]
      ELSE '1/1/2001'
      END AS TV,
      Case 
      WHEN media not like '%tv%' THEN AdDate
      ELSE '1/1/2001'
      END AS [print]
     FROM tblMain
     WHERE AdDate < GETDATE() ) 
      SELECT MAX(tv), MAX([print])
              INTO tblRecent
              from myCTE
             GO 

I think i am getting close but I get the error below An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

more ▼

answered Dec 18, 2012 at 08:11 PM

avatar image

artistlover
866 48 64 68

I'm not sure that I'm following your logic. If the media column is NOT like 'TV' it'll fall into the first ELSE statement, thus assigning the value of '1/1/2001'. I don't think that the second case statement, as you have it, is needed. Unless I'm missing something. Should the second case read "where media like '%print%' maybe?

In regards to the error, you'll need to alias the select. Try this:

SELECT MAX(tv) as 'TV', MAX(print) as 'Print' INTO...

My fault. I missed that on the first go around.

Dec 18, 2012 at 08:19 PM JohnM
(comments are locked)
10|1200 characters needed characters left

;WITH myCTE (tv,prints) AS (SELECT CASE WHEN media LIKE '%tv%' THEN adDate ELSE '1/1/2001' END AS TV, Case WHEN media not like '%tv%' THEN AdDate ELSE '1/1/2001' END AS prints FROM tblMain WHERE AdDate < GETDATE() ) SELECT MAX(tv), MAX(prints) INTO tblRecent from myCTE GO This results in same error

more ▼

answered Dec 18, 2012 at 08:20 PM

avatar image

artistlover
866 48 64 68

(comments are locked)
10|1200 characters needed characters left

hey believe me i can't follow whoever wrote this stuff

SELECT Max(IIf([media] Like 'tv',[AdDate],#1/1/2001#)) AS tv, Max(IIf([media] Not Like 'tv',[AdDate],#1/1/2001#)) AS print INTO tblRecent FROM tblMain WHERE (((tblMain.AdDate)

more ▼

answered Dec 18, 2012 at 08:22 PM

avatar image

artistlover
866 48 64 68

(comments are locked)
10|1200 characters needed characters left

Hey i got it working. Thank you so much for your help.

more ▼

answered Dec 18, 2012 at 08:24 PM

avatar image

artistlover
866 48 64 68

Glad you got it working!! Make sure to mark one of them as the answer to help others in the future. Thanks!

Dec 18, 2012 at 08:42 PM JohnM
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2188
x476

asked: Dec 18, 2012 at 04:34 PM

Seen: 612 times

Last Updated: Dec 18, 2012 at 09:08 PM

Copyright 2017 Redgate Software. Privacy Policy